Dynamic Sorting Based on Selection in Cognos Report Studio

Dynamic Sorting Based on Selection in Cognos Report Studio

Requirement: User should be given a choice to select the columns on which the output should be sorted. Ex: A List has Month and Quantity.  If the user selects Quantity, output should be sorted based on Quantity in ascending order.

Solution: Create a ‘Sort’ data item in the same query subject and perform ‘Advanced Sorting’ based on the ‘Sort’ data item.

Detailed Steps:

1.     Create a List report with Month and Quantity columns

2.     Create a ‘sort’ prompt with static choices as 1-Month and 2-Quantity

3.     Create a Data Item ‘Sort’ in the query subject as
     case
when ?sort? = 1 then [Month]
when ?sort? = 2 then [Quantity]
end

4.      Select 'Month' column in Report Page, select ‘Advanced Sorting’ and  add ‘Sort’ under ‘Detail Sort List’

                 5.  Repeat the above step for “Quantity” also.

Limitations: The above solution sorts both columns in the same order. i.e either by Asc or by Desc. This works only when both columns are of same data type