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

Saturday, August 06, 2016

Playing with variables for group level summations with Increment Type and Reset Type for multi level group aggregations

Hi, 

In this post you will see how to play with variables for group level summations with increment type for repeating "sales" value at detail level. 

The Use Case would be as follows
           4th group level display nothing
           3rd group level get only distinct values of "sales" from detail.
           2nd group level get the summation of 3rd group level values  
           1st group level get the summation of 2nd group level values and 
           in the detail level, display "customer name" and "sales" data. 
           
Software Details to Run this example
Example developed version : 6.2 Pro Studio, foodmart db of postgres in which a sample data populated in a table called test_jasper_2

For the sample data click on this postgresql backup file and restore it using pgAdmin-3 client tool on foodmart database. 


The sample output will look like as shown in below image.  
(Click on image to get best view of the content)

Solution : (Assuming that you have basic idea of Studio bands, table component and variables creation )
1) Create a data for the above sample data with 4 groups
Group 1 : Region 
Group 2 : Country
Group 3 : State and 
Group 4 : Category

2) Design the JRXML as shown below using TABLE component ( Colours are given to differentiate each group)


3) The core part is to play with variables, its increment type and reset type. 

If you have a look at the sample output in first image, the details for "sales" field are repeating and display nothing at 4th group level(i.e., category group level). 

We need roll up aggregations starting from 3rd group and solution is as follows.

Display Distinct values at 3rd group level (i.e., State level)
Create a variable, say : var_StateLevel and give 
Expression=$F{sales}
Increment Type = None
Reset Type = State

Once the variable is created drag and drop it to the place holder as shown in design image and give "Evaluation Time=State"


Display Summation values of 3rd group(State level) level in 2nd Group(Country level)
Create a variable, say : var_CountryLevel and give 
Expression=$V{var_StateLevel}
Increment Type = State
Reset Type = Country

Once the variable is created drag and drop it to the place holder as shown in design image and give "Evaluation Time=Country"



Display Summation values of 2nd group(Country level) level in 1st Group(Region level)
Create a variable, say : var_RegionLevel and give 
Expression=$V{var_CountryLevel}
Increment Type = Country
Reset Type = Region

Once the variable is created drag and drop it to the place holder as shown in design image and give "Evaluation Time=Region"

4) Save the report and run. It will produce the output as shown in very first image. 

I hope it helps someone in community. (Example of JRXML is attached in the top download link)

Cheers.!
Sadakar Pochampalli