Pivot Table in Jasperstudio

Posted on by By admin, in Business Intelligence, Jaspersoft, Open Source Business Intelligence | 0

Pivot Table in Jasperstudio

Often there comes the requirement  to create pivot table while creating reports in  jasper. No doubt one can always use crosstab component. But sometimes crosstab doesn’t give data in required form as grouping is done on column and row basis and measure’s are always present in the end. One such limitation of crosstab I faced when I received the requirement to create report having row groups with calculation based fields(measure) added not in the end of all columns. There I solved the issue by using table component and creating row groups. In this blog I’ll share my approach to solve the issue discussed before by using table component in jasper.

Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.

Here are the steps to create pivot table:

  • Create the query for the report. Here I am using Foodmart database and the query I used is as follows:
    select brand_name, gross_weight,pc.product_family, pc.product_department, product_name,product_category
    From product
    join product_class pc on product.product_id= pc.product_class_id
    where gross_weight between 6 and 8.5
    group by 1,2,3
  • Create dataset using the above query. Using this dataset create the table. The table needs to have header and the detail rows.
  • Create group header in the table component and create all the columns. For calculation field, drag the field value to the desired column and the variable will be created having the field as the “variable expression”. Select the calculation on variable which can be sum, count, average etc.
    Screen like follows is going to appear:-


Make the evaluation time in variable properties as ‘Group 1’. Similarly you can create different variable which functions as measure at different places in the table. Please note that these variables work similar like the variables in crosstab which are generated internally in jasper.blog2


Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.

Thus, you can create your own customized pivot table as per the requirement. Here, I am sharing the final output and the jrxml of the report. Hope It helps!!

File: pivot_table.jrxml


Best Open Source Business Intelligence Software Helical Insight is Here


A Business Intelligence Framework


Nisha Sahu
BI Developer



0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments