IBM Support

Dynamic sorting of columns using Drill through technique

Troubleshooting


Problem

Dynamic sorting of columns using Drill through technique. In this technique there is no involvement of java script as well. With this technique you can only have the column sorted either ascending or descending but not both.

Environment

Windows, Unix

Resolving The Problem

The below steps have been implemented using Go Sales (Query) package by creating a List report.

1) Create a new List report.

2) Drag the below Columns on to List

- [Sales (query)].[Order].[Order number]

- [Sales (query)].[Order method].[Order method]

- [Sales (query)].[Product].[Product type]

- [Inventory (query)].[Product].[Product name]

3) Go to query explorer. Select Data items [Order number],[Order method],[Product type] and [Product name].

4) Right click -> Copy and paste them in the same Query. After you paste you will notice the following data items [Order number1],[Order method1],[Product type1] and [Product name1] being added to same query.

5) Open data item [Order number1] and place single quotes at the begin and the end of expression definition.

eg: '[Sales (query)].[Order].[Order number]'

6) Create a new Data item having name as 'sort' and definition as


#prompt('p_SortColumn','token','[Sales (query)].[Order].[Order number]')#

This is important step for the sorting to work.

6) Save the report so that we can setup drill through in below steps. eg: 'Sort column report'

7) Go back to Page explorer and select a column title for which you need to sort.

8) Define Drill through by clicking on 'Drill-through definition' in properties pane. For Report name select this report only. eg: 'Sort column report'

9) Click on 'Edit' for Parameters. For parameter 'p_SortColumn' set method as 'Pass data item value' and value to data item <column title[1]>. For example if you have selected Column title as [Order number] and you are defining drill through for the same then for parameter p_SortColumn for value we need to select [Order number1]. This is one of the important steps of this technique.

10) Define drill through for all the columns which we need to sort by repeating steps 7 - 9. However whatever columns you select you need to select it's appropriate data item value.

11) Being on report design page go to 'Advanced Sorting...'

12) Drag 'sort' data item under 'Detail sort list'. Whatever sorting type has been set ascending/descending it will sort only in that manner. In other words you can only sort either ascending or descending but not both.

13) Save and execute the report. On the upcoming prompt page Click 'OK' without entering any value. You will notice that the columns which you opted to sort on will have a hyperlink for the same. The moment you click on the link the column would get sorted.

Attached zip file contains the following :
a) Snapshot.doc - Snapshots of certain Steps mentioned above.
b) Report Spec_83.txt - Report specification of Cognos 8.3 on Go Sales (Query) package.
c) Report Spec_84.txt - Report specification of IBM Cognos 8.4 on Go Sales (Query) package.

Please note :- If the column contains blank or NULL values, and if set for sorting it would be displayed at the bottom of the column.

Important steps for report spec to work properly:
If you intend to execute the report specification attached with this technote you would need to fulfill the following requirements :
1) IBM Cognos Samples should have been installed.
2) Save the report specification only with the name 'Sample List Sort report' inside Go Sales (Query) folder before running the report. If you intend to save the report specification with different name you can do so however you would need to modify the drill through definition as per guidelines mentioned in Step(8) and Step(9) of the attached snapshot.doc file.
3) While you execute the report you would get a prompt page asking you to enter value for p_SortColumn, you can simply ignore the same and click on OK button.
4) Once the List report is displayed you can then click on the column Title to see the sorting taking place.

Attachment.zip

[{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Report Studio","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"8.4.1;8.4;8.3","Edition":"All Editions","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018

UID

swg21397882