...

Creating Dynamic Analysis Studio Type Reports in Report Studio Part-2

by user

on
Category: Documents
1

views

Report

Comments

Transcript

Creating Dynamic Analysis Studio Type Reports in Report Studio Part-2
Proven Practice
Creating Dynamic Analysis Studio
Type Reports in Report Studio
Part-2
Product(s): Report Studio
Area of Interest: Report Design
Creating Dynamic Analysis Studio
Type Reports in Report Studio Part2
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
Creating Dynamic Analysis Studio
Type Reports in Report Studio Part2
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
PURPOSE ............................................................................................................4
APPLICABILITY .....................................................................................................4
2
EVALUATION OF THE PROBLEM.................................................................... 4
2.1
2.2
2.3
2.3.1
2.3.2
2.3.3
2.3.4
2.3.5
2.3.6
2.3.7
2.4
2.5
ENABLING DRILL-UP AND DRILL-DOWN .......................................................................4
BREAKING DOWN THE DRILL BEHAVIOUR .....................................................................7
FIXING THE DRILL BEHAVIOUR ..................................................................................8
Step 1 ..............................................................................................................8
Step 2 ............................................................................................................ 10
Step 3 ............................................................................................................ 12
Step 4 ............................................................................................................ 13
Step 5 ............................................................................................................ 14
Step 6 ............................................................................................................ 14
Step 7 ............................................................................................................ 15
EXAMINING THE BEHAVIOUR OF MORE SUBTOTAL ......................................................... 16
MAKING “MORE SUBTOTAL” DYNAMIC ....................................................................... 16
IBM Cognos Proprietary Information
Creating Dynamic Analysis Studio
Type Reports in Report Studio Part2
4
1 Introduction
1.1
Purpose
This document is intended to help anyone who has a requirement to use
Report Studio to perform some of their OLAP type reporting, yet still retain
some of the Analysis Studio type functionality. Only a couple of Analysis
Studio reporting techniques are created in this document, and it is intended
as an example of what can be done in Report Studio. This is part-2 of the
entire technique in which we will take the previously built report and make it
more dynamic by adding drill-up and drill-down functionality to the report as
well as making the “More” calculation visible only if the number of members
in the level warrant the data item to appear. If you have not already done so
it is strongly recommended that you read part-1 of this technique. An
advanced knowledge of OLAP and report design for IBM Cognos 8 is
recommended to fully understand the techniques in this document.
1.2
Applicability
This document was created using IBM Cognos 8 MR2 specifically the Great
Outdoors Company sample cube that comes with the product. At the time
the document was created all techniques were validated and supported.
2 Evaluation of the Problem
2.1
Enabling Drill-Up and Drill-Down
To enable drill-up/drill-down open the report created in part-1 and then select
“Data” from the menu, then “Drill Behaviour” (diagram 1). In the dialog box
that appears ensure that the “Allow drill-up and drill-down” checkbox is
selected (diagram 2) and press “OK”. Run the report and then drill down on
Camping Equipment. Notice the results (diagram 3). They don’t look quite
right. First, the number of visible items exceeds the value of 2 which we set
when creating the previous report. Second, the total for “More Subtotal” and
“Total” didn’t change when drilling down. Let’s examine why these things
happened.
IBM Cognos Proprietary Information
Creating Dynamic Analysis Studio
Type Reports in Report Studio Part2
Diagram 1
Diagram 2
IBM Cognos Proprietary Information
5
Creating Dynamic Analysis Studio
Type Reports in Report Studio Part2
Diagram 3
IBM Cognos Proprietary Information
6
Creating Dynamic Analysis Studio
Type Reports in Report Studio Part2
2.2
7
Breaking Down the Drill Behaviour
A couple of things happened that were unexpected when drilling down on our
previous report. First the number of visible items changed from what we
enabled in the report. We originally set the number of items to be displayed
to 2, with an overflow of 2, however when drilling down on “Camping
Equipment” we saw all 5 members at that level. To fully understand why
this happened let’s break down the calculations that were used to create that
data item.
If you examine the “Products Displayed” calculation you should see that its
syntax is:
_firstfromset([Full Members List],2,2)
[Full Members List] is another data item which is used in the _firstfromset
function. Drill-up/Drill-down should not impact this particular calculation, so
the issue must reside in the “Full Members List” data item. If you examine
the “Full Members List” data item you’ll see that its syntax is:
children([Products])
When you drill down into the “Camping Equipment” member the above
expression gets converted from children([Products]) to
children([Camping Equipment]), and this causes problems with the “Products
Displayed” data item when drilling down. The context gets lost, when drilling
down and as a result the report doesn’t look the way we would expect it to.
This behaviour is a little more visible with the “More Subtotal” and “Total”
data items don’t change in value when drilling down. The reason for this is
that like the “Products Displayed” data item the context for the calculation
doesn’t change when drilling down. For example, the “Total” data item was
created using
total(currentMeasure within set [Full Members List]). Remember
[Full Members List] at the time the report is first run, contains the members
which are the children of the “Products” root level. This is group then used in
the “More Subtotal” and “Total” data items to calculate the totals. When we
drill down the “Full Members List” data item which is actually
children([Products]), becomes static in the “Products Displayed”,
“More Subtotal”, and the “Total” calculations so that each is using the
children of “Products” instead of the children of “Camping Equipment” within
their respective calculations.
IBM Cognos Proprietary Information
Creating Dynamic Analysis Studio
Type Reports in Report Studio Part2
2.3
8
Fixing the Drill Behaviour
Now that we understand why the drill-up/drill-down worked the way it did,
we now need to fix it. To do this we need to define a set of
“Member Sets” that will help to carry the context of where we are in the cube
into each of the calculations needed in this report.
2.3.1
Step 1
Enable Member Sets in Report Studio by selecting the query from the “Query
Explorer” pane window, and then change the properties of the “Define
Member Sets” attribute to Yes (diagram 4). You should now see a new tab
when looking at the query itself (diagram 5).
Diagram 4
IBM Cognos Proprietary Information
Creating Dynamic Analysis Studio
Type Reports in Report Studio Part2
IBM Cognos Proprietary Information
9
Creating Dynamic Analysis Studio
Type Reports in Report Studio Part2
10
Diagram 5
2.3.2
Step 2
Select the “Member Sets” tab in the query view. We need to drag the data
item which is at the root of the behaviour, in our case its “Full Member List”.
Drag that “Full Member List” data item into the Member Set pane, then
change the “Name” property to something new. In this case “Full Member
List ms” was used (diagram 6), you can use any name as long as it’s different
from the actual data item. This avoids name conflicts in the report.
IBM Cognos Proprietary Information
Creating Dynamic Analysis Studio
Type Reports in Report Studio Part2
Diagram 6
IBM Cognos Proprietary Information
11
Creating Dynamic Analysis Studio
Type Reports in Report Studio Part2
2.3.3
12
Step 3
Now we drag all the data items in our report which are dependant on the
“Full Member List” data item, and place them on top of the Member Set that
we just created (diagram 7). It is critical that you ensure that the mouse
pointer is resting directly on top of the member set we created (diagram 8).
If it’s too high you will see a thin black line above the member set (diagram
9), and if it’s too low you will see a thin black line below the member set
(diagram 10). This should create a tree structure with the Member Set we
created at the root and the 3 data items dependant on the “Full Member List”
data item as its children (diagram 11).
Diagram 8
Diagram 9
Diagram 10
IBM Cognos Proprietary Information
Creating Dynamic Analysis Studio
Type Reports in Report Studio Part2
Diagram 11
2.3.4
Step 4
Next follow the steps in section 2.1 “Enabling Drill-Up and Drill-Down”, and
ensure the “Allow drill-up and drill-down” check box is enabled then select
the “Advanced tab”.
IBM Cognos Proprietary Information
13
Creating Dynamic Analysis Studio
Type Reports in Report Studio Part2
14
2.3.5
Step 5
Next select the “Full Member List” data item and change the “Drill-up
behaviour” and the “Drill-down behaviour” to both be “Replace Expression”
(diagram 12). This essentially will change the expression in every instance to
the new value after you drill-up or drill-down. For example when you first
run the report the expression is children([Products]), and when you drilldown on Camping Equipment the expression is not changing. This will force
the expression to change in all data items to be
children([Camping Equipment]).
Diagram 12
2.3.6
Step 6
Repeat Step 5 for the “Products Displayed”,” Total”, and “More Subtotal” data
items using “Preserve” instead of “Replace Expression” for the drill-up or drilldown behaviour (diagram 13). Press “OK” when done.
Diagram 13
IBM Cognos Proprietary Information
Creating Dynamic Analysis Studio
Type Reports in Report Studio Part2
2.3.7
15
Step 7
Run the report and drill down on “Camping Equipment”, notice that the “More
Subtotal” and the “Total” data items change to reflect the level you have now
drilled down into (diagram 14).
Diagram 14
IBM Cognos Proprietary Information
Creating Dynamic Analysis Studio
Type Reports in Report Studio Part2
2.4
Examining the Behaviour of More Subtotal
Run the report and drill-down on “Golf Equipment”, then drill-down on
“Putters”. Notice that the number of visible items shown is four, and the
“More Subtotal” data item is now displaying a value of 0 (diagram 15). If you
remember from part-1 we defined the “Products Displayed” member to
display two members, with an over flow of two. What this essentially does is
display two members up to four (two as the base with an overflow of two).
So in effect will see all the members in a level if there are four or less.
Anything above four will display only two.
Diagram 15
2.5
16
Making “More Subtotal” Dynamic
IBM Cognos Proprietary Information
Creating Dynamic Analysis Studio
Type Reports in Report Studio Part2
17
So now that you understand how the _firstfromset() function affects the
number of visible items how do we set the “More Subtotal” to appear only if it
is needed.
Step 1
Select the query from the “Query Explorer” tab, and insert a new data item
object from the “Insertable Objects” of the “Toolbox” tab. In the “Expression
Definiton” pane enter the following syntax _remainderset( (diagram 16).
Diagram 16
IBM Cognos Proprietary Information
Creating Dynamic Analysis Studio
Type Reports in Report Studio Part2
18
Step 2
Next from the “Data Items” tab of the “Available Components” drag the
“More Subtotal” data item to the right side of the ‘(‘ in the expression. Next
add a comma and then drag the “Full Members List” data item to the end of
the expression. End the expression by entering the following “,4)”
(diagram 17). Your full expression should look like the one below.
_remainderset([More Subtotal],[Full Members List],4)
Validate the expression then press “OK”.
Diagram 17
IBM Cognos Proprietary Information
Creating Dynamic Analysis Studio
Type Reports in Report Studio Part2
19
Step 3
The last step is to rename the data item from “Data Item 1” to “More”. Then
select “Page 1” from the “Page Explorer”. You need to replace “More
Subtotal” with the newly created “More” data item. To do this highlight
“More Subtotal” in the report and choose “Cut” from the “Edit” menu. Next
drag the “More” data item so that it is dropped between “Products Displayed”
and “Total” (diagram 18). The final piece of the puzzle is to change the
expression definition of “Products Displayed” to be “_firstfromset([Full
Members List],2,0)” instead of “_firstfromset([Full Members List],4,0)”. This
will only display four members with no overflow.
This time when running the report and drilling-down into “Golf Equipment”
and then “Putters” you’ll notice that the “More Subtotal” now disappears
instead of showing a value of 0 (diagram 19).
Diagram 18
IBM Cognos Proprietary Information
Creating Dynamic Analysis Studio
Type Reports in Report Studio Part2
Diagram 19
IBM Cognos Proprietary Information
20
Fly UP