Cross Table Columns Sorting..

Hi All,

A SQLQuery is returning the following results from DB.

CompanyName Bucket
Steel 1day
Cement 2day
Infra 16-30 day
Cement 7-15 day
Steel 2day
Cement 7-15 day
Infra  1day
Cement 16-30 day
Infra 1day
Steel 16-30day


I have defined cross table as below :

* Company name as row group.

* Bucket as column group with Ascending order.

* Bucket as Measure.

Now the resultant cross table is displaying as below :

Company 1day 16-30 day 2day 7-15 day
Steel 1 1 1 0
Cement 0 1 1 2
Infra 2 1 0 0

 If you observe colums are not displying in ascending order. It is taking ascending order as 1day, 16-30day, 2day, 7-15day.

But It should be like 1day, 2day, 7-15day, 16-30day.

How can i achive this. Please suggest me. 

Thank you.

nareshbangaram24's picture
Joined: Feb 20 2018 - 11:25am
Last seen: 5 years 1 month ago

Could anybody please suggest me. 

nareshbangaram24 - 5 years 1 month ago

1 Answer:

Crosstabs sort the values alphabetically when the value is alphanumerical. You will either need to assign these buckets some numeric values instead and then If-Then them back to text values or create a custom comparator. Here is a similar problem with Month sorting:

Friendly User's picture
Joined: Oct 8 2009 - 5:59am
Last seen: 3 weeks 1 day ago