Book Image

IBM Cognos 10 Report Studio Cookbook, Second Edition - Second Edition

Book Image

IBM Cognos 10 Report Studio Cookbook, Second Edition - Second Edition

Overview of this book

IBM Cognos Report Studio is widely used for creating and managing business reports in medium to large companies. It is simple enough for any business analyst, power user, or developer to pick up and start developing basic reports. However, this book is designed to take the reader beyond the basics and into the world of creating more sophisticated, functional business reports.IBM Cognos 10 Report Studio Cookbook, Second Edition helps you understand and use all the features provided by Report Studio to generate impressive deliverables. It will take you from being a beginner to a professional report author. It bridges the gap between basic training provided by manuals or trainers and the practical techniques learned over years of practice.Written in a recipe style, this book offers step-by-step instructions for IBM Cognos Report Studio users to author reports effectively, allowing a reader to dip in and out of the chapters as they desire. You will see a new fictional business case in each recipe that will relate to a real-life problem and then you will learn how to crack it in Report Studio. This book covers all the basic and advanced features of Report Authoring. It introduces the fundamental features useful across any level of reporting. Then it ascends to advanced techniques and tricks to overcome Studio limitations. Develop excellent reports using dimensional data sources by following best practices that development work requires in Report Studio. You will also learn about editing the report outside the Studio by directly editing the XML specifications. You will discover how to build and use Cognos Active Reports, a new addition in IBM Cognos 10. Provide richness to the user interface by adding JavaScript and HTML tags and using the different chart types introduced in IBM Cognos 10. The main focus is on the practical use of various powerful features that Report Studio has to offer to suit your business requirements. Learn numerous techniques and hacks that will allow you to make the best out of your IBM Cognos 10 Report Studio.
Table of Contents (21 chapters)
IBM Cognos 10 Report Studio Cookbook Second Edition
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Preface
Index

Aggregation and rollup aggregation


Business owners want to see the unit cost of every product. They also want the entries to be grouped by product line and see the highest unit cost for each product line. At the end of the report, they want to see the average unit cost for the whole range.

Getting ready

Create a simple list report with Products / Product line, Products/Product, and Sales fact / Unit cost as columns.

How to do it...

In this recipe, we want to examine how to aggregate the data and what is meant by rollup aggregation. Using the new report that you have created, this is how we are going to start this recipe:

  1. We will start by examining the Unit cost column. Click on this column and check the Aggregate Function property.

  2. Set this property to Average.

  3. Add grouping for Product line and Product by selecting those columns and then clicking on the GROUP button from the toolbar.

  4. Click on the Unit cost column and then click on the Summarize button from the toolbar. Select the Total option from the list.

  5. Now, again click on the Summarize button and choose the Average option as shown in the following screenshot:

  6. The previous step will create footers as shown in the following screenshot:

  7. Now delete the line with the <Average (Unit cost)> measure from Product line. Similarly, delete the line with the <Unit cost> measure from Summary. The report should look like the following screenshot:

  8. Click on the Unit cost column and change its Rollup Aggregate Function property to Maximum.

  9. Run the report to test it.

How it works...

In this recipe, we have seen two properties of the data items related to aggregation of the values.

The aggregation property

We first examined the aggregation property of unit cost and ensured that it was set to average. Remember that the unit cost here comes from the sales table. The grain of this table is sales entries or orders. This means there will be many entries for each product and their unit cost will repeat.

We want to show only one entry for each product and the unit cost needs to be rolled up correctly. The aggregation property determines what value is shown for unit cost when calculated at product level. If it is set to Total, it will wrongly add up the unit costs for each sales entry. Hence, we are setting it to Average. It can be set to Minimum or Maximum depending on business requirements.

The rollup aggregation property

In order to show the maximum unit cost for product type, we create an aggregate type of footer in step 4 and set the Rollup Aggregation to Maximum in step 8.

Note

Here we could have directly selected Maximum from the Summarize drop-down toolbox. But that creates a new data item called Maximum (Unit Cost). Instead, we ask Cognos to aggregate the number in the footer and drive the type by rollup aggregation property. This will reduce one data item in query subject and native SQL.

Multiple aggregation

We also need to show the overall average at the bottom. For this we have to create a new data item. Hence, we select unit cost and create an Average type of aggregation in step 5. This calculates the Average (Unit Cost) and places it on the product line and in the overall footer.

We then deleted the aggregations that are not required in step 7.

There's more...

The rollup aggregation of any item is important only when you create the aggregation of Aggregate type. When it is set to automatic, Cognos will decide the function based on the data type, which is not preferred.

It is good practice to always set the aggregation and rollup aggregation to a meaningful function rather than leaving them as automatic.