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; } } }

Dynamic Sorting on Derived Columns of Crosstabs

Rin

Hi, 
 
I'm trying to do dynamic sorting on crosstabs, where the users can specify which column they would like to order the rows based on . 
 
I've read 
<a class='bbc_url' href='http://birtworld.blogspot.com/2010/02/birt-crosstab-scripting.html'>http://birtworld.blogspot.com/2010/02/birt-crosstab-scripting.html</a> 
and 
<a class='bbc_url' href='http://www.birt-exchange.org/devshare/designing-birt-reports/1201-add-sort-to-crosstab-using-de-api-in-script/#description'>http://www.birt-exchange.org/devshare/designing-birt-reports/1201-add-sort-to-crosstab-using-de-api-in-script/#description</a> 
 
With the help of the above links, I can do dynamic sorting on aggregation columns with API. But for the Derived Columns , it seems does not work. 
 
 
For example, amount of sales, amount of rent, and amount of tax are 3 aggregation columns of the crosstab, I can do sorting on any of them using API. 
 
But now I have a derived column [amount of mystery income], whose expression is total([amount of sales]* [amount of rent] *[amount of tax]) / total([amount of rent]*[amount of tax]), is there someway to do dynamic sorting on this derived column please? 
 
Code: 
 
var eF = reportContext.getDesignHandle().getElementFactory(); 
var srt = eF.newSortElement(); 
 
srt.setKey("data[\"amount of sales_GroupName/Store_Name\"]");//here, the parameter "amount of sales_GroupName/Store_Name\" is name of a aggregation column. If I replace it with a derived column name ,then the hole script will no longer work. 
 
var xtab = reportContext.getDesignHandle().findElement("Crosstab_Name"); 
var xtabhandle = xtab.getReportItem(); 
 
var rws = xtabhandle.getCrosstabView(ICrosstabConstants.ROW_AXIS_TYPE); 
var lvh = rws.getDimension("GroupName").getLevel(0); 
 
lvh.getModelHandle( ).add( ILevelViewConstants.SORT_PROP, srt ); 
 
 
 
Thanks in advance.

Find more posts tagged with

Comments

mwilliams

Can you set up a sample report with your same setup? I'll give it a whirl.

Rin

Hi Michael, I just resolved the problem by an accident.

Thank you all the same ^^

It seem the name of the derived columns I got from the column binding window is actually wrong. After I found the right name for the derived columns, the API can handle the sorting for those derived columns now

mwilliams

Ah, yeah, getting a crosstab field can be confusing sometimes. There are different ways to access crosstab values depending on where you are trying to access it. Glad you found the solution!