nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Sorting in Cross Tab

KapsHere

Hi,

I am designing a BIRT report which uses a cross tab. The cross tab uses a scripted data source and gets the data at run time. In my case the data in the row headings generally has some range and is of the format a-b, where a and b are integers and a is less than b.

BIRT does some internal sorting on the row headings (and sorts the rows alphabetically), which i would like to override. If the row heading is a-b, i want the data to be sorted on a. I tried to use the javascript split function on the sorting tab for this cross tab, but was not able to achieve the required results.

I have created a test report with sample data source to simulate the same problem. Please find attached the sample report with this post.

The BIRT environment details are as follows:
Business Intelligence and Reporting Tools

Version: 2.3.0.v200804251-7N7Y-AYdeIEKS6mGZPHGhs-0vEl6
Build id: v20080618-0630

BIRT Report Designer

Version: 2.3.0.v20080431-7X7n-ECLmlsLx_z-4z0LEaYz-mA8
Build id: v20080618-0630

BIRT Report Runtime

Version: 2.3.0.v20080516-7g890ELRXmproz-xIDPOJ1
Build id: v20080618-0630

Thanks in advance for any help,
Regards,
Kapil

Find more posts tagged with

Comments

KapsHere

To make things more clear, i would like to sort the data on the numerical value of a (assuming the value in row heading is a-b ).

mwilliams

Kapil,

I have played around with trying to split up the value to sort on just the 'a' portion, but have not had any luck. I'll let you know if I figure anything out.

KapsHere

Thanks Michael!

Please let me know if you find a solution to this issue. I am still awaiting my first successful resolution to a BIRT issue from this forum. All the issues I have raised till now are in pipeline OR have been deferred as an enhancement in a future release!

