Dynamic Column Sorting in Crosstab

[SynexusStuart]

Hi All,

I have a Crosstab which has some nested columns and I would like users to be able to sort the whole crosstab in HTML by clicking on the nested crosstab headers.

I know there are quite a few resources on crosstab sorting and dynmaic sorting of lists in HTML but I was wondering if anyone could put on the right track (assuming this is possible) before I spend hours trying things out.

Cheers
Stuart

[SynexusStuart]

Anyone?

[Bindiya]

Hi, Here is a way to achieve it.
1. Create a value prompt with as many static choices as you have cols in Crosstab
2. In each col header place  HTML tags (by unlocking)
3. This HTML holds a DIV tag which calls a function on clicking the col header
4. In the function, using Javascript, we select the corresponding static choice of value prompt. So say now we click 4th col, 4th option in the value prompt would be selected. So basically we are storing the col number which is clicked on by user in that value prompt.
5. Now, in the query, we create a data item for sorting, where in we use a case statement to identify which value is selected in the prompt and then pass the corresponding query item.
6. This newly created data item needs to be selected in the "sorting" property of crosstab rows

Hope this should fulfill your requirement.

Code Select Expand


<report xmlns="http://developer.cognos.com/schemas/report/4.0/" expressionLocale="en-us">
<modelPath>/content/package[@name='GO Sales (query)']/model[@name='model']</modelPath>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Quantity" aggregate="total"><expression>[Sales (query)].[Sales].[Quantity]</expression></dataItem><dataItem name="Margin" aggregate="total"><expression>[Sales (query)].[Sales].[Margin]</expression></dataItem><dataItem name="Promotion code" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Sales].[Promotion code]</expression></dataItem><dataItem name="Sort"><expression>case when (?HP?='1') then [Quantity] else [Margin] end</expression></dataItem></selection>
</query>
</queries>
<layouts>
<layout>
<reportPages>
<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>
<selectValue required="false" parameter="HP" name="HP"><defaultSelections><defaultSimpleSelection>1</defaultSimpleSelection></defaultSelections><selectOptions><selectOption useValue="1"><displayValue>1</displayValue></selectOption><selectOption useValue="2"><displayValue>2</displayValue></selectOption></selectOptions><style><CSS value="width:0.1px;height:0.1px"/></style></selectValue><crosstab refQuery="Query1" horizontalPagination="true" name="Crosstab1">
<crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents/></crosstabCorner>


<style>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Quantity" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><HTMLItem>
<dataSource>
<staticValue>&lt;script&gt;
function fun1()
{
var f = getFormWarpRequest();
var v1=f._oLstChoicesHP;
v1[2].selected=true;
SetPromptControl('finish');
}
&lt;/script&gt;

&lt;div id="c1" onclick="fun1()"&gt;</staticValue>
</dataSource>
</HTMLItem><textItem><dataSource><memberCaption/></dataSource></textItem><HTMLItem>
<dataSource>
<staticValue>&lt;/div&gt;</staticValue>
</dataSource>
</HTMLItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Margin" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><HTMLItem>
<dataSource>
<staticValue>&lt;script&gt;
function fun2()
{
var f = getFormWarpRequest();
var v1=f._oLstChoicesHP;
v1[3].selected=true;
SetPromptControl('finish');
}
&lt;/script&gt;

&lt;div id="c2" onclick="fun2()"&gt;</staticValue>
</dataSource>
</HTMLItem><textItem><dataSource><memberCaption/></dataSource></textItem><HTMLItem>
<dataSource>
<staticValue>&lt;/div&gt;</staticValue>
</dataSource>
</HTMLItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Promotion code" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><sortList><sortItem refDataItem="Sort"/></sortList></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows></crosstab>
</contents>
</pageBody>
<pageHeader>
<contents>
<block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>
<contents>
<textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>
<dataSource>
<staticValue/>
</dataSource>
</textItem>
</contents>
</block>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="ph"/>
</defaultStyles>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter>
<contents>
<table>
<tableRows>
<tableRow>
<tableCells>
<tableCell>
<contents>
<date>
<style>
<dataFormat>
<dateFormat/>
</dataFormat>
</style>
</date>
</contents>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<contents>
<pageNumber/>
</contents>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<contents>
<time>
<style>
<dataFormat>
<timeFormat/>
</dataFormat>
</style>
</time>
</contents>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableCells>
</tableRow>
</tableRows>
<style>
<defaultStyles>
<defaultStyle refStyle="tb"/>
</defaultStyles>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="pf"/>
</defaultStyles>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</page>
</reportPages>
</layout>
</layouts>
<classStyles>
<classStyle name="pd_1" label="Excellent">
<CSS value="background-color:#009933; color:#FFFFFF;"/>
</classStyle>
<classStyle name="pd_2" label="Very good">
<CSS value="background-color:#FFFFFF; color:#009933;"/>
</classStyle>
<classStyle name="pd_3" label="Average">
<CSS value="background-color:#FFFFFF; color:#CC9900;"/>
</classStyle>
<classStyle name="pd_4" label="Below average">
<CSS value="background-color:#FFFFFF; color:#990000;"/>
</classStyle>
<classStyle name="pd_5" label="Poor">
<CSS value="background-color:#990000; color:#FFFFFF;"/>
</classStyle>
</classStyles>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes></report>


[SynexusStuart]

Hi Diya,

That is basically what i need. I do need to enhance it slightly and I'm stuck again. I need to nest the Quantity and Margin Measures under something else, such as [Country] and I still need the sort to work on individual columns.

Is this possible?

Cheers
Stuart

[Bindiya]

Yes it should be possible..
Place [Country] above [Quantity] and [Margin]. Now, edit the sorting property of Corsstab node member and include [Country] too (previously we just had [Sort]). Here the order is important, in the sort list first should be [Country] then [Sort].

[SynexusStuart]

Thanks again Diya, unfortunately this does not seem to work.

Stuart