Create a Drill-up and Drill-down Report

You can link groups of data items from different queries so that when you drill up or drill down in one query, the data item also drills up or drills down in the linked queries.

When you drill down or up, you can specify whether the column title shows the member caption value or the level label value. By default, titles use member captions. Using level labels may be more meaningful. For example, within the Country or Region column, you drill down on Australia. By default, the column title becomes Australia, the member caption. It may be more meaningful to use Country or Region, the level label, as the column title.

Tip: The Returned Items sample report in the GO Data Warehouse (analysis) package includes drilling. For more information about The Sample Outdoors Company samples, see Sample Reports and Packages.

Before you begin

Before you begin, ensure that you are using a dimensional data source.

Procedure

From the Data menu, click Drill Behavior.
On the Basic tab, in the Report output drill capabilities box, select the Allow drill-up and drill-down check box.
By default, IBM Cognos Report Studio determines which items can be drilled on based on the dimensional structure.

Tip: To enable the report to be used as the source during a package drill-through, select the Allow this report to be a package-based drill-through source check box and click OK. For more information about package drill-through, see the Administration and Security Guide.
To disable drill-up or drill-down for a data item, select the data item in either the Disable drill-up for box or the Disable drill-down for box.

From the Advanced tab, you can change the drill-up or drill-down behavior for any data item by selecting the data item and then choosing one of the following behaviors.

Behavior name	Drill-up behavior	Drill-down behavior
Preserve	The data item's value will remain unchanged.	The data item's value will remain unchanged.
Empty Set	The set of values associated with this data item is set to be the empty set (novalues). For crosstabs, the data item will effectively be removed from the report.	The data item will be removed from the report.
Replace Item	The data item's value will change to become the parent (if a lowest-level summary was drilled up on) or the grandparent (if a lowest-level detail of a dimension was drilled up on) of the item that was drilled on.	The data item's value will change to become the item that was drilled on.
Replace Expression	The data item's value will change to become the children of the parent (if a lowest-level summary was drilled up on) or the children of the grandparent (if a lowest-level detail of a dimension was drilled up on) of the item that was drilled on.	The data item's value will change to become the children of the item that was drilled on.
Change Expression	The data item's value will change to become the value of some other data item in the query.	The data item's value will change to become the value of some other data item in the query.
Depth Based Expression	The data item's value will change to become all data items n generations higher in the dimensional hierarchy than the item that was drilled on. The number of generations or levels is determined by the Depth value.	The data item's value will change to become all data items n generations higher in the dimensional hierarchy than the item that was drilled on. The number of generations or levels is determined by the Depth value.
Ancestor	The data item's value will change to become the data value of the ancestor n generations higher in the dimensional hierarchy than the item that was drilled on. The number of generations or levels is determined by the Depth value.	The data item's value will change to become the data value of the ancestor n generations higher in the dimensional hierarchy than the item that was drilled on. The number of generations or levels is determined by the Depth value.

In the Column title drill behavior box, specify whether to use member captions or level labels as the columns titles for each data item.
From the Data Items Linking tab, you can link groups of data items:
- Click the new button .
- Type a name for this group of linked data items and click OK.
  To rename a group of linked data items, click the group, click the Rename Group of Linked Data Items icon , type a name, and click OK.
- Use the arrows to move data items from the queries on the left to the groups on the right.
  Note: You can only add a data item to one group.

Results

The report will generate links for any item that can be drilled up or down on.

When you run a report, you can drill down or drill up by right-clicking the data item and choosing the action from the context menu. The menu items will be disabled if an item cannot be drilled up or down on.