Example - Drill Through Between OLAP and Relational Packages

You want to drill through from an IBM® Cognos® PowerPlay® Studio report named Profit Margin and Revenue by Country or Region to a Report Studio report named Total Revenue by Country or Region.

You set the drill-through definition up in the package, so that the revenue breakdown is available to any report in the same package.

The profit margin report is based on the package Sales and Marketing (cube) and the target report is based on the relational package GO Data Warehouse (query). Therefore you need to check that the data is conformed. The target report does not contain any prompt parameters, so you will define a drill-through definition using dynamic drill through. This means that when the drill-through link is made, IBM Cognos Business Intelligence matches names of items in the context of the source to available items in the target. For more information and examples, see the IBM Cognos Business Intelligence Administration and Security Guide.

You must have the IBM Cognos BI samples from the deployment zip file IBM_Cognos_DrillThroughSamples installed to follow this exercise. To check the target report, you should have access to Report Studio.

Note: You can set up drill-through definitions without checking the target reports. However, if you set up drill-through access between packages or between objects created in different authoring tools, you should be aware of how the metadata will be matched.

The following figure shows the target report with data for the context of the source, which is the Promotion Plan Revenues for various promotions.

Figure 1. Source and target reports for a drill-through definition
Screen capture showing the target report with data for the context of the source.

Procedure

  1. Open the target report, Total Revenue by Country or Region:
    • Go to IBM Cognos Connection.
    • From Public Folders, navigate to the package GO Data Warehouse (query), and then open the folder Report Studio Report Samples.
    • Select the report Total Revenue by Country or Region, and open it in Report Studio.
  2. Confirm the names of the data items that will be used for filtering context from the source:
    • In the Page Explorer, select the report item.
    • In the Properties pane, check the Data Item property Name.
    • Note the items named Region, Retailer country or region, and Product line.
  3. Close the target report.
  4. Open the source report, Profit Margin and Revenue by Country or Region:
    • Go to IBM Cognos Connection.
    • From Public Folders, open the package Sales and Marketing (cube), and then open the folder PowerPlay Studio Report Samples.
    • Select the Profit Margin and Revenue by Country or Region report, and open it in PowerPlay Studio.
  5. Review the data in the report. (For example, right-click a retailer name and select Explain to look at the structure of the data.)
  6. Keep the Profit Margin and Revenue by Country or Region report open for testing.
  7. In IBM Cognos Connection, navigate to the Sales and Marketing (cube) package.
  8. Click Launch, Drill-through Definitions.
  9. Click New Drill-through Definition.

    Tip: If you do not see the New Drill-through Definition button, check that you are at the root of the folder, and not still in the PowerPlay Studio Report Samples folder.

  10. In the Drill-through Definition wizard, type a name, such as Drill Through to Total Revenue by Country or Region, and a description, and click Next.

    Tip: This is the name that users see in the Go To page, for example when they have to select from more than one drill-through target. As with any other object in IBM Cognos Connection, you can create translations of this name.

  11. Click Set the target, Select a report... and in the screen that appears, set the target report to Total Revenue by Country or Region, in the Report Studio Report Samples folder of the GO Data Warehouse (query) package, and then click OK.
  12. Click Set the scope, and in the screen that appears, set the scope to [sales_and_marketing].[Retailers].[Retailers].[Retailer country or region], and then click OK.
  13. Click Next.

    In the Action field, select Run with dynamic filter.

  14. Leave all other settings at the default values and click Finish.
  15. Go to the Profit Margin and Revenue by Country or Region report, and click the drill-through icon.
    • If the drill-through definition you created is the only drill-through target available, the target reports runs.
    • If more than one drill-through target is available, a list of possible targets for the package and the data that is in scope appears. Click the drill-through definition that you created, and the target runs using the context you selected.

Results

Report users in PowerPlay Studio can drill through from the Profit Margin and Revenue by Country or Region report to the target report that you have defined (Total Revenue by Country or Region for Product Line). Also, the target report is available as a drill-through target for any existing or new report based on the Sales and Marketing (cube) package, whenever Retailer Country or Region is part of the scope.

The drill-through definition that you create should be identical to the sample drill-through definition Dynamicdrill in the Sales and Marketing (cube) package.

Parent topic: Drill-through Access