Jaspersoft BI Tools

iReport,Jaspersoft Design Studio,Jaspersoft Server, Schema Workbench,Analysis,Dashboards, Self Service BI, Domains,Topics,Ad-Hoc Reports, Jaspersoft ETL, Embedding BI,HTML-5 charts, Fusion Charts,JFree Charts,Drill down reports , Drill down Dashboards,Visualize.js, Performance Tuning, D3 Charts, Jaspersoft Customization

Wednesday, February 19, 2014

Sring Type fields as Measures in Cross Tab using Japser iReport and Importent tricks on cross tab

This post is having theory but will give you the exact information,reasons and solutions on cross tab that you are looking for.... 

1) Can you fill String type of filed values on cross tab Measures ? Does it allow when you design the cross tab ?
  • Cross tab by default allow you to fill with only one measure at the time of creation. But later on you can add as many measures you need. 
  • Generally what one can think that cross tab measure only allows Integer or Long type of fields but it also allows String or Other type of fields and you can also to calculations on the values which are in the form of strings. 
  • Example : 
  • You have created a cross tab having 1 row group, 1 column group and 1 measure(Or, 2 row groups , 2 column groups,1 measure).
  • Let us assume that you want to add one more measure to your cross tab: Let us say your field name is time from SQL query which is the form of string having shape hh:mm:ss(i.e., you field is : $F{time}).
  • Now you are going to add this field as 2nd measure to the cross tab. How ?
Steps
  1. Right click on measure-->Click add measure.
  2. Name : time
    Measure class: java.lang.String ( Note that this is a measure but you are giving it as String)
    Value Expression : $F{time}
    Calculation : First (This selection will make the cross tab to bring the value of the field irrespective of type)
    Incrementer Factory :  ---------
  3. Increase the width of already existing Measure and then drag and drop the newly created measure.
  4. Adjust the height and width for both of the measures and save the report.
  5. View the preview. 

Important points 
1) Summations of measures for each level of row group
Assume that you need to add totals of a measure for every row group.
for example you have 4 row groups in your cross tab and want to calculate the sum of every measure for each level of row group ...

When you explicitly adding  custom row groups , you have to select Total Position Property = End
By default it is Never... You can observe the Extra space for the summation values where you can drag and drop your calculations of your measures or simply drag and drop the measure which you have summation.. Cross tab by default gives you the summation of each row level group and Grand total value for the single calculated measure.

You need to repeat the same above procedure for the remaining row groups.


2) Stretch Type =  Relative to band Height
This is useful when you work with row groups and column groups....
1) 2nd field is grouped by 1st field
2) 3rd field is grouped by 2nd field
   4th field have 3 data values
In that case you will get output data correctly but the formatting will be missing ( i.e., the borders of the cells will miss).. To over come this thing you need to set Stretch Type =  Relative to band Height


NOTE:
There are no limitations on adding cross tab row groups and/or column groups. But often we get 3rd variable of row group is not acceptable when adding 4th row group.. You need to use "" double quotes with + for that variable or else take a fresh report and keep on adding row groups.. This must work.