How to Arrange Columns in Required Order in Crosstab

Posted on by By Prashanth, in Business Intelligence, Helical Insight | 0

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.

Get your 30 Days Trail Version

For Example, I have tried to display the profit/loss of warehouse sales of every day of a week using cross-tab.


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 tab columns


crosstab 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 tab columns


Cross tab 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


Prashanth Kasoji

Helical IT Solutions Pvt Ltd

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