Cognos 8 - One Click Dynamic Column Sorting

This technique allows you to dynamically sort a report by column by clicking on the column header.  It also enables you to change the sort order by repeating the click.

 

To create a list report that allows you to dynamically sort the data ascending or descending by pressing each column's header you will need: 

• A parameter that provides the column used for sorting;
• A parameter that provides the sort order (ascending/descending);
• A drill through on each column title that will be sorted that passes the full name of that column.  Also a pass sort order to change it each time a drill through occurs.

Steps:

 

1. Create a new or open an existing list report.
2. In Query Explorer go to Query1.
3. Click on the data item that you would like to sort by and press CTRL+C and CTRL+V to duplicate the data item.
4. Repeat Step 3 for each data item you would like to sort by.
5. Double-click on the copy of each data item to edit it, adding an apostrophe to the beginning and to the end of the expression so it looks like this:  '[Academic Study].[Academic Study].[ACADEMIC_PERIOD]' and press OK.
6. Repeat Step 5 for each duplicate data item.
7. From the Toolbox drag a Data Item to the Data Items Pane.  In the Expression Editor that pops-up type:  if(#prompt('p_Direction','integer','1')#=1) then (#prompt('p_SortColumn','token','[PROGRAM]')#) else ('0') (1 means ascending and -1 means descending.  We will have a data item that changes from 1 to -1 and vice-versa at each drill through.)  Click OK. (Column that we will sort on will be the value of this parameter.)
   
   
   
   
   
8. Rename the newly created data item to:  Sort_item_asc.
9. Create a copy of Sort_item_asc (using CTRL+C and CTRL+V).   Rename the copy to Sort_item_desc.
10. Double-click Sort_item_desc to edit it and change =1 to = -1.  Click OK.
11. In Page Explorer, click on Page 1 and then click anywhere in the list.  
12. From the Report Studio menu, click on Data and then Advanced Sorting…
13. In the Grouping & Sorting dialog box that pops up drag Sort_item_asc on top of Detail Sort List. 
14. Drag Sort_item_desc underneath Sort_item_asc in Detail Sort List folder and click on the Sort Order button to change it to descending.
    
    
    
    
    
15. Click OK.
16. From Query Explorer click Query 1 and add a Data Item from the Toolbox.  Type this in the expression editor:  -(#prompt('p_Direction','integer','1')#)  Click OK.  (This data item is the future value of the sort order on the next drill through.)
17. Rename Data Item1 to Direction.
18. In the Page Explorer click on Page1.
19. Click on the header column of the data item you want to sort by (the Properties pane should display List Column Title next to Ancestor Selector).
20. In the Properties Pane double click Drill Throughs.  Click New Drill Through.  For Report choose your report, for action choose Run the Report, click to edit parameters, choose Pass data item values as Method and for Value select the COPY of the data item you are sorting by for the p_SortColumn parameter and Choose Direction for the p_Direction parameter.
    
    
    
     
21. Click OK and then OK again.
22. Repeat steps 12 thru 14 for each data item column, but selecting the corresponding duplicate data item as the value for the drill through parameter.  (Depending on the column title where the drill through is initiated from – we will assign the according value for the parameter that gives us the column to sort.)
23. From Run->Run Options…menus deselect Prompt and click OK.
24. Save the report and run.  For each click on the column header the order changes from ascending to descending and vice-versa.

--
BI CENTRE
http://bicentre.blogspot.com