Create a Drill-up/Drill-down
Report
You can create a report that allows the reader to drill
down to lower level data sets or drill up to higher-level data sets,
provided you are using a dimensional or dimensionally modeled relational
(DMR) data source. This will allow you to rerun the report for a
different data set without having to regenerate the report.
An example of a dimensional hierarchy might be:
Years - Year - Quarter - Month
Drilling up and down allows you to view more general or more
detailed information on your data within such a predefined dimensional
hierarchy, without having to create different reports.
Before you begin, ensure that you are using a dimensional
data source.
Steps
From the Data menu, click Drill
Behavior.
On the Basic tab, in the Report
drill capabilities box, select the Allow drill-up and
drill-down check box.
By default, Report Studio determines which items can be drilled
on, based on the dimensional structure.
Tip: If you want the report to be used as the source
during a package drill-through, select the Allow package
based drill-through 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.
|
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 of the item drilled on (if a lowest-level summary was drilled
up on), or the grandparent (if a lowest-level detail of a dimension
was drilled up on). | The data item’s value will change to become
the item drilled on. |
Replace Expression | The data item’s value will change to become
the children of the parent of the item drilled on (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). | The data item's value will change to become
the children of the item drilled on. |
Ancestor | The data item's value will change to become
the data value of the ancestor "n generations higher" in the dimensional
hierarchy from the item 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 from the item drilled on. The number of generations or
levels is determined by the Depth value. |
Depth Based Expression | The data item's value will change to become
all data items "n generations higher" in the dimensional hierarchy from
the item 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 from
the item drilled on. The number of generations or levels is determined
by the Depth value. |
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. |
Click OK.
The report will generate links for any item that can
be drilled up or down on.
You can perform 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.
Member Sets
Member sets are used to group data items that are logically
related for various actions, such as drill actions, zero suppression,
and ranking. They can be defined as a flat list or, as a tree structure,
where member sets that are in the same parent chain are considered
related.
For example, for drill operations, a member set defines the set
of items that can potentially change when a given item in the set
is drilled on. The values of other items in the query or even those
in the same hierarchy are preserved when any item in this set is
drilled on. Usually, a member set references items that have logical
roles in a drill action, such as a detail, a summary of the detail,
or a parent of the detail. A single data item can belong to only
one member set.
If you do not define member sets for items, the Cognos 8
server associates items into default member sets and behaviors using
simple dimension rules on item expressions. You can override the
behavior for a particular item while other items continue to use
the default.
When you define a member set, you must explicitly define behaviors
for each item in the set. Items in the set that have no behaviors
have their values preserved.
Drill behaviors always act from a root member set. This means
that when an item is drilled on, the root member of its member set
is found and all items from the root down are processed. Although
calculations and aggregates are not directly related by hierarchy,
they respond because of their dependence on the items upon which
they are based.
Create a Member Set
Create a member set when you want to define a non-default
drill behavior. You specify what items respond to a drill action
by adding them to the member set.
Steps
Pause the pointer over the query explorer button 
and click the query in which you want to create
a member set.
In the Properties pane, click the Define
Member Sets property and click Yes.
Click the Member Sets tab.
In the Insertable Objects pane, drag
the items that you want to the work area.
To define a member set as a tree structure, drag the item that
will serve as the root item of the set to the work area, and then
drag other items over the root item to create the tree structure.
You can also nest member sets.
