Tuesday 22 October 2013

Publishing OLAP Schema to Jasper Server - Step by Step procedure

Hi guys,
This post will teach you how to publish OLAP Schema( .xml file OR CUBE) to the Jasper Server.

Where to download Schema Workbench ?
You can develop Schema either using PSW(Pentaho Schema Workbench of Pentaho) or SW(Schema workbench of Jasper)
Both the tools are same with their version release difference.

Download the latest PSW in location mentioned below.
http://sourceforge.net/projects/mondrian/files/schema%20workbench/3.5.0-stable/psw-ce-3.5.0.zip/download

Download the same for Jasper using below link
http://sourceforge.net/projects/jasperserver/files/JasperServer/JasperServer%204.0.0/JaspersoftOLAP-Workbench-cp-4.0.0.zip/download

You need to work out at 3 places to see the output of the Schema that you created using PSW or SW tool. They are
1) Analysis Schema
2) Analysis Connection &
3) Analysis Views

I have used PSW-CE 3.5 and Jasper Server 4.5 CE for doing this.

1) Analysis Schema

Step 1:
Upload your .xml file as shown in below figure
RightClick on Analysis Views -> Add Resource -> File -> OLAP Schema

Step 2:
* Browse for the file(.xml file, nothing but your schema file) from your local computer
* Give the location where you want to save the schema file.

2) Analysis Connection
* Right click on Analysis connection -> give name and resource ID (Any name and ID)
* Give location to save the connection in your repository and click on Next button.

* As you are already uploaded your schema in Step 1 you need to to browse for the .xml file here again.
* Just you need to give the path of the schema file(.xml) file that you uploaded in step 1
* For example : /analysis/schemas/demo_schema.xml
* Click on Next, again click on Next
* Imp thing you need to Locate your data source.
* In this exmplae I'm locating already available data sources in the jasper server( I externally created and giving the path for that data source).
  For Instance : /datasources/demodatasource

That's it click on submit/OK button.


3) Analysis Views
* Here you can see the visualization of your schema.
* Right click on Analysis View- > Add resource -> OLAP View
* Give name for the view  then click on Next
* Give the connection path that you created in step 2 and click on Next
* Provide MDX query for your analysis and clickon Submit


You are done with uploading/publishing your Schema to Jasper server.

OUTPUT
You can find the output at "Analysis Views" .. there you will see the name of the schema you uploaded .. just click on that schema.. you are ready to analyze your schema.

 Sadakar
BI Developer









Thursday 10 October 2013

Export report output to multiple sheets of excel- Page breaking in jasper iReport or Studio

Hello Community,

NOTE : Find the update section : Updated On  14th April 2015 : Example JRXML

You can break the page where ever you want and export to excel in jasper server. 
For this you need to give  the properties  shown in image to jasper iReport  report.

Right  click on report Name from Reporot Insepctor > Properties -> report properties

net.sf.jasperreports.export.xls.one.page.per.sheet true
net.sf.jasperreports.export.xls.sheet.names.1 Pivot Remit
net.sf.jasperreports.export.xls.sheet.names.3 Detail
net.sf.jasperreports.export.xls.sheet.names.2 Summary
net.sf.jasperreports.page.break.no.pagination apply

 






As shown in the above picture use the page breaks where ever you want to break the report to a page when exporting to excel.


Update 14th April 2015 : Example JRXML
Click Me to Download JRXML Example

Click Me to Download the Sample Excel Output File of the Above JRXML

Quick Screenshot :


NOTE : The above example JRXML runs on PostgreSQL foodmart database.

Server file changes : 

add below line of property in jasperreports.properties file
net.sf.jasperreports.export.xls.one.page.per.sheet=true

Location of the file :
C:\Jaspersoft\jasperreports-server-6.0\apache-tomcat\webapps\jasperserver-pro\WEB-INF\classes

Quick Steps :
1) Keep the elements in User Defined Groups (bands)
2) Take Data sets to be used by elements kept on User Defined groups (bands)
3) Place the break element either at the end of elements.
4) Set Ignore Pagination= true for the report (If you use sub reports do the same).
5) Add net.sf.jasperreports.export.xls.one.page.per.sheet=true property in jasperreports.properties file.
6) set below shown properties for the report.

7) Save the report publish it the server and export the output to Excel.
Sample output image : 

:-)
Cheers..!!!

Wednesday 9 October 2013

start_date/from_date and end_date/to_date parameters - giving default values - iReport

Imp Reference for : daily, weekly, monthly, yearly
http://community.jaspersoft.com/wiki/dynamic-dates-reports

This post let's you know how to use date parameters in SQL query with between operator and using with multi select parameter.

Example Query:

SELECT
        column1, colum2
FROM
        XYZ_table
WHERE
      $X{[BETWEEN], date_field_name_from_table, start_date, end_date}

In this way you can use between operator for start_date and end_date parameters with multi select.
Note that multiselect generally selects the input controls at a time.


Default values for start_date and end_date:
Problem statement:
Find the problem statement in this post.
http://community.jaspersoft.com/questions/819583/default-values-date-parameters

The problem statement which I faced is :
If today is 2013-10-07 then startdate is : 2013-09-16 enddate is : 2013-09-31
if today is : 2013-10-23 then startdate is : 2013-10-01 enddate is: 2013-10-15
The same logic should applicable in january month as well(i.e, for instance if today is : 2013-01-13 then startdate: 2012-12-16 enddate:2012-12-31)
 

Apart from the solution that given in the community, also find below solution

start_date Default Value Expression:

Syntax followed : ternary expression:  condition?True:False  

(
    $P{cal}.get(java.util.Calendar.DAY_OF_MONTH)>15 ?
    $P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 1) :
    ($P{cal}.add(java.util.Calendar.MONTH, -1) ||
        $P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 16))
)

? null : $P{cal}.getTime()

end_date Default Value Expression:

(
    $P{cal}.get(java.util.Calendar.DAY_OF_MONTH) == 1 ?
    $P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 15):
    $P{cal}.set(java.util.Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(java.util.Calendar.DAY_OF_MONTH))
)

? null : $P{cal}.getTime()


In the above two start_date and end_date parameters you can find $P{cal} - which is a parameter created to get the calender dates (in simple words java calender instance)

You must create this($P{cal}) parameter and have to give default value expression before you use this parameter in start_date and end_date parameters.

Default Value Expression for $P{cal} parameters is : java.util.Calendar.getInstance()

NOTE:
for all the input controls Parameters classs is : java.util.Calendar

Sadakar 
(Learning never exhausts the mind)