BSP Announces Our Newest          Professional Services Offering

Register for Cognos Forum and Save $600!

Dynamically Sort a List Report Without Using JavaScript

• Create a simple list report based on the 'Go Sales and Retailers' package.
• Add 'Product Line', 'Product Type', 'Product Name', 'Quantity' and 'Revenue' to the report. 
• Add a value prompt to the report page above the list report. Name the parameter p_sortcolumn. Do not create a parameterized filter and a new query.
• Add another value prompt next to the one above and name the parameter p_asc_desc. Do not create a parameterized filter and a new query.
• Add a prompt button after the value prompts above. Set the type property to Reprompt.
• Set the static choices for the first value prompt.  The Use Value should equal what is displayed for the column headings. The display value does not need to be set.
• Set the static choices for the second value prompt.  The first value should be Use=ASC and Display=Ascending.  The second value should be Use=DESC and Display=Descending.
• Add a calculated member to the list report.  The expression should be as follows: 

case
when ?p_sortcolumn? = 'Product line' then (RANK([Product line] #prompt('p_asc_desc','token','ASC')#) )
when ?p_sortcolumn? = 'Product type' then (RANK([Product type] #prompt('p_asc_desc','token','ASC')#) )
when ?p_sortcolumn? = 'Product name' then (RANK([Product name] #prompt('p_asc_desc','token','ASC')#) )
when ?p_sortcolumn? = 'Quantity' then (RANK([Quantity] #prompt('p_asc_desc','token','ASC')#) )
when ?p_sortcolumn? = 'Revenue' then (RANK([Revenue] #prompt('p_asc_desc','token','ASC')#) )
else 0
end

• Highlight the calculated column and header and set the box type to none.
• Save and run the report. 
• You can select the column you want to sort on, from the first prompt and the order of sorting from the second prompt.

 Additional Information

Interested in Streamlining and Automating Your Cognos Administration Role?  Try MetaManager!

• Report Validator
• Global Name Changer
• Screen Tip Generator
• Find Replace
• Backup / Restore

And even more, the results are captured in an XML format, so you can use your Cognos environment to view the results.  Click here for an example.