creating_drillable_all_items_list_with_top_n_chart.pdf

Tip or Technique
Creating Drillable Lists and Charts
Displaying All or Top N Members
Product(s): Cognos 8 Report Studio
Area of Interest: Report Design
Creating Drillable Lists and Charts Displaying All or Top N Members
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] .
Cognos Proprietary Information
Creating Drillable Lists and Charts Displaying All or Top N Members
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
PURPOSE .............................................................................................................. 4
APPLICABILITY ....................................................................................................... 4
2
CREATE THE REPORT .................................................................................... 4
2.1
2.2
2.3
2.4
2.5
2.6
STEP 1 ................................................................................................................ 4
STEP 2 ................................................................................................................ 5
STEP 3 ................................................................................................................ 5
STEP 4 ................................................................................................................ 7
STEP 5 ................................................................................................................ 7
STEP 6 ................................................................................................................ 8
3
MAKING THE REPORT DRILLABLE .............................................................. 10
3.1
3.2
3.3
3.4
STEP 1 .............................................................................................................. 10
STEP 2 .............................................................................................................. 10
STEP 3 .............................................................................................................. 11
STEP 4 .............................................................................................................. 13
Cognos Proprietary Information
Creating Drillable Lists and Charts Displaying All or Top N Members
4
1 Introduction
1.1
Purpose
This document will demonstrate how to make use of Member Sets to create
drillable reports that contain a list object which will display all the members in
the set, and a chart which will display only the top N members.
1.2
Applicability
This document should work fine on any platform of Cognos 8 Report Studio.
The datasource for the creation of this document is the sample cube (Great
Outdoors Company.mdc)
2 Create the Report
2.1
Step 1
Create a list report which contains the member you wish to perform the
topcount on. It is important to ensure you put the member into the list and
not the level (diagram 1). The example below selects Sales Territory.
Diagram 1
Cognos Proprietary Information
Creating Drillable Lists and Charts Displaying All or Top N Members
2.2
5
Step 2
Change the expression of the member just added to find the children of the
member. For example if you used Sales Territory then the original expression
would look like [great_outdoors_company].[Sales Territory].[Sales
Territory].[Sales Territory]->:[PC].[@MEMBER].[Sales Territory]. Change this
expression to look like the following (diagram 2):
children([great_outdoors_company].[Sales Territory].[Sales Territory].[Sales
Territory]->:[PC].[@MEMBER].[Sales Territory])
Diagram 2
2.3
Step 3
Drag the same member you did in step 1 onto the list again. Is this case a
new data item called Sales Territory1 is created. Rename the data item to
“Top Count” (diagram 3).
Cognos Proprietary Information
Creating Drillable Lists and Charts Displaying All or Top N Members
Next drag a measure, in this case revenue onto the list report (diagram 4)
Diagram 4
Cognos Proprietary Information
6
Creating Drillable Lists and Charts Displaying All or Top N Members
2.4
7
Step 4
Edit the Top Count data item to create a top count against the unfiltered item
against the measure you are interested in. For example (diagram 4)
topCount([Sales Territory],3,tuple([Revenue])).
Diagram 4.
The function topCount requires 3 parameters. The first is the set against
which you wish to apply the top count. In this case the set is the children of
the Sales Territory member. The next parameter is the index or the number
of member you wish to return for the topCount. In this case the function will
return the top 3 members. The last parameter is the numeric value for which
the calculation is basing its top count on. In this case a simple evaluation of
the total revenue for each Sales Territory is what is being used.
2.5
Step 5
The next step is to cut the Top Count data item out of the list report. Do not
delete the data item as we will need it later in this document. Next insert a
chart object and change the query for the chart to be Query 1, the same
query which the list is based off of (diagram 5)
Diagram 5
Cognos Proprietary Information
Creating Drillable Lists and Charts Displaying All or Top N Members
2.6
Step 6
Insert Revenue into the measures, and Top Count into the row axis. Your
Report should now look like diagram 6. When run, the results should look
like diagram 7.
Diagram 6
Cognos Proprietary Information
8
Creating Drillable Lists and Charts Displaying All or Top N Members
Diagram 7
Cognos Proprietary Information
9
Creating Drillable Lists and Charts Displaying All or Top N Members
10
3 Making the report Drillable
3.1
Step 1
The next step is to enable drill through for the report. From the Data menu
select Drill Behaviour, and ensure the “Allow drill-up and drill-down” checkbox
is set under “Report drill capabilities” (diagram 8).
Diagram 8
3.2
Step 2
Next select the Advanced tab and change the Drill-up and Drill-down
behaviour for “Top Count” to Preserve (diagram 9), and set “Sales Territory”
to Replace Expression (diagram 10).
Diagram 9
Cognos Proprietary Information
Creating Drillable Lists and Charts Displaying All or Top N Members
Diagram 10
3.3
Step 3
The last thing to do is to create the member set that will control the drill
behaviour we just created. Go to the Query Properties, change the setting
“Define Member Sets” to “Yes” (diagram 11).
Cognos Proprietary Information
11
Creating Drillable Lists and Charts Displaying All or Top N Members
12
Diagram 11
You should see a new tab in the query window which says “Member Sets”.
Select this tab and create a new member set by dragging Sales Territory into
the window to the right. Next drag Top Count on top of the Sales Territory
member set to create a parent child relationship (diagram 12).
Diagram 12
Cognos Proprietary Information
Creating Drillable Lists and Charts Displaying All or Top N Members
13
The last thing to do is rename the Sales Territory member set to read “Sales
Territory set” (Diagram 13).
Diagram 13
3.4
Step 4
Run the report and drill up or down on the chart. The calculation you just
created should continue to show the top 3 members regardless of which level
you are at.
Cognos Proprietary Information
Similar pages