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 document describes an approach that can be used to dynamically sort on a Numerical Column.

Share:

Sonal Bhatt, Software Engineer, IBM

Sonal Bhatt is a part of IBM Cognos BI Fix Pack Team of India Software Labs based in Pune, India. She has completed Bachelor of Engineering in Computer Science. Sonal is working on feature and functional testing of different Cognos BI products having 4+ years of experience.



29 June 2010

Also available in Chinese Spanish

Introduction

Purpose

This document describes an approach that can be used to dynamically sort on a Numerical Column.

Applicability

This document was tested in IBM Cognos 8.4.


Creating the Report

Creating a simple List report using ‘GO Sales (query)’ package

  1. Open Report Studio using GO Sales (query) Package.
  2. Select 'Create a new Report or Template'.
  3. Select 'List' and Click OK.
    New window in Report Studio with List highlighted
  4. Drag Staff name and Revenue from the package into List Report.
    Report Studio with staff name and Revenue shown in a list report

Creating Value Prompt

  1. In the Insertable object pane, click on Toolbox Tab.
  2. Drag a value prompt to the report page.
  3. Name the parameter 'Sort' and click ‘Finish’.
    Chooose Parameter window of the Prompt Wizard
  4. 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.
    Static Choice window
  5. 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.
  6. In the properties of the value prompt, set Auto-Submit property to Yes.

Creating Data Item

  1. 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.
  2. 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:
    [Sales (query)].[Sales].[Revenue]*?Sort?

    Click OK.
    Data Expression window
  3. In the Data Item properties, name the data item sortkey and set the Pre-Sort property to 'Sort ascending'.
    Properties pane showing revenue properties
  4. Return to Page1. Select the in the properties window List.
  5. In the List properties select Properties, have the sortkey checked.
    Properties box
  6. Click the lock button on the toolbar to unlock the layout. The button should change to look like the image below.
    Unlocked layout button
  7. Drag value prompt in the column header near revenue.
    Revenue prompt with the prompt title to the left of prompt
  8. 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.
    Revenue prompt with the prompt title above the prompt
  9. Run the report. Revenue is in Ascending order.
    List report with revenues Ascending
  10. Select Descending from the drop down. Revenue in Descending order.
    List report with revenues Descending

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Business analytics, Information Management
ArticleID=498390
ArticleTitle=IBM Cognos Proven Practices: Dynamic Sort on a Numerical Column in IBM Cognos 8
publish-date=06292010