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.
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
Thanks,
Nisha Sahu
BI Developer
Creating pivot tables Jaspersoft Community crosstab How do I create a pivot table in Jaspersoft report How do I make a pivot table editable How do you use the crosstab in Jasper Ireport jasper studio measure pivot table Pivot Table in Jasperstudio TIBCO Jaspersoft Studio User Guide