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