How to Sort Crosstab by Defined Column Dimension's Value

My previous post maybe to long, so I will shorten it. I hope this time someone will help me. Please... :mellow:
I need to know how to sort crosstab by a defined value from column's dimension. Let's say I have a table of employee with several criteria value (discipline, achievement, education level and age). All of them are numeric field. How to sort row of the crosstab table based on achievement, then education level, then discipline, and lastly is age. But still maintain the column order.

Find more posts tagged with

Comments

yus2aces

I am able to find the solutions, but strangely the sorting works only to 2 sort keys. The 3rd and 4th didn't work at all.To make sure my sorting expression is right, I have delete the first 2 sort keys. And the previous 3rd and 4th key is working.I need to know whether the sort keys has limitations only to 2 sort keys?Or what should I do to make it work like what I want to?

yus2aces

I am still waiting someone who can help me solve this issue, please.

My method to sort crosstab is by adding the sort key based on column's dimension consist of 2 levels of grouping, and I already fill in the row member value for those 2 levels' value. My sorting structure:<ul class='bbcol decimal'><li>grand total column </li><li>column level 1 value is Cat1 and column level 2 is Subcat1 </li><li>column level 1 value is Cat2 and column level 2 is Subcat5 </li><li>column level 1 value is Cat2 and column level 2 is Subcat6</li></ul>Only the 1st and 2nd sort key worked, but if I am deleting the first sort key (above), the 2nd and 3rd works but not the 4th one, and so on. BTW, all of the value field is numeric (float and integer). I hope this information could help someone helping me.

johnw

Sorry for the delay in getting a response to this. Yes, you can sort by more than 2 dimensions. Attached is a report design that shows how to do this.

yus2aces

Thank you so much to John Ward - my Guru for replying my problem here. 
 
But I want to sort the crosstab based on column's dimension. To make it easier, I will create an SQL statement to show my problem more clearly. 
<pre class="_prettyXprint">CREATE TABLE cat ( cat_id serial, cat_desc varchar(30));CREATE TABLE subcat ( subcat_id serial, subcat_desc varchar(30), cat_id smallint);CREATE TABLE table ( table_id serial, employee_name varchar(30), subcat_id integer, perf_value smallint, perf_score smallint);</pre>Then with all these tables I am creating SQL statement in BIRT. 
<pre class="_prettyXprint">SELECT table.*, subcat_id, subcat_desc, cat_id, cat_descFROM tableJOIN subcat ON subcat.subcat_id = table.subcat_idJOIN cat ON cat.cat_id = subcat.cat_id</pre>Then I am creating a data cube. 
1. employee_name as the row dimension 
2. cat_id as level 1 column dimension 
3. subcat_id as level 2 column dimension (shorten it into sc_id in the table below) 
4. perf_value & perf_score as the summary value (shorten in the table below as pv & ps) 
 
Then in crosstab, I am creating a grand total for perf_score (ps) value and sub total of ps for each Cat.I am able to show crosstab like this.<pre class="_prettyXprint"> Cat1 Cat1 Cat2 Cat2 sc_id1 sc_id2 Sub sc_id3 sc_id4 Sub GrandEmployee pv ps pv ps Total pv ps pv ps Total TotalMr. A 1.4 8 0 10 18 55.1 4 12.2 10 14 32Mr. B 0 10 0 10 20 23.2 8 120.0 0 8 28Mr. C 0 10 2.5 6 16 38.4 6 34.4 6 12 28</pre>Last thing is the sorting, which I can't solve to work on the way that I want to. 
 
1. The 1st sorting key is the perf_score (ps) grand total (descending) 
2. pv of sc_id1 (ascending)3. pv of sc_id3 (ascending)4. pv of sc_id4 (ascending) Yes... i don't want to have a sort key on pv of sc_id2. I hope it is clear enough and Mr. John Ward or anyone else still able to spare the time to show me the way.

johnw

Can you do me a favor and post the RPTDOCUMENT of the report you are having issues with? You posted the RPTDESIGN on StackOverflow, but it doesn't do me any good without the data that goes with it. To run and get an RPTDOCUMENT, right-mouse click on the RPTDESIGN in the Navigator in Eclipse, and specify Generate Document. Depending on the version, Generate Document might be under a menu called Report. See the attached Screen Shot.

