Try it yourself - create a report with drill-through access to itself

You can create a report that drills through to itself so that users can view detailed information in the same report.

In this topic, you learn how to create a report that shows revenue by each retailer. Users can access detailed order information for a particular retailer.

It should take 20-25 minutes to complete this topic, and your report will look like this.

Figure 1. A report with drill-through access to itself
a report showing revenue for all retailers with drill-through links on each retailer name to view order details.

Procedure

  1. Create a new blank report that uses the GO Data Warehouse (query) package.
  2. Add a table with two columns and one row.
  3. Add a list object to each column in the table.
  4. Open Sales and Marketing (query), then open Sales (query). Add these data items to the first list:
    • Retailer name in Retailers
    • Revenue in Sales fact
    Tip: Use the Source tab source tab.
  5. Set the sort order for the Retailer name column to Sort Ascending.
  6. Add these data items to the second list:
    • Retailer name in Retailers
    • Order number in Sales order
    • Date in Time
    • Product in Products
    • Quantity in Sales fact
    • Revenue in Sales fact
    Tip: To simultaneously add all the data items to the list, Ctrl+click the items before dragging them to the list.
  7. In the second list, select the Retailer name list column and in the Properties pane, set its Render property to No.
  8. In the second list, create the following parameterized filter:

    [Retailer name]=?Selected retailer?

  9. Save the report.
  10. In the left list, right-click the Retailer name column (and not the column title) and click Drill-Through Definitions.
  11. Create a new drill-through definition.
  12. Specify the report as the target report.
  13. Under Parameters, click the edit button.
  14. Set the Selected retailer parameter to pass data item values using the Retailer name data item.
  15. In the Toolbox tab, add a value prompt under the table.

    Because you are setting up drill-through access to the same report, you must add a prompt with a default value so that users are not prompted when they run the report.

  16. In the Prompt Wizard, specify that the prompt is to use the existing parameter named Selected retailer, and click Finish.
  17. Click the value prompt.
  18. In the Properties pane, double-click the Default Selections property and add a simple selection named NoRetailer.
  19. Set the following properties:
    • Required to No
    • Hide Adornments to Yes
    • Visible to No
  20. Create a title for the report.
    1. In the Toolbox tab, add a block above the table.
    2. Click the block and, in the Properties pane, set the class to Report title area.
    3. Insert a text item above the table. Type the following text, adding a blank space at the end:

      Order Details:

    4. In the Toolbox tab, add the following layout calculation immediately following the text item: 
       if(ParamDisplayValue('Selected retailer')='NoRetailer')
 then 'All Retailers' else (ParamDisplayValue('Selected retailer'))

      When the report runs, the report title changes to reflect the retailer selected by the user. If no retailer is selected, 'All Retailers' appears.

    5. Click the text item and the layout calculation and, in the Properties pane, set the class to Report title text.
  21. Pause the pointer over the condition explorer button and click Variables.

    Use conditional formatting to show a message above the second list when users have not selected a retailer from the first list.

  22. Create a Boolean variable named HighLevel:

    ParamDisplayValue('Selected retailer')<>'NoRetailer'

  23. Pause the pointer over the page explorer button and click Page1.
  24. In the Toolbox tab, add a block above the second list.
  25. Insert a text item in the block with the following text:

    Select a retailer in the Retailer list to view order details

  26. Set the font style for the text to bold.
  27. Click the text item and, in the Properties pane, set the Style Variable property to the HighLevel variable.
  28. Pause the pointer over the condition explorer and click the Yes value for the HighLevel variable.
  29. Set the Visible property for the text item to No.
  30. Triple-click the explorer bar to turn off conditional formatting.
  31. Run the report to view what it will look like for your users.

Results

A list appears on the left that shows revenue for each retailer. When users click a retailer, order information for the selected retailer appears in the second list. To return to the original report, in IBM® Cognos® Viewer, click Previous Report.

For more information, see the following references:

Parent topic: Using Drill-through Access