Business Analytics Business Intelligence Cognos Report Studio

Cognos Prompt Page with Cascading Prompts

Filters
Jason Clements
Written by Jason Clements

Cascading prompts are typically used to improve the user experience when dealing with related data items consumed by the prompt page. The cascade functionality limits the displayed prompt values based on the user’s selection within the parent prompt. This built-in functionality causes the entire prompt page to be re-submitted resulting in a possible re-run of the supporting data queries and additional time to re-render the prompt page. The approach in this document queries all the data at once and performs the prompt refresh without the requirement of re-running the queries or re-rendering the prompt page.

Steps to Implement Auto Cascading without Page Refresh

  1. Within IBM Cognos Report Studio, create a Query (we will refer this query as Prompt  query) containing the use and display values of all prompts. For this example the query will contain the following query items:
    • [Branch_Hierarchy].[Region_Name]
    • [Branch_Hierarchy].[Bank_Name]
    • [Branch_Hierarchy].[Branch_Name]
    • [Branch_Hierarchy].[Branch_Number] 5Below are the highlighted panes you will need to familiar with.
    • On the Left we have the Properties Pane.
    • In the Center we have the Query Explorer.
    • On the Right we have the Details Filters.11
  2. Create optional Filter(s) with in the new Query for Region_Name and Bank_Name. 8
  3. Within Report Studio, create a “Main Report Query” with your report data. Create a Filter(s) for each value. For this example the query filter on Region, Bank, and Branch. This will give the user the option to filter by only Region OR by Region and Bank OR all the way down the Region, Bank, and Branch.8
  4. Create a prompt page and insert one table with two columns and three rows by dragging them from the available Toolbox items onto the Prompt Page canvas.    4 **This whole implementation can be done quickly if you can copy and paste from existing report or use the built in Cognos Templates.
  5. From the available Toolbox items, insert three Value Prompt objects into the second column. As each of the Value Prompt objects are dragged onto the canvas, IBM Cognos Report Studio will display the Prompt-Wizard-Value Prompt dialog box. Within that dialog box specify the parameter name for each of the three parameters and click the Finish button.10 For this example the parameter names will be ?Region?, ?Bank? and ?Branch?. When completed the Prompt Page canvas should have two columns stacked next to each other, with three Value Prompt objects within each row.3
  6. Highlight the first Value Prompt object (Region) on the Prompt Page canvas and within the bottom left Properties pane set the following properties:
  • Data > Query = Query1
  • Data > Use Value = Region
  • Data > Display Value = Region
  • General > Auto-Submit = Yes
  • General > Cascade Source = Leave Blank

12

6a. Highlight the second Value Prompt object (Bank) on the Prompt Page canvas and within the    bottom left Properties pane set the following properties:

  • Data > Query = Query1
  • Data > Use Value = Bank_Name
  • Data > Display Value = Bank_Name
  • General > Auto-Submit = Yes
  • General > Cascade Source =Region_Name

6b. Highlight the third Value Prompt object (Branch) on the Prompt Page canvas and within the    bottom left Properties pane set the following properties:

  • Data > Query = Query1
  • Data > Use Value = Branch_Name
  • Data > Display Value = Branch_Name
  • General > Auto-Submit = No
  • General > Cascade Source =Bank_Name

7. From the available Run menu, select the Run Report –HTML to run the report in the IBM Cognos Report Viewer.

13

About the author

Jason Clements

Jason Clements

Who is Jason Clements? Good question. He is a husband, father, BI consultant, and database administrator, He has been working with SQL Server for a decade, working primarily in business intelligence, ETL/SSIS, data quality, and reporting. Jason is employed with BlueNET Technologies as Senior Business Analytics Consultant.

As an active member of the community, Jason has spoken at regional and local venues including the vendor based trade shows and various user groups & webcasts. He is an active group member and speaker at the North Georgia SQL Server User Group in the ATL area.

62 Comments

Leave a Comment