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.
In case if you have any queries please get us at support@helicaltech.com
Thanks,
Prashanth Kasoji
Free Open Source BI Platform
- AI-Powered Analytics
- Embedded Analytics
- Single Sign-On (SSO)
- Multi-Tenancy
- Paginated Canned Reporting
- Interactive Dashboard
- White Labeling