| Sort Crosstab with several criteria [message #1228849] |
Wed, 08 January 2014 02:46 |
Eclipse User |
|
|
|
I have a table tblCat.
Cat_ID | Desc
1 | Cat 1
2 | Cat 2
3. | Cat 3
Another table is tblSubCat.
SubCat_ID | Desc
1 | Subcat 1
2 | Subcat 2
3 | Subcat 3
4 | Subcat 4
The Detail table for the crosstab, which is performance table based on the SubCat above.
ID |Emp Name | Subcat_ID | % |
1 | John | 1 | 2.2 |
1 | John | 2 | 0.0 |
1 | John | 3 | 0.0 |
1 | John | 4 | 3.3 |
2 | Jim | 1 | 31.3 |
2 | Jim | 2 | 0.0 |
2 | Jim | 3 | 15.3 |
2 | Jim | 4 | 33.0 |
3 | Jane | 1 | 10.5 |
3 | Jane | 2 | 28.3 |
3 | Jane | 3 | 43.6 |
3 | Jane | 4 | 10.2 |
I can build below crosstab table based on the 3 tables above (see attached picture). BTW pls ignore the Value column, it comes from another table to define the performance in value.
| CAT 1 | CAT 2 | CAT 3 | |
NAME | Subcat 1 | Subcat 2 | Subcat 3 | Subcat 4 | GT |
| % | Val | % | Val | % | Val | % | Val | |
============================================================
John | 2.2| 10 | 0 | 10 | 0 | 10 | 3.3| 10 | 40 |
Jim |31.3| 8 | 0 | 10 |15.3| 7 |33.0| 8 | 33 |
Jane |10.5| 10 |28.3| 2 |43.6| 4 |10.2| 10 | 26 |
The problem is how to make a custom sort based on several criteria :
1. By GT column (decreasing) ---> I can do this
2. By % of Subcat 1 column (increasing)
3. By % of Subcat 2 column (increasing)
4. By % of Subcat 3 column (increasing)
I have tried to apply the method in this article developer.actuate.com/community/devshare/_/designing-birt-reports/1172-sorting-a-crosstab-by-a-field-not-in-the-crosstab/#description.
My method are:
1. Create a computed column for 3 value in Data Set, for example :
a. the 2nd criteria (by % of Subcat 1 column), the expression in computed column is if(subcat_id == 1) row["pct"];
b. the 3rd criteria (by % of Subcat 2 column), the expression in computed column is if(subcat_id == 3) row["pct"];
c. the 4th criteria (by % of Subcat 3 column), the expression in computed column is if(subcat_id == 4) row["pct"];
2. In Datacube, get these 3 computed column as the attribute of emp_name column. Sort it by this exact level.
3. In Crosstab properties in Sorting tab, put the 3 attributes such as below,
dimensions["GroupName"]["Emp_Name"]["criteria1"]
I can make the crosstab table sorted on first criteria (2nd criteria of my method above), but it didn't work on the second and third one.
I don't know where I've done wrong. Anybody please help me.
I need your guidance for my problem.
Thank you in advance.
Attachment: sample2.jpg
(Size: 24.66KB, Downloaded 405 times)
|
|
|
Powered by
FUDForum. Page generated in 0.03378 seconds
Home