Crosstab rows and columns sorting

I came across the following devshare recently, but this was written for BIRT 2.5, while I'm using BIRT 2.2.1. 
 
<a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/1172-sorting-a-crosstab-by-a-field-not-in-the-crosstab/'>http://www.birt-exchange.org/org/devshare/designing-birt-reports/1172-sorting-a-crosstab-by-a-field-not-in-the-crosstab/</a> 
 
The other issue is that I don't seem to be able to download the example, so I cannot actually validate that what I'm doing is correct. 
 
It seems to me that this would address my problem exactly, but I could not actually get the example working. I created the attribute, but on trying to setup the sort on the crosstab, I did not have the option to select the attribute directly. I therefore manually updated the XML for the key entry to match my attribute. 
 
At runtime, I got the following error : 
<blockquote class='ipsBlockquote' >
The level name "dimension["Group1"]["CASETYPE"]["SORTORDER"]" is not found. </blockquote>
 
Has anyone tried something similar? 
 
Rgds, 
Cedric

Find more posts tagged with

Comments

mwilliams

Hi Cedric,

I just downloaded the example. Not sure what the issue is that you'd be having with this. I can email you the example if you'd like. I'll take a look at recreating it in 2.2 as well.

mwilliams

I tried in 2.2.1 and got the same as you. It seems that the attribute is not accessible for sorting. It is only accessible for filtering it seems in this version. Is the field that you want to include as an attribute to sort by unique to the dimension field? Or is there possibly more than one dimension field value with the same attribute value?

cypherdj

Hi Michael,

sorry for the late reply, been busy working on other requested changes. The column would be added to the dataset, and is unique to the cube grouping dimension. So the data set would look like:

|CatSortOrder|Category|Date|recordCount|
|5|Sales|2010-05-03|350|
|2|HR|2010-04-02|230|
|5|Sales|2010-01-23|123|
|3|Accounts|2010-04-12|32|
|2|HR|2010-06-03|34|

And the resulting crosstab should be presented like:

2010
Q1 Q2 Q3 Q4
HR ... ... ... ...
Accounts ... ... ... ...
Sales ... ... ... ...

I read in a different post of yours that it might be possible to include the sort column as a dimension of the cube, displayed as hidden in the crosstab.

If that is a possibility, I would guess that this would also address our problem without the need to upgrade to a more recent version,

Thanks,
Cedric

mwilliams

Cedric,

Yes, if you make the field apart of the crosstab in a way that you can hide it, you can work around the issue at hand. Another way, since your numeric sort field and category field are unique to each other, you could simply use the sort field in your cube and crosstab, store conversion values in variables for your dimensions, and then replace the sort values with the variable stored category values in script. Let me know.

cypherdj

Hi Michael, 
 
I created an extra dimension to the cube, then modified my crosstab to include this extra field. It seems the ordering of the fields in the crosstab matters for the sorting to work. The SORTORDER needs to be first: 
| Year | 
| Quarter | 
|SORTORDER|CASETYPE| COUNT | 
 
I then used the visibility for the data element field (visibility is not available for the cell itself) and hid the dimension. But this leaves the containing cell to be displayed, even when I set its width to 0%/0mm/0in. 
 
is there anything else I would need to do to effectively hide this column altogether? 
 
Rgds, 
Cedric

mwilliams

Cedric,

Try selecting the cell, going to the advanced properties, going to the "section" section, and then making the display value "no display". Hopefully that option was there in 2.2.1. Let me know. If it doesn't work for you, you can use the old hack workaround of disguising the blank cell using borders that match your background color.

cypherdj

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="71195" data-time="1291905404" data-date="09 December 2010 - 07:36 AM">
Cedric, 
 
Try selecting the cell, going to the advanced properties, going to the "section" section, and then making the display value "no display". Hopefully that option was there in 2.2.1. Let me know. If it doesn't work for you, you can use the old hack workaround of disguising the blank cell using borders that match your background color. </blockquote>
 
Thanks for your help with this issue Michael, your suggestions were once again spot on! 
 
Just for info, the section "display: none" did not quite work as intended at row level (see crosstab-display-none.jpg), but worked fine at column level, so I've used a mix of "display: none" and border styling to get the desired effect (see crosstab1.jpg) 
 
Thanks again!