(

Regards,
Kapil

mwilliams

Kapil,

Got it. All you need to do is create a type integer computed column in your dataset that separates that value, then in your sorting on the crosstab, you can use an "attribute" value of your group item (the computed column) in your sorting.

KapsHere

Hi Mike,

My report uses scripted data source, so i don't have the computed columns option! At least i don't see it under Data Set. Is there any other place i should be looking for the same? If not, is there any other solution that you can think of for scripted data source scenario?

Regards,
Kapil

mwilliams

Kapil,

With your scripted dataSource, you should be able to add another column within your java or javascript that does the same thing as the computed column.

KapsHere

Hi Michael,

In my reports with scripted data source, i am using javascript on Data set events (like open, fetch etc) to populate data in the columns in the data set. I have added the 'SortColumn' to the Data Set and I am using the same sort logic on the fetch event.

For some reason, when i try to add a sort key to the cross tab in my report, i dont see the 'SortColumn' in the available data cube. In the report you've attached, when we try to add the sort key, i can see the 'SortColumn' within the first group of the data cube, but the same doesnt happen in my case.

Did you explicitly connect the data cube with the 'sort column' or does it just show up because its a computed column?

What step am i missing here which connects a column in the data set to the data cube group?

I am sure its the 'attribute' thing you've specified in your post. I am not sure how to use it. Could you please tell where i see that option?

Regards,
Kapil

KapsHere

Got it! Its working! Infact its working with a little more complex sorting logic as well, where the data can be a String, number or a range of number.

Thanks a ton Michael! I had this cross tab sorting issue going on from a long time now!

Regards,
Kapil

mwilliams

Kapil,

No problem! Glad I could help. Let us know if you have any other questions.

KapsHere

It seems i celebrated to early!!

the logic works for integer ranges, but if i have data which has integer ranges and Strings, and i chose the data type of the computed column as 'Any' instead of 'Integer', sorting doesnt work. e.g. if i have the following data:
aa, bb, 100-200, 60-40, tt

i would like the sorted output to be as follows:
aa, bb, tt, 60-40, 100-200

I wrote some logic in javascript for sorting such data. It works fine for data in tables. You can sort the data in the table detail rows and also in table group rows, but with a cross tab, the same logic doesn't work.

The reason is, to sort the data sample shown above, you need to have the type of computed column as 'Any' instead of 'Integer'(although its the same with tables as well) and for some reason cross tab doesn't like it!

Any ideas/pointers on this issue would be appreciated!

Regards,
Kapil

mwilliams

Kapil,

If in your computed column, you change make it string type and make the numbers strings as well, does it not sort in an acceptable way?

KapsHere

Sorting nos like strings is what the cross tab is doing by default. e.g.

if the column type is 'String' and it has the following data: a,5,12,b

cross tab sorts them as a,b,12,5
but i want a,b,5,12

I think i have found a work around!

I let the computed column type as Integer, and in the javascript code, i check if the data is a String, i return a -1(minus 1) as the sort key. So it gets a -1 as the sort key for all String data, (all of the numeric data i have is positive integers) and then it sorts all the Strings alphabetically!! which is exactly what i wanted.

I seems its working for me now, but i am holding my celebrations till the time 110% sure.

Regards,
Kapil

KapsHere

Attaching the test report file if somebody is interested in looking at the javascript code that worked for me.

The javascript code is in the expression builder for computed column.

mwilliams

Excellent! Glad you got it working. Let us know if you have any other questions.

keith_c06

I've read the previous posts in this thread and I understand the concept of creating a "sortBy" column, this is especially easy when using a scripted data source. I can then get the data cube to sort by that "sortBy" value, but cannot get it to show the associated text values. For example, I am trying to sort days of the week (Sunday = 1, Saturday = 7). If I know that my "sortBy" id will always signify the day of the week, it is easy to make a week day name show up, but I do not know what the value will be. It will be determined by the business logic, so it could be weekdays, month names, or some other custom combination.

I have attempted to duplicate your posted report to no avail. My problem comes in trying to set the sort key for the cross tab. I am unable to get the cross tab to see the "sortBy" value, even though it is a part of the data cube that the cross tab is based from.

Any suggestions?

Thank you,

Keith

keith_c06

Never mind my previous post. If I could only get out of my own way and think for a minute, I would be just fine.

mwilliams

keith_c06,

So, you figured it out?

sulik

Hi Michael, 
 
I'm looking at the sample design you attached here. Do you know if it's possible to manually sort the columns? Right now it goes "Classic Cars - Motorcycles - Planes - Ships", but what if I wanted to display the columns as "Motorcycles - Ships - Classic Cars - Planes"? Would that be possible? Thanks. 
 
<blockquote class='ipsBlockquote' data-author="mwilliams">Kapil, 
 
Got it. All you need to do is create a type integer computed column in your dataset that separates that value, then in your sorting on the crosstab, you can use an "attribute" value of your group item (the computed column) in your sorting.</blockquote>

mwilliams

Hi sulik,

If you knew the values of the field you wanted to sort, you could create a computed column in your dataSet that assigned an order value to those however you wanted.

sulik

Hi Michael, 
 
The values of the field are all the same, so I don't think I can sort that way. 
 
Is there anyway to sort the columns by using the Sorting tab in the Property Editor for the crosstabs? 
 
Thanks 
 
<blockquote class='ipsBlockquote' data-author="mwilliams">Hi sulik, 
 
If you knew the values of the field you wanted to sort, you could create a computed column in your dataSet that assigned an order value to those however you wanted.</blockquote>

mwilliams

sulik,

If all of the values of the field that you want to sort by are the same, then how are you wanting to sort it?

sulik

<blockquote class='ipsBlockquote' data-author="mwilliams">sulik, 
 
If all of the values of the field that you want to sort by are the same, then how are you wanting to sort it?</blockquote>
 
Hi Michael, 
 
Let's say that the current report looks like this: 
 
Classic Cars - Motorcycles - Planes - Ships 
X

X 
X

X

X 
 
Where every value is an X or blank. I want the report to look like this: 
 
Motorcycles - Ships - Classic Cars - Planes 
X

X

X 
X

X 
 
I'm trying to figure out how to sort purely by the column header (Classic Cars - Motorcycles - Planes - Ships)

mwilliams

sulik,

So, what I said before would be the way to do it. You would create a computed column in your dataSet and assign all the Motorcycle rows a 1, all the Ships rows a 2, and so on. Then you could use this field as the dimension rather than "product line" and map the correct string values back onto the header rather than showing 1, 2, etc... Let me know if you have questions.

sulik

Michael, 
 
I've been wrestling with this on and off the past couple days, and can't seem to get anywhere with assigning values. I must admit, I'm quite new at BIRT, and scripting in general. Would you be able provide a sample report of what you described below? 
 
Thanks for all your help 
 
<blockquote class='ipsBlockquote' data-author="mwilliams">sulik, 
 
So, what I said before would be the way to do it. You would create a computed column in your dataSet and assign all the Motorcycle rows a 1, all the Ships rows a 2, and so on. Then you could use this field as the dimension rather than "product line" and map the correct string values back onto the header rather than showing 1, 2, etc... Let me know if you have questions.</blockquote>

mwilliams

sulik,

What version of BIRT are you using?

sulik

<blockquote class='ipsBlockquote' data-author="mwilliams">sulik, 
 
What version of BIRT are you using?</blockquote>
 
It would be version 2.2.2. I would use 2.5.1, but I'm using BIRT in conjunction with IBM Reqpro, and that only supports 2.2.2.

mwilliams

sulik,

Here's a report that you can check out. It shows the standard ordered crosstab and the one that I created a custom order with by using a computed column and mapping. I created it with 2.2.2. Hope this helps. Let me know if you have questions.

sulik

<blockquote class='ipsBlockquote' data-author="mwilliams">sulik, 
 
Here's a report that you can check out. It shows the standard ordered crosstab and the one that I created a custom order with by using a computed column and mapping. I created it with 2.2.2. Hope this helps. Let me know if you have questions.</blockquote>
 
That did it! Thanks so much for your help!

mwilliams

Great! Glad to help. Let us know whenever you have questions!

vash

Michael,

I have read the posts earlier and I downloaded the report design in which you showed how to sort the crosstab.
I did the same thing in my report - and for some reason it doesnt sort.
I have attached the design - can you please check what am I doing wrong.

Thanks,
Vash