A drill-through definition in Cognos Connection is associated with a source package. You can define drill-through definitions between the reports created in the different Cognos 8 studios, and the reports based on different packages and data sources. The drill-through path defined in the drill-through definition is available to any report based on the source package.
A drill-through definition contains:
a target report
The target report is based on any target package in Cognos Connection and can be stored anywhere.
scope
Specifies when the target report is shown to the user. For example, when the scope is set to "Product Line", the target report is shown as an available choice in the Go To page only when data from "Product Line" is selected in the source report.
action
Specifies the run options for the target report.
The View most recent report option should be used with reports that contain bookmark references.
format
Specifies the format, such as PDF or HTML, in which users view the target report.
parameter mappings
When the target report is referenced in the drill-through definition, all parameters in it are visible. To ensure successful drill-through, the parameters should be mapped to the source package metadata.
bookmark references
Use this property when the Action property in a drill-through definition is set to View the most recent report, and the report contains bookmark references. This option helps the users to find the data they need without running a large report.
For example, you have a large report scheduled to run on a daily or weekly basis during off hours because of resource considerations. Your users may want to view this report as a target because it contains detailed information on a particular topic, such as the inventory, but you want them to view the saved output rather than run this large report. Using this option and bookmark settings, the user can drill through from another source location based on products to open the saved report to the page showing the product they want to focus on.
display prompt pages
Controls when prompts are displayed during the drill-through activity. For example, you may want the prompts to always appear, regardless of whether they are fulfilled or not. Alternatively, you may never want the prompts to appear if they are unfulfilled.
You can specify drill-through paths for packages using Drill-through Definitions in Cognos Connection. Any source report created in the package can drill through to targets defined in any other package if the users select information in their source report that is within the scope of the drill-through path. For example, all reports authored in the Great Outdoors Company sample package can access any drill-through definition created in this package.
You can create a new drill-through definition , or
edit an existing one
. Before you create a drill-through definition,
you must understand some important concepts about drill-through
access
.
The target report must be created before you start creating the drill-through definition in Cognos Connection. Drill-through targets can be reports, analyses, report views, and queries. If you want the target report to be filtered with the values passed from the source, ensure that the target report contains parameters.
Drill-through definitions support both dimensional and relational packages, and are available to Analysis Studio, Query Studio, and Cognos Viewer.
Open the target report
Create a parameter that will serve as the drill-through column, or that will be used to filter the report.
For example, to drill through or filter on Product line, create a parameter that looks like this:
[Product line]=?prodline_p?
Tip: Use the operators in or in_range if you want the target report to accept multiple values, or a range of values.
In the Usage box, specify the action to take when the target parameter is not fulfilled as part of a drill-through action.
Click Required to specify that users must click a value in the source report if the parameter is required, but not fulfilled.
Click Optional to specify that users do not need to click a value in the source report if the parameter is not fulfilled.
Click Disabled to specify that the parameter is not used by the drill-through action.
Note: The drill-through definition controls when prompt pages or parameters are displayed.
In Cognos Connection, in the upper-right corner, click Launch, Drill-through Definitions.
Click a package name to create a new drill-through definition for that package.
This package is considered the source package and the drill-through definition you are creating will be available by default to any report in this package.
Click the new drill-through definition button on the toolbar.
Type a name for the drill-through and, if you want, a description and screen tip. Click Next.
Set the scope and target, and click Next.
The scope specifies whether the target report in the drill-through definition is available to the user in the Related Links page. If you want to make the target report available regardless of what is included in the report, in the Set the scope page, click Entire package, which is the default setting for scope. For more information, see Scope
The target report can be based on any package in Cognos Connection, but when drill-through definitions cross packages, you must consider the data types used in both the source and the target, and ensure that the created parameters are appropriate for your scenario. For more information, see Conformed Dimensions and Business Keys.
The target report can be located anywhere in Cognos Connection. The user must have execute permissions for the report to use it in a drill-through.
Specify the target details.
In the Action section, specify the action for a drill-through link.
The default action is to run the target report, analysis, or query.
If you change the action to View most recent report, you do not need to map the target parameters to the source package metadata. The Display prompt pages property will be disabled because you will be using a previously run report. Running the report requires that the target report parameters are mapped to the source package metadata.
If you change the default action to open with Analysis Studio or Query Studio, you must also map the target report parameters to the source package metadata.
In the Prompt Values section, specify parameter values.
Every parameter that exists in the target report should be mapped to the appropriate source package metadata in order for the drill-through to successfully filter the target report with data from the source report.
If you do not map target parameters to the source package metadata, the source report does not pass anything to the target parameter. If your target report is not receiving any parameters, check the mapping in your drill-through definition, and ensure that your parameters were created against the correct data type for your drill-through scenario. For example, if you want to create a drill-through definition from an OLAP package to a target report based on a relational package, your target parameters need to be set up to a query item that has the same value as the OLAP business key.
For more information, see Members and Values.
In the Display prompt pages section, specify when the prompt pages will appear.
Only when required parameter values are missing
The prompt pages are not displayed when the required parameter values are provided by the drill-through definition. This is the default.
Always
The prompt pages are always displayed when the drill-through action occurs, regardless of whether values were specified for the required parameter values.
Based on the default prompt settings of the target report
The prompt pages are displayed or not displayed depending on the prompt settings of the target report specified by the Prompt for Values check box in Cognos Connection - Report Properties - Report tab.
Click Bookmark References to specify a bookmark reference to pass to the target report.
Tip: Use this option only when the target report is a previously run PDF report that has bookmarks.
Click Finish.
In Cognos Connection, in the upper-right corner, click Launch, Drill-through Definitions.
Click a package name to view its drill-through definitions.
Click the set properties button on the actions toolbar to the right of
the drill-through definition that you want to modify.
Click the Target tab.
Make the necessary modifications, and click OK.
You can create a drill-through target analysis and add target parameters in the analysis by setting a dimension or a level as the Go To parameter. Set the dimension as the Go To parameter for drill-down and drill-through within the dimension. This will allow you to perform a drill-down or drill-through action, but only the level, which is currently in your view, is passed. This applies to any query, report, or analysis used in Cognos 8 drill-through action. A source report can then pass the current selection of values to the target analysis.
You can set multiple parameters in an analysis target. However, you cannot pass members within a selection set in Analysis Studio.
In Analysis Studio, create a cross-tab analysis using the package that was set up for drill-through analysis.
If you want, add as a row or column the data item that you want to be the prompt.
Move or add the dimension or level that you want to be a target parameter to the Context area.
Note: You cannot pass members within a selection set in Analysis Studio.
View the list for the item in the Context area and click Use as "Go To" Parameter.
Save this analysis as your target report in Cognos Connection.
You can now create the drill-through definition under a source package.
When you create the drill-through definition and
use the cross-tab analysis as a target, the Go To parameter
item in the analysis appears as a drill-through parameter. You can
map to this parameter the same way that you drill through to Report
Studio or Query Studio reports.
Cognos 8 includes a debugging functionality that you can use to find problems with your drill-through definitions created in Cognos Connection, and to correct any drill-through errors. It can also help you understand how the drill-through functionality works, especially across different types of data sources. This functionality is also referred to as the Drill-through Assistant.
The Drill-through Assistant provides the following information:
Shows the values selected by the user when invoking the drill-through action that are available for passing to the target report. For example, if you drill through from a source in Analysis Studio, you see the values at the intersection you selected prior to the drill-through action, as well as any values in the context area. The values in the debug list are the values in the source report that were not transformed by any drill-through operation.
Display Value
Shows the value that users see when using this data item or this member. For OLAP members, this is the member caption or label. For example: Telephone is a member from the Order Method dimension.
Use Value
Shows the value that Cognos reports and analyses use when retrieving the data item or the member. For OLAP members, this is the member unique name (MUN). For example: [great_outdoors_company].[Order Method].[Order Method].[Order Method1]->:[PC].[@MEMBER].[2] is the MUN for the Telephone member in the Order Method dimension.
Shows the name of the parameter that was mapped in the drill-through definition, and the values that the source is attempting to pass to that parameter.
Parameter Name
Shows a list of valid target parameters mapped in the drill-through definition to receive information from the query item, level, or hierarchy on which you performed the drill-through action.
You can see only parameters for which there is a valid mapping and only the names of the parameters. For example, if the target report contains a parameter for Product Type and the drill-through definition maps that target parameter to the source Product Type level metadata, you see this target parameter only if you attempt to drill through on the Product Type level in the source report. Drilling through on the Product Line level does not display this parameter target.
You must ensure that the target parameters in your drill-through definitions are mapped correctly. Incorrectly mapped parameters can receive information from the wrong source metadata, especially where you have data values that are not unique. If you cannot see any target parameters or the parameters you expected to see in the View Target Mapping list, check the parameter mapping in the drill-through definition.
Display Value
Shows the value that users see when using a data item or member. For OLAP members, this is the member caption or label. For example: Telephone is a member from the Order Method dimension
Use Value
Shows the transformed value that the drill-through definition uses when passing a data item value or member to the target parameter.
OLAP members passed to relational target parameters obtain the business key from the members MUN and pass only the business key. Using the above example of the Telephone member in Order Methods, the business key is 2. If you are unsure of what the business key is for a member, you can write a Report Studio expression such as roleValue('_businessKey',[member]). This value is passed to the target parameter.
OLAP members passed to a target parameter based on another OLAP package of the same OLAP type show a transformed MUN. Using the above Order Methods example, the MUN is now transformed and the drill-through definition uses the value of [great_outdoors_company].[Order Method].[Order Method].[Order Method1]-> [Order Method1]. [2]:[PC].[@MEMBER].[2]. The middle portion of [Order Method1][2] is where the drill-through definition finds the correct member in the target when the OLAP data sources are different. To see the MUN for a specific member, you can look at the properties of the member in Report Studio and look at the Member Unique Name property.
You can use the Drill-through Assistant for debugging purposes when you work with drill-through definitions in Cognos Connection.
To use the this functionality, you must have the
required permissions for the Drill Through Assistant secured
function in Cognos Administration.
Select a link in your source report, right-click the link, and select Go To, Related Links.
The Related links page is displayed with the list of available target reports. If your target report is not shown, review the scope settings in your drill-through definition.
Click View passed source values to see the values that are available for passing by the source report.
Next to the target report, click the down arrow and choose one of the following options:
Runs the target report.
Shows the valid mapped parameters for the available source values, and the use and display values that the drill-through definition will receive.
Your OLAP source has a Products dimension with the levels Line, Type, and Name. You have defined a parameter in your relational target to match each level of that OLAP source dimension. You can have a situation where you see all target parameters from a single dimension displayed in the View Mapped Target list. This is likely because the individual target parameters are mapped to a single dimension in the drill-through definition, in this case the Products dimension. In your OLAP data source, you have a business key value, or the source value used to create the members, that is duplicated in all three levels.
Parameter Name | Display Value | Use Value |
Prod Line Param | Camping Equipment | 1 |
Product Type Param | Cooking Gear | 1 |
Product Name Param | Trail Chef Water Bag | 1 |
Having all three parameters mapped to the Products dimension is correct if the use values are not duplicated in the dimension. In the above table, you can see that members from all three levels have the same use value. In this case the drill-through operation cannot determine which level is the correct one because the scenario indicates that all levels are valid. In this situation, the first level encountered with a valid business key or use value is fulfilled by the drill-through definition. This can result in unexpected behavior.
This example shows why it is important to always ensure that your data warehouses and OLAP sources are designed with unique business keys or source values. To correct this situation, the drill-through definition should have each individual target parameter mapped to each associated level in the source metadata rather than in the dimension.