yus2aces

Thank you John for your time. Attached is the RPTDOCUMENT.I hope it can help you help me.

johnw

Can you do me a favor and run the attached RPTDESIGN and generate a RPTDOCUMENT from it? There are a lot of nested data sets in the design so it is hard to extract what I need from it. The attached will make that easier for me.

yus2aces

Thank you so much for the effort. Here is the rptdocument generated from your rptdesign.

johnw

What is happening in your report design is it won't sort past the 2nd sort key because afdelingmain_id is a top level dimension. So every row you are seeing in the cross tab is a unique afdelingmain_id, there isn't more than one, at least not with the cross-tab columns you have. So it won't ever on any of those lower level censusvals. If you move the Cat-Id and Sub-Cat id to rows, you can then add a sort based on Cat-Id, afdelingmain_id, and then any of the measures (censusvals, qlty_value, etc). So the sort is working correctly, but the data is such that you won't see the sorts working correct. By the way, this is one of the trickiest report designs I have ever looked at. Took me a while to get my head around it.

yus2aces

I don't know how to work around on this matter, because the crosstab design was given by my employer.There is no chance for me to change the design. -_-Anyway, thank you so much for your time and effort, John.

johnw

Let me see if I can explain it a little better so can relay it back to your employer, and hopefully they will understand. So the structure of the crosstab looks like this (taking out the table that is doing the lookup for the assistant name:<img src='http://developer.actuate.com/community/forum/uploads/monthly_02_2014/post-3729-0-44545600-1391664056_thumb.png' alt='Posted Image' class='bbc_img' /> [font="arial, helvetica, sans-serif;"]In the above screen shot, we have 3 sorts on the lower crosstab, Cat-id, Sub-cat-id, and [color=rgb(0,0,0);font-size:13px;font-weight:bold;text-align:center;]afdelingmain_id. This is the exact same structure that the above cross tab has for dimensions. So the 3 sorts work fine. The problem is that for every row in afdelingmain_id, there is only 1 cross-tab row. So any further sorts on censusval, qlty_value, etc will be applied, but you won't ever see them because there is only ever 1 row for each [/color][/font][color=rgb(0,0,0);font-family:sans-serif;font-size:13px;font-weight:bold;text-align:center;]afdelingmain_id. In the cross-tab above, it doesn't look like that because the table hides the [/color][color=rgb(0,0,0);font-family:sans-serif;font-size:13px;font-weight:bold;text-align:center;]afdelingmain_id value, so you never know if that sort is being applied. [/color] [color=rgb(0,0,0);font-family:sans-serif;font-size:13px;font-weight:bold;text-align:center;]So lets take a look at a second example:[/color]<img src='http://developer.actuate.com/community/forum/uploads/monthly_02_2014/post-3729-0-44685400-1391664831_thumb.png' alt='Posted Image' class='bbc_img' /> Here, you can see that I moved cat_id and subcat_id to better illustrate. The first 2 sort columns are still cat_id and sub_cat id, but this time [color=rgb(0,0,0);font-family:arial, helvetica, sans-serif;font-size:12.800000190734863px;font-weight:bold;text-align:center;]afdelingmain_id is out of order, because the sort has been moved to censusval. So censusval is the 3rd sort column, and the 4th is qlty_value. However, we never run into a situation where both [/color][color=rgb(0,0,0);font-family:arial, helvetica, sans-serif;font-size:12.800000190734863px;font-weight:bold;text-align:center;]afdelingmain_id and censusval are the same so that qlty_value can show its sort order. [/color] [color=rgb(0,0,0);font-family:arial, helvetica, sans-serif;font-size:12.800000190734863px;font-weight:bold;text-align:center;]But, if I move the sort to where qlty_value is the the 3rd sort value, and the 4th is censusval, it will work as expected:[/color]<img src='http://developer.actuate.com/community/forum/uploads/monthly_02_2014/post-3729-0-52598000-1391665228_thumb.png' alt='Posted Image' class='bbc_img' /> So the problem is, when you move cat_id and sub_cat id to the column area as with your report design, there is no way to sort because each cat_id and subcat_id intersect with a afdelingmain_id multiple time, so there is no way to really determine which intersection should have the sort. I hope that make sense.