COGNOS(R) 8 Business IntelligenceREPORT STUDIOUSER GUIDEReport Studio TourCognos(R) 8 Business Intelligence ReadmeReport Studio User GuideUSER GUIDETHE NEXT LEVEL OF PERFORMANCETM

Product Information(R)This document applies to Cognos 8 Version 8.1.2 MR2 and may also apply to subsequent releases. To check for newer versions of thisdocument, visit the Cognos Global Customer Services Web site ( (C) 2006 Cognos Incorporated.Portions of Cognos(R) software products are protected by one or more of the following U.S. Patents: 6,609,123 B1; 6,611,838 B1; 6,662,188B1; 6,728,697 B2; 6,741,982 B2; 6,763,520 B1; 6,768,995 B2; 6,782,378 B2; 6,847,973 B2; 6,907,428 B2; 6,853,375 B2; 6,986,135 B2;6,995,768 B2.Cognos and the Cognos logo are trademarks of Cognos Incorporated in the United States and/or other countries. All other names aretrademarks or registered trademarks of their respective companies.While every attempt has been made to ensure that the information in this document is accurate and complete, some typographical errors ortechnical inaccuracies may exist. Cognos does not accept responsibility for any kind of loss resulting from the use of information contained inthis document.This document shows the publication date. The information contained in this document is subject to change without notice. Anyimprovements or changes to either the product or the document will be documented in subsequent editions.U.S. Government Restricted Rights. The software and accompanying materials are provided with Restricted Rights. Use, duplication, ordisclosure by the Government is subject to the restrictions in subparagraph (C)(1)(ii) of the Rights in Technical Data and Computer Softwareclause at DFARS 252.227-7013, or subparagraphs (C) (1) and (2) of the Commercial Computer Software - Restricted Rights at48CFR52.227-19, as applicable. The Contractor is Cognos Corporation, 15 Wayside Road, Burlington, MA 01803.This software/documentation contains proprietary information of Cognos Incorporated. All rights are reserved. Reverse engineering of thissoftware is prohibited. No part of this software/documentation 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 Incorporated.

Table of ContentsIntroduction 21Chapter 1: Report Studio 23Planning Reports 23Working in Report Studio 23The User Interface 23Basic Report Structure 25Working with Objects 27Set Options 28Creating a Report 29Specify the Package 29Choose a Report Template 30Add Data to a Report 31Working with Dimensional Data 31Save a Report 33Run a Report 33Print a Report 37Creating Your Own Report Templates 37Convert a Report to a Template 38Create a Query Studio Template 38Techniques for Creating Accessible Reports 39Techniques for Creating Reports for Cognos Office Connection 39Managing Your Reports 40The Cognos 8 SDK 41Chapter 2: Types of Reports 43List Reports 43Group Data 43Set the Group Span for a Column 44Format Lists 47Crosstab Reports 47Crosstab Nodes and Crosstab Node Members 48Create a Single-Edge Crosstab Report 49Create a Nested Crosstab Report 49Specify the Default Measure 50Format Crosstabs 50Change a List into a Crosstab 52Example - Add Aggregate Data to a Crosstab Report 52Example - Create a Discontinuous Crosstab 53Charts 54Example - Create a Column Chart to Plot Order Trends 55Customize a Chart 56Customize the Colors of a Chart 58Change a Chart Background 60Add a Baseline to a Chart 61Customize the Lines in a Chart 61Add a Marker to a Chart 62Add a Note to a Chart 62Specify the Chart Label Text and Value Text Shown 65Specify the Properties of a Gauge Chart 65User Guide 3

Create a Drill-up and Drill-down Chart 66Example - Show Values in the Chart Legend 66Define Query Context When Customizing Legend Entries, Legend Titles, or AxisLabels 68Example - Customize the Axis Titles 68Example - Create a Conditional Palette 69Maps 70Example - Create a Map Report 72Example - Define Data Values for the Region Layer 73Ignore Data with no Features 73Match Data Values to Names in the Map File 74Define Data Values for the Point Layer 74Add Another Color to the Region or Point Layer 75Specify the Size of Points 75Add Titles, Subtitles, Footers, and Axis Title 75Add Legend Titles 76Add a Note to a Map 76Customize a Map 76Drill Through to Another Report From a Map 77Edit a Map 78Additional Maps 78Location Intelligence 79Repeaters 79Convert a List into a Repeater 79Example - Create Mailing Labels 80Chapter 3: Formatting a Report 81Recommendation - Laying Out a Report 81Report Layout Guidelines 82The Page Structure View 82Add a Header or Footer 82Add Borders 83Add Text 84Specify the Font 84Add Color 85Insert an Image 85Insert a Background Image 85Add a Bookmark 86Example - Add a Table of Contents to a Report 87Insert Other Objects 89Elements Supported in Rich Text Items 90Example - Add a Multimedia File to a Report 91Align an Object 91Use Tables to Control Where Objects Appear 91Apply a Table Style 92Apply Padding 92Set Margins 92Reuse a Layout Object 93Change a Reused Object 93Update Reused Objects 94Add a Page 94Create Page Sets 95Join Nested Page Sets 95Example - Preparing a Product List Report 96Reorder Columns 97Rename a Column 97Swap Columns and Rows 984Report Studio

Setting Object Properties 98Control Page Breaks and Page Numbering 99Specify Text Properties 99Specify Text Flow 100Specify the Height and Width 100Control How Objects Flow Around Other Objects 101Specify Borders and Cell Size in Tables 101Create and Modify Classes 102Modify Classes to Format Query Studio Reports 102Modifying the Default Layout Style Sheet 103Chapter 4: Working with Data 105Filter Data 105Define a Slicer 106Filtering Data Using an SAP BW Data Source 107Ignoring the Time Component in Date Columns 107Sort Data 108Perform Advanced Sorting 109Create Sections 109Remove Sections 110Add a Summary 110Setting Aggregation Properties for a Column 111Changes in the Behavior of Count Between Cognos ReportNet(R) 1.1 and Cognos 8 112Aggregating Values in Crosstabs and Charts 113Create a Calculation 114Format Data 115Specify the Format for an Object 116Specifying the Number of Decimals 116Locale-sensitive Properties 116Specify the List of Properties for a Layout Object 117Show Data for a Specific Time Period 117Adding Prompts 117Use the Build Prompt Page Tool 118Build Your Own Prompt and Prompt Page 118Create a Parameter to Produce a Prompt 122Create a Prompt Directly in a Report Page 122Modifying Prompts 123Control the Data That Appears in a Tree Prompt 127Working with Queries 127Relating Queries to Layouts 127Working with Dimensional Data Sources 128Add Queries to a Report 128Create a Union Query 129Create a Join Relationship 131Add Dimension Information to a Query 132Reference Package Items in Child Queries 134Working with SQL or MDX 134Add a Query Macro 137Chapter 5: Working with Existing Reports 141Open and Save a Report Locally 141Open a Report from the Clipboard 142Remove Upgrade Messages 142Open Files from Other Studios 142Components of an Analysis Studio Query Specification 142Managing Changes in the Package 145Update Name References 145Change the Package Connection 146User Guide 5

Add Multiple Items to a Single Column 146Example - Create a Report with Multiple Items in One Column 146Bursting Reports 148Defining Burst Recipients 148Specify Burst Groups 150Set Burst Options 150Enable Bursting 151Creating Burst Reports Using a Dimensional Data Source 152Example - Burst a Report 152Defining Conditions 153Add a Variable 153Adding Conditional Formatting to a Report 155Add Conditional Rendering 158Drill-through Access 160Understanding Drill-through Concepts 160Set Up Drill-through Access in a Report 164Create a Drill-up/Drill-down Report 167Member Sets 169Example - Create a Drill-up/Drill-down Report 170Create a Master-Detail Relationship 170Link Members from Two Dimensional Data Sources 171Chapter 6: Try It Yourself Exercises 173Try It Yourself - Create a List Report 173Try It Yourself - Create a Crosstab 174Try It Yourself - Create Charts 175Try It Yourself - Create Map Reports 184Show the Distribution of Revenue by Country 185Show Revenue and Margins for Cities in the United States 186Try It Yourself - Add Prompts 187Try It Yourself - Create a Multiple-Page Report 188Try It Yourself - Create a Template 190Try It Yourself - Create an Invoice 191Try It Yourself - Create a Dashboard Report 193Try It Yourself - Create a Dynamic Report That Shows Period-to-date Data 195Try It Yourself - Create a Report with Drill-through Access to Itself 196Appendix A: Troubleshooting 201A Web Server Error When Trying to Browse Images 201Values Not Recognized in Multilingual Query Items 201Changes in the Model Are Not Reflected in the Report If Model Versioning Is Used 202Problems When Printing a PDF Manual 202A Running Total in Grouped Reports Gives Unexpected Results 202The java.lang.OutOfMemory Error Message Appears in Cognos Connection or ReportStudio 203Report Studio Does Not Start 203A Report is Not Rendered Properly 203Unable to Delete a Prompt 203Appendix B: Samples 205Sample Reports in the GO Sales Package 205Conditional Display 205Custom Legend 205Orders Report 205Product Report 205Retailer Report (Multiple Prompt Values) 205Returns by Order Method - Prompted Chart 205Revenue by Sales Territory 2056Report Studio

Top 5 Sales Staff 205Top Revenue (Conditional) 205Sample Reports in the GO Sales and Retailers Package 206Actual Sales Against Target Sales 206Actual Sales Against Target Sales - Burst 206Banded Report 206Basket Analysis with Total Contribution (Top 10 Rank) 206Business Details Drill Through 206Consumer Trends 206Cost of Goods 206Custom Grouping 206Customer Invoice 206Global Sales 206Global Sales (1) 206GO Business View 207GO Media 207Mailing Labels 207Margin Cost and Volume Report 207Multi-Grain Fact 207Multiple Charts 207Order Analysis 207Percent Contribution by Country 207Product Comparison Charts 207Product Line by Year 207Product Line by Year-Prompt 207Product Revenue 207Product Revenue - Lifetime/Q2 208Product Summary 208Products Ranked by Revenue 208Quantity by Retailer 208Regional Orders 208Report with Totals 208Retailer Contact 208Retailer Contact (Multiple Prompt Values) 208Returns by Order Method 208Revenue by Date and Amount 208Sales Representative Contact List 208Sales Reps Performance 208Union Crosstab 209Waterfall Chart 209Sample Reports in the GO Data Warehouse Package 209Current Assets 209Current Liabilities 209Employee Profile 209Sales Target by Region 209Tool Tips 209Sample Reports in the Great Outdoors Company Package 209Margins and Revenue Map for United States 209Product Cost by Years 209Profit and Revenue Combination Chart 209Revenue by Product Line 209Revenue by Sales Branch 210Revenue by Year over Year Growth 210Revenue Chart by Sales Territory and Year 210Revenue for Specified Order Method and Sales Territory 210Sales Territory Map 210Slicers Filters 210User Guide 7

Appendix C: Chart Types 211Choosing a Chart Type and Configuration 212Pie Charts 212Column Charts 213Progressive Column Charts 213Pareto Charts 214Bar Charts 214Line Charts 215Area Charts 215Combination Charts 216Radar Charts 216Scatter Charts 217Bubble Charts 217Point Charts 218Quadrant Charts 218Polar Charts 219Metrics Range Charts 219Gauge Charts 220Chart Configurations 220Standard Charts 221Stacked Charts 221100% Stacked Charts 2223-D Charts 222Appendix D: Using the Expression Editor 223Creating Expressions Using SAP BW Data Sources 224Browse the Data of a Data Item 224Using Quotation Marks in Literal Strings 224Recommendation - Use Member Unique Name (MUN) Aliases 225Functions Not Available When Creating a Report or Layout Expression 225Operators 225Summaries 231Member Summaries 241Constants 243Constructs 244Business Date/Time Functions 245Block Functions 249Macro Functions 249Common Functions 254DB2 267Informix 278MS Access 282Oracle 288Red Brick 295SQL Server 299Teradata 304SAP BW 309Sybase 310Report functions 315Appendix E: Producing Reports in Microsoft Excel Format 323Microsoft Excel Limitations 323Unable to Load Images from the Cognos 8 Content Store in a Report 323A Blank Worksheet is Opened 323A Warning Message Appears When Excel Opens a Cognos 8 Report 323Using Reports Saved in XLS Format 323Loading Excel Reports in Netscape 7.01 Is Not Supported 323Nested Labels in Charts Are Not Supported 3238Report Studio

Data Series Are Truncated 324Charts and Custom Colors 324Repeating Pie Charts 324Discrete Axis Label Skip Control in Charts 324Formatting Limitations 324Overline Text Format 327Text Strings with More Than 255 Characters 327Reports with More Than 256 Columns 327Table and Column Width 327Excel Formats and Secure Socket Layer (SSL) 327Number Format Becomes Currency Format in Japanese Excel 327Cognos 8 Limitations 328Accessing Reports on a Remote Server 328Drill-through Reports 328Map Reports 328Formats Not Supported for Reports in Excel Format 328Hyperlink Buttons 328Emailing Reports in Excel Format 328Charting Support in Excel and Cognos 8 328Appendix F: Report Studio Object and Property Reference 333Report Studio Objects 3333-D Area 3333-D Bar 3333-D Combination Chart 3333-D Line 3343-D Scatter Chart 334Angular Axis 334Angular Measure 334Area 334As of Time Expression 334Axis Title 335Bar 335Baseline 335Baseline 335Baseline 335Baseline 335Block 336Bookmark 336Bubble Chart 336Bubble Measure 336Calculated Member 336Caption 336Chart Body 337Chart Footer 337Chart Node Member 337Chart Subtitle 337Chart Text Item 337Chart Title 337Class 338Combination Chart 338Component Override 338Conditional Block 338Conditional Block 338Crosstab 338Crosstab Columns 339Crosstab Columns 339User Guide 9

Crosstab Corner 339Crosstab Fact Cells 339Crosstab Intersection 340Crosstab Member Fact Cells 340Crosstab Node Member 340Crosstab