how to swap the rows and columns in crosstab dynamically

[Sarayucog]

Hi all,

    how to swap the rows and columns in crosstab dynamically.


Thanks in advance

[laxmanrao83]

Dynamically Swap Rows and Columns of a Crosstab Report

The technique explained below will enable you to build functionality in a report studio report allowing users to dynamically swap the rows and columns of a crosstab report in IBM Cognos viewer.

The following steps highlight this technique:
In Report Studio, create a new Crosstab report based on the GO Sales and Retailers Package.
Do not add any items to the cross tab. We will do it later. 
In Query Explorer, click Query1.
From the Insertable Objects pane, add Order Method, Order Year and Quantity to data items.
Use the Page Explorer to return to Page1.
From the Toolbox, drag a Value Prompt to the immediate left of the blank crosstab, and assign the parameter name Disp_Param.
Click Finish.
In the report page, click the value prompt you just created to select it, and set the following General properties:
Property 'Required' set to 'No'
Property 'Auto Submit' set to 'Yes' 
Property 'Select UI' set to 'Radio Button group'
Still in the Properties for the value prompt, under Data, click the ellipsis in the Static Choices box. 
Click the Add icon. 
In the Use box, type 'Original', and in the Display box, type 'Original Display', and then click OK.
Create a second static choice with Use and Display values of Swap and Swap Rows and Columns respectively.
Click OK.
In Query Explorer, for Query1, add a new data item CalcRow with the following expression:
if (?Disp_Param? = 'Original')
then ( cast_char([Order method]) )
else ( cast_char([Order year]) )
Add another calculated data item called CalcCol with the following expression:
if (?Disp_Param? = 'Original')
then ( cast_char([Order year]) )
else ( cast_char([Order method]) )
In the Page Explorer, add the data item CalcRow to rows, add CalcCol to columns, and add Quantity to measures.
Click the value prompt that you created earlier, and in the Properties pane, under General, set the default value for the prompt to a simple selection of 'Original'.
Save and run the report.
Now end users can swap the rows and columns in report viewer by clicking one of the options provided.

[Sarayucog]

Thanks a lot Laxman. You gave me step by step procedure I appriciate you. Its perfectly working.


Sarayu