Playing with variables for group level summations with Increment Type and Reset Type for multi level group aggregations
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
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
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
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)