...

Drill Up/Down Guideline Product(s): IBM Cognos 8

by user

on
Category: Documents
1

views

Report

Comments

Transcript

Drill Up/Down Guideline Product(s): IBM Cognos 8
Guideline
Drill Up/Down
Product(s): IBM Cognos 8
Area of Interest: Report Design
Drill Up/Down
2
Copyright
Copyright © 2008 Cognos ULC (formerly Cognos Incorporated). Cognos ULC
is an IBM Company. While every attempt has been made to ensure that the
information in this document is accurate and complete, some typographical
errors or technical inaccuracies may exist. Cognos does not accept
responsibility for any kind of loss resulting from the use of information
contained in this document. This document shows the publication date. The
information contained in this document is subject to change without notice.
Any improvements or changes to the information contained in this document
will be documented in subsequent editions. This document contains
proprietary information of Cognos. All rights are reserved. No part of this
document may be copied, photocopied, reproduced, stored in a retrieval
system, transmitted in any form or by any means, or translated into another
language without the prior written consent of Cognos. Cognos and the
Cognos logo are trademarks of Cognos ULC (formerly Cognos Incorporated)
in the United States and/or other countries. IBM and the IBM logo are
trademarks of International Business Machines Corporation in the United
States, or other countries, or both. All other names are trademarks or
registered trademarks of their respective companies. Information about
Cognos products can be found at www.cognos.com
This document is maintained by the Best Practices, Product and Technology
team. You can send comments, suggestions, and additions to
[email protected] .
IBM Cognos Proprietary Information
Drill Up/Down
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
PURPOSE ............................................................................................................4
APPLICABILITY .....................................................................................................4
2
HOW DRILL UP/DOWN WORKS.................................................................... 4
3
ORGANIZE ITEMS INTO SETS ....................................................................... 5
3.1
3.2
DEFAULT MEMBER SETS ..........................................................................................5
CUSTOM MEMBER SETS ...........................................................................................5
4
DEFINE SET DRILLABILITY........................................................................... 7
4.1
4.2
DEFAULT DRILLABILITY SETTINGS ..............................................................................7
RULES FOR CUSTOM DRILL BEHAVIOURS ......................................................................9
5
DEFINE DRILL “TRANSITIONS” .................................................................. 12
5.1
5.1.1
5.1.2
5.1.3
5.1.4
5.1.5
5.1.6
5.1.7
5.2
5.3
DRILL BEHAVIOURS ............................................................................................. 12
Replace Expression .......................................................................................... 13
Replace Item .................................................................................................. 14
Ancestor (N) ................................................................................................... 16
Preserve ......................................................................................................... 18
Depth Based Expression (N) ............................................................................. 19
Change Item ([X]) ........................................................................................... 20
Empty Set....................................................................................................... 21
DEFAULT DRILL BEHAVIOURS .................................................................................. 21
COPYING VERSUS MODIFYING EXPRESSIONS DIRECTLY ................................................... 22
EXAMPLE 1 - CREATING LIST THAT SHOWS ALL ITEMS AND A CHART TO ONLY
SHOW THE TOP N ................................................................................................... 25
MAKE THE REPORT .......................................................................................................... 25
ENABLING DRILL ............................................................................................................ 28
HOW EXAMPLE 1 WORKS: ................................................................................................. 32
IBM Cognos Proprietary Information
Drill Up/Down
4
1 Introduction
1.1
Purpose
This document describes the ideas behind how drill up/down in IBM Cognos
8. The default behaviours are defined and descriptions are provided for the
techniques used to override these defaults with advanced drill behaviours and
member sets.
1.2
Applicability
The focus of this document is for IBM Cognos 8.2. However, many of these
topics will also be applicable in the releases of IBM Cognos 8.1.
2 How Drill Up/Down Works
When evaluating and defining drill up/down there are a sequence of steps
that need to be performed. This process is used internally when evaluating
default drill behaviours and can be used by a report author when the choice
is made to define custom drill behaviours for a report.
As an overview, the key steps in this process are:
1. Organize Data Items into Sets
By default this is accomplished through the hierarchies present within
your package. Custom sets can be created by authoring Member Sets.
2. Define Drillability for each Set
Drill up/down can be enabled or disabled for an entire report or
disabled for particular Data Items within a query. There are also limits
on drillability imposed by the set structures defined in step 1.
3. Define drill “Transitions”
Each Data Item has an action/behaviour that will be performed when
the drill takes place. These behaviours address how a drill event will
impact or change the expression of each data item.1
1
In some cases a new data item will be created internally so that dependent expressions,
such as filters, are not impacted by a drill event. See section 5.3 for details.
IBM Cognos Proprietary Information
Drill Up/Down
5
3 Organize Items into Sets
3.1
Default Member Sets
Figure 1
The following are the default rules used to group query items into sets and
determine the item relationships for drill events. Theses are applicable when
no additional author intervention is applied other than enabling drill within the
report:
1. Only items in the layout are considered (items in a query that aren’t
included in a list, crosstab, chart, etc. won’t be changed)
2. Items are put into default sets by hierarchy2. In Figure 1, there are 3
sets:
a. The [Years] and [Year] data items are grouped together into a
single set because they are both from the same hierarchy.
b. [Order Method] is a set on it’s own because it is the only data item
from the Order Method hierarchy.
c. [Product line] is also a set of its own because, like [Order
Method], there are no other data items from the same hierarchy
present on the layout.
3.2
Custom Member Sets
Member Sets can be authored for a particular query. Most of the more
sophisticated drill up/down features will require definition of custom member
sets. Some of the basic concepts for custom member sets are:
1. Any query items within the scope of a particular query can be made
part of a set.
2. Query items that represent members from the same hierarchy do not
need to be in the same set. Defining these items as separate sets is
useful for allowing independent drill on the different sets of members
from the same hierarchy.
2
Default rollup calculations created by default or through the studio UI actions will also be
assigned based on the data source hierarchies. Determination of hierarchy associations for
more advanced calculations may require authored member sets.
IBM Cognos Proprietary Information
Drill Up/Down
6
3. Any “leftover” items that are in the layout and have not been put in a
set will be placed in a default set using default rules as described in
section 3.1.
4. A query item that is used in a custom member set will not be available
for drill up and drill down until a drill behaviour is defined for that
item.
(This seems unusual, but there are good reasons for this which will be
explained later.)
The following steps can be used to author custom member sets:
1. Open the Query containing the items to be placed into sets
2. Set “Define Member Sets” to “Yes” in the properties of the query (the
‘Member Sets’ tab for the query will appear as in Figure 2).
Figure 2
3. On the Member Sets tab, drag query items that should be affected by
a particular drill event into a member hierarchy.
For example, if you have two data items ([Product Line] and [Product
Type]) that should work together, then a single structure should
contain both items. Each new root item defines a new grouping or
association between individual custom member sets. A separate root
item would be defined for the [Region] query item3 because it is not
related to [Product line] or [Product type].
3
Note that the default assignment of member sets based on the dimension hierarchy will
accomplish the same result without creating a custom member set for [Region]. If there is no
need for advanced drill behaviours to be applied to [Region] then there would not be a need
to create a custom member set for this item.
IBM Cognos Proprietary Information
Drill Up/Down
7
Figure 3
If [Product Line] and [Product Type] should be both drillable without
affecting each other then the custom member sets for [Product Line]
and [Product Type] would be defined as separate root items rather
than nesting them within a single branch. This operation explicitly
overrides the default set associations that will occur when custom
member sets are not defined.
Figure 4
4 Define Set Drillability
4.1
Default Drillability Settings
For query items included in the layout that are not part of authored member
sets and have no custom drill behaviour the drill rules will be assigned by the
system as follows:
Drill Down Enabled
1. The query item must be associated with a member hierarchy.
2. Only items on the layout are evaluated.
3. The item must be the most detailed item reported for a particular
dimension
This is independent of the layout object referencing the query. If a
lower level of detail is reported in a second layout object (list,
crosstab, etc.) then only the layout object with the most detailed
members will have drill down enabled. If this is not desired then
separate member sets can be authored to remove the link between
the data items based on the default set allocations for items from the
same hierarchy.
a. In the following list, Figure 5, items from the time dimension are
being reported by Country against Revenue. Since there is no
breakdown of country into lower-level details (i.e. State or City), it
can be drilled down on. However, each Year has been broken
down into a lower level of detail (i.e. Quarter), so the Year
CANNOT be drilled down on. Members from the Quarter level, i.e.
“2004 Q 1”, can be drilled down on because they are not broken
down into lower levels of detail.
IBM Cognos Proprietary Information
Drill Up/Down
8
Figure 5
b. The results of drilling down on “2004 Q 2” are shown in Figure 6
below. Note that the Months of Q2 can be drilled down on
because they are now the most detailed level from the Time
dimension. Country continues to be drillable as it was unaffected
by the drill action on “2004 Q 2”.
Figure 6
4. The item must not be “isolated”.
As in Rule 3 of section 4.1, isolation occurs whenever drill down past a
leaf level has been performed. In this case, the siblings of the leaf
will be removed and the resulting item after the drill (the single
isolated member) will have drill down disabled.
a. As an example of isolation, we will drill down on a month,
2004/Jun, from the previous drill result, Figure 6. From the Years
hierarchy shown earlier, we can see that the Month level is the
lowest level in the hierarchy. This means that the months are leaf
nodes in the hierarchy. Note that after the drill on 2004/Jun the
siblings, 2004/May and 2004/Apr, have been removed so that the
focus of the results for each country is on 2004/Jun alone.
2004/Jun is now “isolated” and drill-down is disabled.
Figure 7
IBM Cognos Proprietary Information
Drill Up/Down
9
In the previous examples, all other items included from the Time dimension
will implicitly set drill down to Disabled because they are not the most
detailed level included in the layout4.
Drill Up:
By default, the following rules are used to decide that an item will have drill
up enabled.
1. The query item must be associated with a member hierarchy.
2. The item must be either:
a. the most detailed item reported for a particular hierarchy.
b. the PARENT of the most detailed item reported for a particular
dimension
(As with drill down, this is layout independent. To overcome this,
the query items can be split into separate member sets as in
section 3.2.)
c. The least detailed item in the set cannot be at the top of the
hierarchy.
3. All other items will implicitly set “drill up Disabled”
4.2
Rules for Custom Drill Behaviours
Enabling/Disabling of drill down is determined using the following rules. The
rules are listed “strongest to weakest”. I.e. Rule 1, disabling drill, will take
precedence over any subsequent rules.
Rule 1: (Strongest Rule) Authored Disabling
A user can disable any item deemed to be drillable by setting the “disable drill
down” or “disable drill up” flags manually. When set, the items are simply
not drillable.
(In Figure 8, Country has been set to disallow drill down)
4
A member set with both a replace expression and a replace item in it is considered a “detail
summary” set and isolation will not occur.
IBM Cognos Proprietary Information
Drill Up/Down
10
Figure 8
Rule 2: Using Member Sets without defined Drill Behaviours
If no drill behaviour exists for a query item that is part of a member set, drill
up and drill down will be disabled for that item.
Figure 9
In Figure 9, if Quarter were part of a Member Set (see above), and had
default drill down behaviour as shown, then Quarter would NOT be drillable
even though, for that member set, it is deepest in the Years hierarchy.
The creation of a member set for a query item implies that the author has
taken control over the query item behaviour. As such, the drill behaviours are
disabled until the author has completed the full customization of the query
item.
IBM Cognos Proprietary Information
Drill Up/Down
11
Rule 3: Top or Bottom of the Hierarchy
Items can become isolated during drill when drilling on a leaf member. When
drilling on a leaf member the siblings will be removed and only the single,
“isolated”, leaf member will be displayed.
Figure 10
Isolated Items will be drill down disabled and drill up enabled5.
Similarly, items at the top of the hierarchy will be drill up disabled and drill
down enabled to prevent drilling past the root of the hierarchy.
Note that some limitations exist because the system cannot always determine
whether a member is isolated. The ability to determine this relationship within
the hierarchy varies depending on the functions applied and the capabilities
of the OLAP provider.
Rule 4: Using Member Sets with defined Drill Behaviours
Note that limits imposed by previous rules will override the drill up/down
behaviours listed in Table 1. See section 5.1 for definitions of the individual
drill behaviours.
Table 1
Behaviour
replaceExpression
replaceItem
Preserve
depthBasedExpression
drillChange
5
Drill Down
Enabled
Disabled
Enabled
Enabled
Enabled
Drill Up
Enabled
Enabled
Enabled
Enabled
Enabled
With the default drill behaviour the drill up on an isolated member will “de-isolate” or
restore the prior expression rather than returning the parent of the isolated member.
IBM Cognos Proprietary Information
Drill Up/Down
12
5 Define Drill “Transitions”
With a few minor exceptions, Drill Up/Down in cognos8 is “member based”
rather than “level based”. A single member from a result set will serve as
part of the input value for a Drill Up/Down operation when a “Transition”
occurs.6
The input value provides the full context of a Drill Up/Down operation and is
defined in 4 parts:
1. Drill direction: up or down
2. Data item name (i.e. “Product Type”)
3. Query name (i.e. “Query1”)
4. “Input member”: a “member Unique Name”7.
The majority of the drill behaviours are based upon this input value.
IMPORTANT: DEFINED DRILL BEHAVIORS WILL BE APPLIED TO THE
INDIVIDUAL QUERY ITEMS. A SINGLE DRILL INPUT CAN BE USED
BY MULTIPLE QUERY ITEMS, EACH WITH DIFFERENT BEHAVIORS.
THIS MEANS THAT ONE DRILL ACTION CAN IMPACT MANY RELATED
QUERY ITEMS.
5.1
Drill Behaviours
In the examples that follow, the drill behaviours will be described against the
Sales Territory hierarchy in Figure 11.
Figure 11
6
Note that when working with multi-edged layout objects, such as a crosstab or chart, a drill
even on a measure cell will trigger separate evaluation for each projected edge (rows and
columns)
7
A Member Unique Name, MUN, is a unique way of defining a specific reported value. For
example, if reporting values for a column named “City”, “London” in the United Kingdom
would have a different member unique name value from “London” in Canada.
IBM Cognos Proprietary Information
Drill Up/Down
5.1.1
13
Replace Expression
Drill down:
During drill down this behaviour will make the expression for this query item
a set of children of the drill input at the most detailed level.
children(input member)
For example, the result of drilling on the left chart for input member “United
States” would be the right chart (children of “United States”), Figure 12.
Figure 12
Drill up:
Drill up using Replace Expression must essentially undo the effects of a drill
down.
If input member is at the most detailed level, this operation would be defined
by:
children(grandparent(input member))
The logic behind this expression can be explained by working through an
example of drilling up from our previous result, Figure 12. After the drill down
there are 4 possible input values can be used for drilling up: Boston, Seattle,
Los Angeles, or Miami.
Each of these members has the same Parent, “United States”. The parent of
“United States” is “Americas”. The grandparent for each of the possible input
members would then be “Americas”. The Children of the grandparent,
“Americas”, are the following set of members: “United States”, “Canada”,
“Mexico”, and “Brazil”. So, for each of the possible input members the result
of drilling up is the children of “Americas”.
If the input value is a member one above the most detailed level, then the
drill-up would be defined by:
children(parent(input member))
To show this graphically, drilling up on either a City or the Country query
items would produce the output on the right, Figure 13.
IBM Cognos Proprietary Information
Drill Up/Down
14
Figure 13
Notes:
If drill down behaviour is replaceExpression and drill up behaviour is
“default”, drill up to the “home level” will restore the initial expression for this
item instead of returning the full set of children of the grandparent as per the
replaceExpression drill up behavior shown above.
For example, if a custom set of the members for “United States” and
“Canada” were the original expression then drilling up with the drill behaviour
set to “default” would return only “United States” and “Canada” when the
Country level is reached rather than the full set of countries in “Americas”.
Special case for Isolation:
If drill down occurs on a leaf when using Replace Expression, the item will be
“isolated” (its siblings will be removed and only that item will be displayed).
This avoids the set of members from becoming an empty set. See section
4.1, Rule 3, for additional information on member isolation.
Exceptions:
If both an item with behaviour “replaceExpression” AND an item with
behaviour “replaceItem” are part of the same member set, drill down will not
isolate the detail, it will allow children(x) to be empty assuming that a rollup
summary member can be used to drill back up. This is a commonly used
practice for Analysis Studio sets where a rollup row or column is typically
displayed with the detailed members for a set.8
5.1.2
Replace Item
Drill Down:
When drilling down the expression of the query item will become the input
member itself.
If Replace Item is applied to the lowest level query item in the report layout
then this drill behaviour will update the query item to be the single input
member after drill, Figure 14:
8
For cases where the details cannot be summarized by a parent member this may result in a
calculation over an empty set. This scenario can cause the summary row or column to be
empty as well.
IBM Cognos Proprietary Information
Drill Up/Down
15
Figure 14
Typically though, the Replace Item drill behaviour is used in combination with
another query item from the same set whose drill behaviour is Replace
Expression. For example, the left-hand “Sales Territory” column (in Figure 15
below) is set to use Replace Item. When drilling down on the lower-level
Country column (“United States”) this will update the Sales Territory query
item to contain the input country member.
Figure 15
Drill Up:
For the case when the query item with “Replace Item” is at the most detailed
level in the report layout9 then the query item expression using “Replace
Item” would be defined by:
grandparent(input member)
For example, the parent of “Los Angeles” is “United States” and the
grandparent is “Americas”. So, the drill up on “Los Angeles” would produce:
Figure 16
If the query item with “Replace Expression” is one level above the most
detailed level query item on the layout, then the expression for the query
item with “Replace Item” after drilling up on this same item would be
evaluated as:
Parent(input member)
9
As with drilling down on a query item with the “Replace Item” behaviour, the drill up using
“Replace Item” is most effective when used in combination with other query items using
different drill behaviours. Using “Replace Item” on it’s own typically has limited practical use.
IBM Cognos Proprietary Information
Drill Up/Down
16
For example, in a nested structure as in Figure 17, “United States” in the leftmost column is one level above the cities listed in the second column and has
a drill behaviour set to “Replace Item”. Drilling up on the “United States”
value in the left-most column will return the parent of “United Stated”,
“Americas”.
Figure 17
Exception:
If drill down behaviour is replaceItem and drill up behaviour is “default”, drill
up to the “home level” will restore the initial expression for this item.
5.1.3
Ancestor (N)
Drill Down:
During a drill down the first action for Ancestor (N) is to compute the result of
the drill down and determine the child set of the input member. The next
step is to find the ancestor member N levels above the members in the child
set from the drill down event.
The Ancestor(N) behaviour is typically used in a report where multiple levels
from the same hierarchy are nested on an edge. Here the most detailed level
in the layout would normally be defined with “Replace Expression” and the
Ancetor(N) would be applied to control the higher levels included on the
layout.
• Ancestor (1) would be equivalent to “Replace Item”. The ancestor 1
level above the set of children is the input member itself.
• Ancestor (2) returns the parent of the input member. The ancestor 2
levels above the set of children from the drill down result is the parent
of the input member.
For example, in Figure 18 the Country query item is defined with Ancestor(1)
as the drill-down behaviour. Drilling on “Los Angeles” will result in “Los
Angeles” as the result of Ancestor(1) (i.e. it is the parent of the children of
the input member, “Los Angeles”).
IBM Cognos Proprietary Information
Drill Up/Down
17
Figure 18
To continue the example, if the Country were set to use Ancestor(2) then the
result of drilling down on “Los Angeles” would be “United States” as in Figure
19 (i.e. the grandparent of the children of the input member, “Los Angeles”)
Figure 19
Drill Up:
Similar to the drill-down, the Ancestor (N) is computed after the drill-up is
performed on the input member. This means that the parent of the input
member is the starting point for computing the Ancestor (N) drill-up result.
• Ancestor(1) would be the grandparent of the input member. One
levels above the result of drilling up.
• Ancestor(2) would be the great-grandparent of the input member.
• Etc.
For example, in Figure 20 the drill behavior of “United States” is set to
Ancestor (1). Drilling up on Seattle will produce the result on the right.
Figure 20
The input member is “Seattle”. The first step in evaluating the Ancestor (1)
behavior is to compute the drill-up result, which is “United States”, the parent
of “Seattle”. Then the ancestor one level above “United States” (the parent)
is evaluated and the final result of the Ancestor (1) drill behavior for first
column is “Americas”.
IBM Cognos Proprietary Information
Drill Up/Down
18
Notes:
The depth offset is based on the RESULT of drilling up or down. If the input
member is at the most detailed level and we’ve drilled down, Ancestor 2
would return the member two above the children of the input member (which
is the parent of the input member).
5.1.4
Preserve
Using the Preserve drill behaviour will enable drill on a data item (so that it
may provide an input member to a drill action) but the underlying expression
of the data item will be kept as it is and will not be changed during a drill
action.
The role of Preserve is to supply input values to other data items in the same
member set so that their transitions will be reflected on drill. To be
meaningful, the preserve query item’s expression should reference another
query item that will be updated by a drill event.
For example, we can create a query where two items [TopTwo] and
[Country] exist in the same member set. The expression for [TopTwo] will
identify the top two Countries based on Revenue:
topCount([Country],2,[Revenue])
[Country] is not included on the layout but is referenced because it is in the
same member set hierarchy as [TopTwo].
Figure 21
Without drill behaviours and member sets defined, the drill event will replace
the expression for [TopTwo] and the entire set of children will be returned as
below in Figure 22.
Figure 22
The expression for the top two can be applied to the result of the drill by
setting the drill behaviours for the two items to:
• [TopTwo]: Preserve
IBM Cognos Proprietary Information
Drill Up/Down
•
19
[Country]: Replace Experssion
After a drill down on “United States” the [TopTwo] will report the top two
cities within the United States.
Figure 23
5.1.5
Depth Based Expression (N)
Similar to the Ancestor (N), this drill behaviour is applied after the result of
the drill is computed. However, instead of a single ancestor member, the
entire level of the ancestor is returned.
Drill Down:
On drill down the drill behaviour can be expressed as10:
level(ancestor(children(<input member>) , N))
Drill Up:
On drill up the expression describing the drill behaviour would be:
level(ancestor(parent(<input member>) , N))
For example, in Figure 24 to Figure 26, the data item containing “United
States” is set to use Depth Based Expression (0). After drill down the entire
level for cities is returned, not just the cities under “United States” as would
be displayed using the default drill behaviours.
Figure 24
10
More appropriately, this expression would use the firstChild() function instead of children()
to return a single member. The single member is required to satisfy the input argument of
the ancestor() function. However, the drill result is not computed directly through olap
expressions so the expression is only useful as a concept for understanding the drill
behaviour.
IBM Cognos Proprietary Information
Drill Up/Down
20
Drilling up on “Calgary” when using Depth Based Expression (0) will return
the entire level of the parent (not just the parent member, “Canada”).
Figure 25
If the drill up behaviour were Depth Based Expression (1) then the level one
above the parent will be returned after drilling up on “Calgary”. In our case
this is the single member “Americas”.
Figure 26
5.1.6
Change Item ([X])
On drill, the expression for this item will change to be the expression of
another data item, [X]. The drill input value is not considered when using
Change Item.
Change Item is used to cause the list of values for an item to change to a
completely different set of values.
New for IBM Cognos 8.2:
The behaviour, Change Item, is now “transitive”. After the drill changes a
data item to use a new item’s expression, the drill behaviour of the new item
will be applied on subsequent drills. This allows for custom chains of item
changes to be defined. The effect is that after the Change Item ([X]) drill
takes place, the new drill behaviour for the resulting members will be defined
by the drill behaviour defined for data item [X].
IBM Cognos Proprietary Information
Drill Up/Down
21
For example, a query could be constructed to show two alternate views of a
set of data. If the query contained a data item to show all countries,
[Country], and one to show the top two countries, [TopTwo], then Change
Item could be used to switch from the an initial view of the highlighted toptwo countries to a more detailed view of all countries, Figure 27. The drill
behaviours would be defined as:
• TopTwo: Change Item ([Country])
• Country: Change Item ([TopTwo])
Figure 27
5.1.7
Empty Set
This drill behaviour is used to remove a set of displayed values from the
report by creating a set that does not contain any members.
For example, if a crosstab were used to display Countries along with the top
two cities under “United States” then drilling on “Canada” may mean that the
top two data item for “United States” no longer needs to be displayed. Using
Empty Set for the top two data item will remove the results from the report
after drill:
Figure 28
5.2
Default Drill Behaviours
Conditions for applying default drill behaviours:
• Only query items included in the layout are considered.
• Only query items in from the same hierarchy as the drilled member
will be considered.
• The items must be drillable (see the section “Define Set Drillability” for
additional details):
o Drill Down: if the item can be drilled down on, drillability has
determined that it is the deepest item from the dimension. Note
that query items defined within custom member sets will not be
assigned a default drill-down behaviour.
IBM Cognos Proprietary Information
Drill Up/Down
22
o
Drill Up: if the item can be drilled up on, drillability has determined
that it is the deepest item or the parent of the deepest item from
the dimension.
Rules for default drill behaviours:
• The deepest item from a hierarchy will be assigned
“replaceExpression” as a drill down behaviour and “default” (meaning:
replaceExpression with restore original expression at home level) as a
drill up behaviour.
• The next-to-deepest item will be assigned “replaceItem” as a drill
down behaviour and “default” (meaning: replaceItem with restore
original expression at home level).
• The third deepest item will be assigned Ancestor (2).
5.3
Copying versus Modifying Expressions Directly
There is an important difference in behaviour between when member sets
are or are not used with drill behaviours.
When member sets are not defined then drill behaviours that change
expressions, such as Replace Expression or Replace Item, will make a copy of
the data item and the drill behaviour will be applied to the copied data item
while the original is left untouched.
When member sets are defined for the data items then the drill behaviour will
be applied to the original data item itself rather than referencing a copy of
the data item.
The reason for this difference can best be seen when a filter is applied to a
query. For example, the filter
[Country]=[United States]
is applied to the following report query
Figure 29
Without member sets the result of drilling down on this report would be:
IBM Cognos Proprietary Information
Drill Up/Down
23
Figure 30
After drilling down, the [Country] data item would no longer contain the
“United States” member. If the original [Country] member were modified by
the drill then the filter would eliminate all the rows from the drill result.
However, during the drill, a copy of [Country] was made to perform the drill
down. The original [Country] data item is left unchanged and when it is
referenced in the filter expression there will still be results returned for the
query after drilling down.
On the other hand, if a report author chooses to define advanced drill
behaviours with member sets then this is considered a conscious choice to
assume control over the way that the data items will behave together within
the query during drill events. Using the same example but defining a member
set for [Country] the result of the drill becomes:
Figure 31
When using member sets the result of the drill becomes empty because the
original [Country] data item expression is modified by the drill and the
resulting set of members will not contain [United States] as specified in the
filter. Since there are no matching members the filter will remove all rows
from the result of the drill down on “United States”.
However, when authoring such a query this scenario can be handled by
creating a stand-alone copy of [Country] that will be used on drill so that the
original [Country] query item will remain unchaged in the filter expression.
If we were to do so in the above example, a new query item named “Display
Country” would be created in the query and the expression would be a direct
reference to the existing [Country] query item.
IBM Cognos Proprietary Information
Drill Up/Down
24
Figure 32
The next step would be to cut [Country] from the report layout and insert
[Display Country] in its place.
Figure 33
The new query item would need a drill behaviour (in our case, Replace
Expression) and would be defined as a new member set in the query.
Figure 34
Once this is complete, performing a drill on [Display Country] will replace the
expression with the children of the input member, leaving the [Country]
query item unchanged so that the filter condition is still valid.
Figure 35
IBM Cognos Proprietary Information
Drill Up/Down
25
Example 1 - Creating List that shows all Items and a Chart
to only show the Top N
Make the Report
1. Create a list report with the member whose children members you’d like
use for the top N. For the sake of this example we are using the root
member of the Sales Territory dimension from the Great Outdoors
Company sample PowerCube.
Important: drag the member, not the level, onto the report!
Figure 36
2. Cut (not delete) Sales Territory column from the list. This will leave the
data item in our query for reuse in later expressions.
3. Drag a query calculation object from the Toolbox into the list. This will be
the base set of members for the Top N.
Name the new calculation “Locations” and use the following expression to
generate the children of the Sales Territory root member:
children([Sales Territory])
Figure 37
IBM Cognos Proprietary Information
Drill Up/Down
26
4. Drag the Revenue measure into the list next to the Locations calculation.
5. Drag a new query calculation into the list. Name the calculation “Top
Locations” and use the following expression to generate the top 2
locations based on Revenue.
topCount([Locations], 2, [Revenue])
Figure 38
6. Use “Cut” to remove Top Locations from the list
7. Drag a chart onto the page layout above the existing list object. Select
the default column chart.
8. Select the new chart and, in the properties pane, change the Query
property to reference Query1 (the same query as the list).
9. From the Data Items, add the following to the chart
• Measure (y-axis): [Revenue]
• Category (x-axis): [Top Locations]
Figure 39
IBM Cognos Proprietary Information
Drill Up/Down
27
10. Run the report to get the following. Notice that the list will show all
Locations while the Chart only displays the top 2 by revenue.
Figure 40
The next section will address defining the drill behaviours. Close the Cognos
Viewer window and return to Report Studio.
IBM Cognos Proprietary Information
Drill Up/Down
28
Enabling Drill
1. Open the Drill Behaviour dialog from the Data menu and enable “Allow
drill-up and drill-down”
2. Go to the Advanced Tab and apply the following
• Change both drill up and drill down behaviours for “TopLocations” to
“Preserve”. If you recall from section 5.1.4, “Preserve” will maintain
the expression within the query item during a drill action.
• Change drill down behaviour for “Locations” to “Replace Expression”
(shown below)11. From section 5.1.1 we can see that on drill down
“Replace Expression” will return the children of the input member
while on drill up the parent of the input member will be returned.
3. Use the Query Explorer to select Query1 and change the setting “Define
Member Sets” to “Yes”.
11
In this case drill up is not required. See the section 4.1 for details on the default drill-up
behavior.
IBM Cognos Proprietary Information
Drill Up/Down
29
4. Choose the “Member Sets” tab which now appears under the query item
list for Query1.
5. Drag Locations and Top Locations into the member sets as shown. The
nesting of the custom member sets establishes an association between
the two query items for drill actions.
6. To avoid name conflicts between query items referenced in expressions
and member sets, select the Locations custom member set and change
the name property to “Custom Locations Set”
IBM Cognos Proprietary Information
Drill Up/Down
IBM Cognos Proprietary Information
30
Drill Up/Down
31
7. Run the report and click on the “Americas” member to drill down and
obtain the following output for the drill-down action.
Notice that after the drill the chart still only displays the top 2 members.
However, the drill has updated the set being used for the topCount
calculation and now returns the top two countries within the Americas sales
territory instead of the top two sales territories.
IBM Cognos Proprietary Information
Drill Up/Down
32
How Example 1 Works:
The chart shows the query items [Top Locations] and [Revenue], and the list
shows the [Locations] and [Revenue] query items. Drilling on a member of
[Top Locations] or [Locations] is equivalent because the member sets are
linked together and each set can supply a valid member from the Sales
Territory hierarchy as the drill input.
In this example, it makes no difference whether [Locations] is in the layout or
not to have the chart behave as a “Top 2 filter”. With the drill behaviours and
member sets established a user can drill on [Top Locations] and this will
perform the drill action on [Locations], eliminating the need to display
[Locations] on the report at all.
Run the report to generate the initial output in Cognos Viewer.
For the sake of the example, let’s use “Americas” from the Chart as the drill
down member. Americas will be used to generate the INPUT value for the
drill actions as follows:
Input:
• Drill Direction: Down
• Data Item:
[Top Locations]
• Query:
Query1
• Member value:
[great_outdoors_company].[Sales
Territory].[Sales Territory].[Sales territory]>:[PC].[@MEMBER].[1099]12
Processing:
For each step of processing below, the related authoring step will be listed so
that you can refer to the rules in the chart as well as the corresponding
numbered detail sections of this document.
1. HAS [Top Locations] been ORGANIZED INTO A SET BY THE AUTHOR?
(Section 3 “Organize Items into Sets”)
YES
12
This is the Member Unique Name (MUN) for the “Americas” member. “Americas” is the text
caption for the member that is uniquely identified by this MUN.
IBM Cognos Proprietary Information
Drill Up/Down
33
2. DOES [Top Locations] HAVE A DRILL-ENABLED BEHAVIOR?
(Section 4 “Define Set Drillability”)
YES
3. Apply Drill behaviours for the [Locations] set.
(Section 5 “Define Drill “Transitions””)
Item
Locations
Top Locations
New Expression
children([great_outdoors_company].[Sales Territory].[Sales
Territory].[Sales territory]->:[PC].[@MEMBER].[1099] )
TopCount([Locations], 2, tuple([Outdoors Power
Cube].[Measures].[Revenue]))
If the children of the “Americas” Sales Territory returns a set of countries
(Canada, US, Mexico, Chile, Argentina) the chart will list only the two
countries with the highest Revenue.
IBM Cognos Proprietary Information
Fly UP