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