Drill-through
Access
Using drill-through access, you can move from one report
to another within a session while maintaining your focus on the
same piece of data. For example, you select a product in a sales report
and move to an inventory report about that product.
Drill-through access helps you to build business intelligence
applications that are bigger than a single report. Drill-through
applications are a network of linked reports that users can navigate, retaining
their context and focus, to explore and analyze information.
Drill-through access works by defining the target report using
prompt parameters, and then using information from the source report
to provide answers to those prompts. An important part of defining
a drill-through path is defining which values to use for each prompt.
If a prompt is answered by the drill-through value passed to it,
the prompt page does not appear.
In Cognos 8, you can drill through
between reports created in different packages
against different data source types
For example, you can drill
through from an analysis against a cube to a detailed report against
a relational data source.
from one existing report to another report using Report Studio 
between reports in Report Studio, Query Studio, or Analysis
Studio, exploring data in many ways
For more information, see
the user guide for the studio you are working in.
from Cognos Series 7 to Cognos 8
from Metric Studio to other Cognos 8 reports by passing
parameters using URLs
For more information, see the Metric Studio User Guide.
Understanding
Drill-through Concepts
Before you set up drill-through access, you must understand the
key concepts about drilling through. Knowing these concepts will
help you to avoid errors so that report consumers drill through
as efficiently as possible.
The target of drill-through access is always a saved
report definition. The report can be created in Report Studio, Query
Studio, or Analysis Studio.
Drill-through Paths
You can create a drill-through path in a source report
in Report Studio, or using Drill-through Definitions in
Cognos Connection. A drill-through path is the definition of the
path that is taken when moving from one report to another, including
how the data values are passed between the reports.
Using Drill-through Definitions, you can
create a drill-through path from any report in the source package
to any target report in any other package in Cognos Connection.
This type of drill-through definition is stored in the source package
and can be used when you want to drill between any combination of
Analysis Studio, Query Studio, or Cognos Viewer reports in any package.
For any target report that contains parameters, the target parameters
must be mapped to the correct metadata in the drill-through path.
This ensures that the values from the source report are passed to
the correct parameter values, and that the target report is filtered
correctly.
A report-based drill-through path refers to a path created and
stored in a Report Studio source report. The path is associated
with a specific data column, chart, or cross tab, and is available only
when users select that area of the report. The drill-through definition,
if it exists, appears as a hyperlink in the source report when it
is run.
The report-based drill-through is limited to Report Studio source
reports and any target reports. This type of drill-through can be
used when you want to pass the data item values or parameter results
within a source report to the target report, the results of a report
expression to a target report, or a URL link as a part of the drill-through
definition.
Drilling Through to Different
Report Formats
The settings in the drill-through definition determine
how users see the report results. For example, the users may see
the reports in Cognos Viewer as an HTML Web page, or the reports may
open in Query Studio or Analysis Studio.
Reports can be output as HTML Web pages, as PDF, XML, CSV, or
Excel formats. When you define a drill-through path, you can choose
the output format. This can be useful if the expected use of the
target report is something other than online viewing. If the report
will be printed, output it as PDF; if it will be exported to Excel
for further processing, output it as Excel or CSV, and so on.
If you define a drill-through path to a report that is created
in Analysis Studio or Query Studio, the report can be run and opened
in its studio instead of in Cognos Viewer. This can be useful if you
expect a consumer to use the drill-through target report as the
start of an analysis or query session to find more information.
An example of a this type of drill-through action can be an application
where a dashboard style report of high-level data can be drilled
through to Analysis Studio to investigate items of interest. The
Analysis Studio view can then be drilled through to a PDF report
for printing.
Note: Drilling through to Report Studio is not supported
for the Express Authoring Mode. The Report Studio Professional Mode
does not display data results.
Bookmark
References
When you drill through, the values that you pass are
usually, but not always, used to filter the report using parameters.
Cognos 8 Business Intelligence supports bookmarks within
saved PDF reports so that a user can scroll a report to view the
relevant part based on a URL parameter. For example, you may define
a report with one page per product and add a bookmark of the product number
on each page. Report consumers can then select a bookmark to see
the product that they want.
When a bookmark in the source report is used in a drill-through
definition, it provides the value for the URL parameter. When report
consumers drill through using this definition, they see the relevant
section of the target report.
Bookmark references are limited to previously run reports that
are output as PDF and contain bookmark objects.
Members and Values
Dimensionally modeled data, whether stored in cubes
or stored as Dimensionally Modeled Relational (DMR) data, organizes
data into dimensions. These dimensions contain hierarchies. The
hierarchies contain levels. And the levels contain members.
An example of a dimension is Locations. A Locations dimension
may contain two hierarchies: Locations by Organization Structure
and Locations by Geography. Either of these hierarchies may contain
levels like Country and City.
Members are the instances in a level. For example, New York and
London are members in the City level. A member may have multiple
properties, such as Population, Latitude, and Longitude. Internally,
a member is identified by a Member Unique Name (MUN) . The method by which a
MUN is derived depends on the cube vendor.
Relational data models are made up of data subjects, such as
Employees, which are made up of data items, such as Name or Extension.
These data items have values, such as Peter Smith.
In Cognos 8, the methods of drilling through available
are:
Dimensional (member) to Dimensional (member)
Dimensional (member) to Relational (data item value)
Relational (data item value) to Relational (data item value)
If the target parameter is a member, the source must be a member
and must also be from a conformed dimension .
If the target parameter is a value, the source can be either
a value or a member. If the source is a dimensional member, you
must ensure that the level or dimension is mapped to the target data
item correctly in the drill-through definition. The business key
from which the member is sourced must match the relational target
value, which is most often the business key .
Member Unique Names
The member unique name (MUN) is a unique identifier
for a member in Cognos reports. It is stored in the report specification
when the member is referenced in the report directly. The MUN is
used in drill-through between OLAP data sources. The member keys
in the MUN for the different OLAP data sources must match.
The MUN is used to find the member in the data source, which
is similar to how business keys are used to find records in a table.
For example, when you create OLAP dimension Products, you use the
Product Line database column as s label for the members in your
Product Line level. However, you use the Product Line Code business
key from the database table to ensure that all the Product lines
are unique in that level. The source value that you used to create
the members is used in combination with the data source name, hierarchy,
and level information in the member unique name.
If the MUN changes, members that are directly referenced in expressions,
filters, or reports are no longer found. Changes to the MUN may
be related to other changes. For example, changes to the hierarchy
and level structures may change the level unique name, and changes
to the business key values may change the member key path. Other
factors that can affect the MUN are application changes during the
design stage or over time, Cognos PowerCube category codes that
are unpredictably unique, the production environment that has more
members than the test environment, or removing the member from the
data source.
To avoid potential problems, we recommend the following best
practices when you build OLAP data sources:
Use unique codes and keys within a dimension
for the member keys.
Define your OLAP and relational packages using unique conformed
values for the source values (business keys) within similar dimensions
or data values where drill-through between applications may be required.
Ensure that the business keys and dimension metadata structure
are the same in the production and test environments.
Do not change the business keys in Framework Manager in the
production environment.
Resolve the non-unique keys in a dimension in the data source
before you build the cube.
Ensure that there are no duplicate
source values in all levels of a dimension before you build a PowerCube.
We do not recommend using the tilde character (~) in the category codes.
For
more information, see the section about uniqueness in the Cognos
Series 7 Step-by-Step Transformer.
Conformed Dimensions
If you work with more than one dimensional data source,
you may notice that some dimensions are structured the same, and
some are not. The reason that dimensions can be structured differently
is that the data sources may serve different purposes.
For example, a Customer dimension appears in a Revenue data store,
but not in an Inventory data store. However, the Products dimension
and the Time dimension appear in both data stores.
Dimensions that appear in multiple data stores are conformed
if their structure is identical for all of the following:
hierarchy names
level names
level order
internal keys
Drilling through is possible between different dimensional data
stores only if the dimensions are conformed, and if the dimension
data store is of the same vendor type, such as Cognos PowerCube
as the source and the target. In the previously mentioned example
of the Revenue and Inventory data stores, it is possible to define
the Products and Time dimensions differently for each data store.
However, for drill-through between the Products and Time dimensions
to work, their structures must be identical in each data store.
Business Keys
When performing a drill-through from a member to a relational
value, the business key of the member is passed. This means that
your relational target parameter must be set up using the data item
with a matching value, which is most often the business key data
item.
For example, employees are usually uniquely identified by an
employee number, not by their name, because their name is not necessarily
unique. When you drill through from a dimensional member to a relational
data item, the value provided is the business key. Therefore, the parameter
in the target report must be defined to accept a business key value.
The exact logic used to define the business key value supplied depends
on the cube vendor. For Cognos PowerCubes, the business key value
is the Source property defined for the level
in Cognos Transformer. Cognos Series 7 Transformer PowerCubes
pass the source value if the drill-through flag was enabled before
the cube was built. Otherwise, the category code is used.
In Report Studio, you can determine what the member business
key is using an expression such as roleValue('_businessKey',[Camping
Equipment]). This expression is case sensitive.
MSAS 2005 multi-part business keys are not supported in drill-through
operations.
Tip: When other users run your drill-through report, you
may not want them to be prompted for a business key. In Report Studio,
you can build a prompt page with a text that is familiar to the
users, but filters on the business key. Your Framework Manager modeler
can also set the Display Item Reference option
for the Prompt Info property to use the business
key when the data item is used in a prompt.
Drilling
Through on Dates Between PowerCubes and Relational Packages
Usually, drilling through from OLAP to relational packages
requires that the target report parameter is set using the business
key in the relational data. However, this method does not work well
for dates. OLAP data sources typically view dates as members, such
as Quarter 1 2006, while relational data sources view dates as ranges,
such as 1/Jan/2006 to 31/March/2006.
A special feature exists for drilling through between PowerCubes
and relational packages. Ensure that the target report parameter
is set up using in_range. Here is an example:
[gosales_goretailers].[Orders].[Order date] in_range
?Date?
Also ensure that the drill-through definition maps the parameter
at the dimension level and that the PowerCube date level is not
set to suppress blank categories. Enabling the option to suppress
blank categories in the Transformer model before you build the cube
may cause the drill-through on dates to be unsuccessful. This happens
because there are missing values in the range.
Drilling Through Between Packages
You can set up drill-through access between different
packages. The two packages can be based on different types of data
source, but there are some limits.
The following table shows the data source mappings that support
drill-through access.
|
| OLAP | OLAP Note: OLAP to OLAP drill
through is supported only if the data source type is the same, for
example, MSAS to MSAS. |
| OLAP | Dimensionally modeled relational |
| OLAP | Relational data Note: For more
information, see Business Keys. |
| Dimensionally modeled relational | Dimensionally modeled relational |
| Dimensionally modeled relational | Relational |
| Relational | Relational |
Scope
Scope is specific to drill-through definitions created
using Drill-through Definitions in Cognos Connection.
It defines when the target report is shown to the users, based on
the items they have in the source report.
Usually, you define the scope of a drill-through path to match
a parameter that it passes. For example, for a target report that
contains a list of employees, typically you only want to display the
report as an available drill through-choice when a user is viewing
employee names in a source report. If employee names are not in
the source report and the scope was set on the employee name in
the drill-through definition, the employee report is suppressed
from the list of available drill-through target reports in the Go
To page.
In report-based drill-through access, where the drill-through
path is associated with a specific report column, the column serves
as the scope.
Set Up Drill-through Access
in a Report
Set up drill-through access in a source report to link
two reports containing related information. You can then access
related or more detailed information in one report (the target) by
selecting one or more data item values from another report (the
source). In addition, you can pass parameter values from the source
report to the target report. This means that you can filter the
target report using the same prompted filter values in the source
report.
You can also drill through within the same report by
creating bookmarks , and create drill-through definitions in the
package. Users can use package drill-through definitions to navigate
to a target report from an Analysis Studio analysis, a Query Studio
report, or a Report Studio report. For more information, see the Administration and Security Guide.
If you have the Cognos 8 software development kit (SDK) ,
you can use URLs to set up drill-through access to and from third-party
sources.
You can also drill through from a map .
If you are using an SAP BW data
source for the target report, and if the target report contains
a variable for a hierarchy node, values from the source report can
be values only of the data item representing the leaf-level identifier
of the hierarchy.
Before you begin, ensure that you have a report that
will serve as the source report and another report that will serve
as the target report.
Steps
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 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 what to
do when the target parameter is not fulfilled as part of a drill-through:
To specify that users must click a value
in the source report, click Required.
If
the parameter is not fulfilled and it is required, users are prompted
to choose a value.
Note: Drill-through definitions
that have been authored inside a Report Studio report appear to
the consumer as a blue hyperlink. Clicking the hyperlink is how
they drill through to the target report and select the value they
want passed to the target.
A consumer can choose to select
multiple items within a single column and when the drill-through
target report is run, the values from each row selected will be
passed to the target parameters. This occurs as an and condition.
To specify that users do not need to click a value in the
source report, click Optional.
Users
are not prompted to choose a value if the parameter is not fulfilled.
The value will be unfiltered.
To specify not to use the parameter, click Disabled.
The
parameter is not used during the drill-through.
To create other parameters, repeat steps 2 to 3.
Open the source report.
Click the element in the report that will serve as the
drill-through object.
You can select a data item that your report users are likely
to choose to drill on for more detailed information, such as an
Employee Name data item. When defining the drill-through path in
Report Studio, you can choose to pass a value from a different data item
hidden from the user for display purposes, but still in the query.
For example, your users see the Product Name data item and can drill
through on that item but the drill-through definition passes the
Product Number value for that particular product name that the user
has chosen.
Tip: If you are passing only parameter values to the
target report, you do not have to drill on a data item. Instead,
you can drill on any object in the report, such as the report title. This
means that you can drill from outside the context of a query.
Click the drill-through definitions button 
or from the Properties pane,
double-click the Drill-Through Definitions property.
Click the new drill-through definition button.
A drill-through definition is created.
Tip: To change the drill-through name, click the rename
button, type the new name, and click OK.
On the Target report tab, click the
ellipsis (...) button next to the Report box,
and select the drill-through target report.
Under the Parameters box, click the
edit button 
.
Each required and optional parameter defined in the target report
appears in the Parameters dialog box.
For each parameter, click Method and
do one of the following:
To specify not to pass any value, click (Default).
That
is, the default method is to not pass any value.
To specify not to use this parameter, click Do not
use parameter.
The target report is not be filtered
by this parameter.
To pass values from a data item, click Pass data item
value, click Value, and then click
the data item that you want.
Values for the selected data item
are passed to the target report.
To pass values from a source report parameter, click Pass
parameter value, click Value, and
then click the parameter that you want.
Values for the selected
parameter are passed to the target report.
Click OK.
In the Action box, decide how the
target report will be viewed when users click the drill-through
column in the parent report:
To run the target report, click Run
the report.
Run the target report when you want to
see the latest data.
To view the most recently saved output version of the target
report, click View the most recent report.
Saved
output versions are created in Cognos Connection.
To use the default action specified for the report in Cognos
Connection, click (Default).
If you chose to run the target report in the previous step,
in the Format box, click the output format
you want for your report.
Tip: Click (Default) to run the
report using the default format specified for the report in Cognos
Connection.
If you want the target report to appear in a new window,
select the Open in new window check box.
In the Display prompt pages box, decide
whether to display prompt pages:
To always display prompt pages when
the drill-through action occurs, regardless of whether values were
specified for the required parameter values, click Always.
To use the prompt settings of the target report (specified
by the Prompt for Values checkbox in Cognos
Connection - Report Properties - Report tab) to determine whether
to show the prompt pages, click Based on the default prompt
settings of the target report.
To not display prompt pages when the required parameter values
are provided by the drill-through definition, click Only
when required parameter values are missing. This is the
default.
The
drill-through object appears as a blue hyperlink in the report.
This drill-through action can also be started by clicking the Go
To button 
. When users run the source report, they
can click the object to drill through to the target report. If more
than one target report is available, the Go To page
appears with the list of available targets.
Tip: If you run the target report directly, you are
prompted to select a value for the report to run on.
Specify the Drill-through Text
You can specify the drill-through text that appears
when users can drill through to more than one target. For example,
if users from different regions view the report, you can show text
in a different language for each region.
Steps
Right-click the drill-through object and click Drill-Through
Definitions.
If more than one drill-through exists for the object, in
the Drill-Through Definitions box, click a
drill-through definition.
Click the Label tab.
To link the label to a condition, in the Condition box,
do the following:
Click Variable, and
click an existing variable or create a new one.
Click Values, and click one of the variable’s
possible values.
In the Source type drop-down list,
click the source type you want to use.
If the source type is Text, click
the ellipsis (...) button beside the Text box
and type the text you want.
If the source type is Data Item Value or Data
Item Label, click Data Item, and click
a data item.
If the source type is Report Expression,
click the ellipsis (...) button beside the Report Expression box
and define the expression.
If the label is linked to a condition, repeat steps 5 to
8 for the remaining possible values.
Click OK.
When users run the source report and click a drill-through
link, the Go to page appears. The drill-through
text you specified appears for each target. If you did not specify
the drill-through text for a target, the drill-through name is used.
Example - Create a Drill-through Report
You are a report author at The Great Outdoors Company,
which sells sporting equipment. You are requested to create a report
that lists product sales by order method for each product line, and
allows users to drill through from the sales report to view the
product details for any item selected. You will create two reports,
one that contains the details for the item, and another that lists
the product sales for the order methods selected by users.
Steps
In the Cognos Connection Welcome page,
click the Launch link, and then click Report
Studio.
Select the package GO Data Warehouse (query).
In the Welcome dialog box, click Create
a new report or template.
In the New dialog box, click List and
click OK.
In the Insertable Objects pane, on
the Source tab 
,
add the following data items to the list by double-clicking them:
Order method
- Product line
- Product type
- Product name
- Introduction date
- Product image
Tip: You can find these data items in the Order
method and Product folders.
Group the Order method, Product
line, and Product type columns.
Click the filters button 
.
Click the add button, and type the following in the Expression
Definition box:
[Product name]=?p_PN?
Click OK.
Repeat steps 8 to 9 to create the following filter:
[Order method] in ?p_OM?
Change the title of the report to Product Details.
Save the report as Product Details.
From the File menu, click New to
create a new report.
In the New dialog box, click List and
click OK.
In the Insertable Objects pane, on
the Source tab, add the following data items
to the list by double-clicking them:
Order method
- Product line
- Product type
- Product name
- Quantity
- Revenue
Tip: You can find these data items in the Sales
fact and Product folders.
Group the Order method, Product
line, and Product type columns.
Click the filters button .
Click the add button, and type the following in the Expression
Definition box:
[Order method] in ?p_OM?
Click OK.
Right-click the Product Name column
and click Drill-Through Definitions.
Click the new drill through button 
.
Under Report, click the ellipsis (...)
button and select the Product Details report you
created previously.
Click Action and click Run
the report.
Click Format and click HTML.
Click the edit button .
For the item p_OM, under Method,
select Pass parameter value, and select p_OM for
the Value.
For the item p_PN, under Method,
select Pass data item value, and select Product
name for the Value.
Click OK twice.
Change the title of the report to Product Revenue by
Order Method.
Save the report as Product Revenue by Order Method.
Click the run report button 
.
When the report runs, you are prompted to select one
or more order methods. When you click OK, the
list shows the product names as clickable links. When you click
a product name, the second report runs, showing the order method
that was selected in the prompt page of the source report and the
product (the source report’s column value) selected in the first
report.
