Prerequisites: Jaspersoft Studio
Database: Postgres – Foodmart
In some cases,there might be a scenario that we want to display the columns in our required order. But cross-tab automatically displays the columns in ascending/descending order(A-Z) irrespective of the Order By clause that we involved in query.
Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.
For Example, I have tried to display the profit/loss of warehouse sales of every day of a week using cross-tab.
Query:
select concat(round((warehouse_sales-warehouse_cost)*100/warehouse_cost,2),’%’)
as percentage,warehouse_name,product_name,the_day,day_of_week
from inventory_fact_1997 i inner join warehouse w on i.warehouse_id=w.warehouse_id
inner join time_by_day t on i.time_id=t.time_id
inner join product p on i.product_id=p.product_id
where $X{IN,warehouse_name,warehousename} and $X{IN,product_name,productname}
and $X{IN,day_of_week,weeknumber} and warehouse_cost >0
Cross-tab1:
Output:
As I explained above, the cross-tab is automatically inserting columns in ascending order.But I want to display the days from Monday to Sunday respectively.
For that I have added an extra field as a column before the days column ( see below image cross-tab2 ) which contains the numbers of individual day, So with the help of this field we can arrange the days column in our required order.
Cross-tab2:
Output:
If you don’t want to display the day number in output you just need to hide it by reducing its height to zero in cross-tab, This is one way that you can easily achieve your problem.
Best Open Source Business Intelligence Software Helical Insight is Here
A Business Intelligence Framework
In case if you have any queries please get us at support@helicaltech.com
Thanks,
Prashanth Kasoji
BI tool Business Intelligence Can we apply sorting on Column values in Cross tab crosstab Helical Insight How do I sort columns in crosstab report? How do you sort a crosstab query in Access How to Add sorting to Table item and Crosstab item How to Arrange Columns in Required Order in Crosstab How to change the Sort Order of Column Headings How to sort columns in a predefined order in crosstab How to sort on a specific column in a crosstab open source open source BI What is the default sorting order for a character column in a crosstab