IBM Cognos Proven Practices
Dynamic Sort on a Numerical Column in IBM Cognos 8
Nature of Document: Tip or Technique; Product(s): IBM Cognos 8; Area of Interest: Reporting
This content is part # of # in the series: IBM Cognos Proven Practices
This content is part of the series:IBM Cognos Proven Practices
Stay tuned for additional content in this series.
This document describes an approach that can be used to dynamically sort on a Numerical Column.
This document was tested in IBM Cognos 8.4.
Creating the Report
Creating a simple List report using ‘GO Sales (query)’ package
- Open Report Studio using GO Sales (query) Package.
- Select 'Create a new Report or Template'.
- Select 'List' and Click OK.
- Drag Staff name and Revenue from the package into List Report.
Creating Value Prompt
- In the Insertable object pane, click on Toolbox Tab.
- Drag a value prompt to the report page.
- Name the parameter 'Sort' and click ‘Finish’.
- Highlight the added prompt and add two static choices through the properties pane of the value prompt. For the first static choice, display value will be ascending and set the use value to 1. For the second static choice, set the display value to descending, and the use value to -1.
- Add a default selection in order to avoid being prompted on report load. This can be done through the properties of the value prompt. Put value 1 in the default selection for ascending.
- In the properties of the value prompt, set Auto-Submit property to Yes.
Creating Data Item
- Hover over Query Explorer and click on Query1. In the Insertable Object, click on the toolbox tab and add a Data Item to the query.
- Click on the Source tab and drag and drop Revenue from the Insertable object pane to the expression definition. Then modify the expression to look like the following:
- In the Data Item properties, name the data item sortkey and set the Pre-Sort property to 'Sort ascending'.
- Return to Page1. Select the in the properties window List.
- In the List properties select Properties, have the sortkey checked.
- Click the lock button on the toolbar to unlock the layout. The button should change to look like the image below.
- Drag value prompt in the column header near revenue.
- Select Revenue and delete it. Drag a 'Block' from toolbox above the value prompt and drag and drop 'Text Item' into the 'Block'. Type Revenue in the 'Text Item'. Again, click on lock button to lock the report layout.
- Run the report. Revenue is in Ascending order.
- Select Descending from the drop down. Revenue in Descending order.