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.

Before you begin

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

Procedure

  1. Click the Insertable objects icon Insertable objects, or the Queries icon Queries icon, and click the Report icon Report overview icon.
  2. Click the Show properties icon Show properties icon, and in the Properties pane, double-click the Advanced drill behavior property.
  3. 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® Analytics - Reporting determines which items can be drilled on based on the dimensional structure.

  4. 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 IBM Cognos Analytics Administration and Security Guide.

  5. When the report is a drill-through target, to specify whether to apply additional filtering when names from the context in the source report matches names of items in the target report, select the Accept dynamic filters when this report is a drill-through target check box.
  6. 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.
  7. 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.
  8. In the Column title drill behavior box, specify whether to use member captions or level labels as the columns titles for each data item.
  9. From the Data items linking tab, you can link groups of data items:
    • Click the New Group of Linked Data Items icon new icon.
    • 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 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.