Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Save more on your purchases! discount-offer-chevron-icon
Savings automatically calculated. No voucher code required.
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletter Hub
Free Learning
Arrow right icon
timer SALE ENDS IN
0 Days
:
00 Hours
:
00 Minutes
:
00 Seconds
IBM Cognos 8 Report Studio Cookbook
IBM Cognos 8 Report Studio Cookbook

IBM Cognos 8 Report Studio Cookbook: Over 80 great recipes for taking control of Cognos 8 Report Studio

Arrow left icon
Profile Icon Abhishek Sanghani
Arrow right icon
€18.99 per month
Full star icon Full star icon Full star icon Empty star icon Empty star icon 3 (3 Ratings)
Paperback Jun 2010 272 pages 1st Edition
eBook
€8.99 €37.99
Paperback
€46.99
Subscription
Free Trial
Renews at €18.99p/m
Arrow left icon
Profile Icon Abhishek Sanghani
Arrow right icon
€18.99 per month
Full star icon Full star icon Full star icon Empty star icon Empty star icon 3 (3 Ratings)
Paperback Jun 2010 272 pages 1st Edition
eBook
€8.99 €37.99
Paperback
€46.99
Subscription
Free Trial
Renews at €18.99p/m
eBook
€8.99 €37.99
Paperback
€46.99
Subscription
Free Trial
Renews at €18.99p/m

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing
Table of content icon View table of contents Preview book icon Preview Book

IBM Cognos 8 Report Studio Cookbook

Chapter 2. Advanced Report Authoring

In this chapter, we will cover the following:

  • Adding cascaded prompts

  • Nested reports: defining master detail queries

  • Writing back to the database

  • Conditional formatting

  • Show negative numbers in red and brackets

  • New conditional styling with version 8.3 onwards

  • Conditional block—many reports in one

  • Drill link from crosstab intersections

  • Overriding crosstab intersection expression and drill

Introduction


Now as you have implemented the recipes in Chapter 1, or read them through, I am confident that we are on the same page about fundamental techniques of report authoring.

You now know how filtering, sorting, and aggregations work. You also know how to apply data formatting, create sections, and hide columns. You are also now aware of how to add new prompts and select appropriate options in the prompt wizard.

Based on this understanding, we will now move on to some advanced topics; including cascaded prompts, nested reports, and conditional blocks. We will also examine some techniques around drill-through links. These will enable you to create professional reports as required in current industrial environments.

Adding a cascaded prompt


Business owners want to see sales made by employees. They also want the facility to limit the report to certain region, country, or employee.

When they select a region, they would like the country pick-list to automatically reduce to the countries falling in that region. Similarly, the employee pick-list should also reduce when they pick a country.

Getting ready

Create a simple list report with Employee name (from Employee by region query subject) and Quantity (from Sales fact).

Define appropriate grouping and sorting for Employee name and ensure that aggregations for Quantity are correctly set.

How to do it...

  1. We will start by creating detailed filters on the report query. Select the list report and open the filters dialog by clicking the Filters button.

  2. Add three detailed filters as follows:

    1. [Employee name]=?Employee?

    2. [Sales (query)].[Employee by region].[Country]=?Country?

    3. [Sales (query)].[Employee by region].[Region]=?Region?

  3. Define all filters as Optional.

  4. Now create...

Creating nested report: Defining master detail relationship


Users want to see product lines, products, and corresponding unit costs. For every product, they also want to see the trend of sales over last year.

We need to produce a list report with required information and nest a line chart within it to show the sales trend.

Getting ready

Create a simple list report based on the Sales (query) namespace. Pull Product | Product line, Product | Product name and Sales fact | Unit cost in the list.

How to do it...

  1. We already have a list report that shows the product lines, products, and corresponding unit costs. Please make sure that appropriate sorting and aggregations are applied to the columns.

  2. Now we will add a nested Chart object to show the sales trend for each product.

  3. Drag a new Chart object from the Insertable Objects pane onto the report as a column.

  4. Choose an appropriate chart type. In this recipe, we will choose "Line with Markers".

  5. From Source pane, drag Quantity from Sales fact onto the chart...

Writing back to the database


This is perhaps the most frequently requested functionality by business users—writing some notes or comments back to database, for a particular entry on the report. Though there is no direct functionality provided in Cognos Report Studio for this, it is still possible to achieve it by putting together multiple tools. This recipe will show you how to do that.

The business wants to see sales figures by products. They want to then write some comments for the products from the same interface. The comments need to be stored in database for future retrieval and updating.

You would need access on the backend database and Framework Manager for this recipe.

Tip

As we are only concentrating on Report Studio in this book, we will not cover the Framework Manager options in depth. The power users and Report Studio developers need not be masters in Framework Modelling, but they are expected to have sufficient knowledge of how it works. There is often a Framework Manager Specialist...

Adding conditional formatting


The business wants to see company sales figure by years and quarters. They want to highlight the entries where sales are below 5,000,000.

We will assume that database provides us the 'Quarter number' and we need to convert that to words. We will use conditional formatting for that. Also, where sales is below 5 million, the cell will be shown in red using another conditional variable.

Getting ready

Create a simple list report with Current year and Current quarter columns from the Sales | Time Dimension query subject.

Drag Quantity from Sales Fact.

Group by current year and sort by current quarter.

How to do it...

  1. Go to Condition Explorer and click on Variables.

  2. Drag a new string variable from Insertable Objects pane. Define the expression as: [Query1].[Current quarter].

  3. Change the name of variable to Convert_to_words.

  4. Add four values for the variable; the numbers 1 to 4.

  5. Now add a Boolean variable and define it as: [Query1].[Quantity]<5000000.

  6. Call this variable as Show_Red...

Running difference: Negative values in brackets and red


Business owners need to see the sales figures by months and their month-on-month difference.

If the difference is negative (fall in sales) then it needs to be shown in red and values need to be in brackets.

Getting ready

Create a simple list report with Time Dimension | Current year, Time Dimension | Current month, and Sales fact | Quantity as columns.

Group Current year and sort Current month ascending.

How to do it...

  1. Add a new query calculation to the list. Define the expression as:

    running-difference([Quantity])
    

    Call this item as "Running Difference".

  2. Open the Data Format properties for this calculation from Property list.

  3. Set the Format type as Number and the Negative Sign Symbol as brackets ().

  4. Now go to Condition Explorer and create a new condition variable of Boolean type. Define the condition as:

    [Query1].[Running Difference] < 0
    
  5. Call the variable as Show_Red.

  6. Now go back to report page and select the Running Difference column...

New conditional styling with v8.3 onwards


In this recipe, you will learn about the new conditional styles property introduced in Report Studio which, in my humble opinion, is the best feature added to make a report author's life less tedious.

Assume that the following report needs to be formatted such that quantities below 1.7M will be highlighted with red background and those above 2M should be green. Also, we need the negative values for Running Difference (month-on-month) to be shown in red and in a bracket.

As shown in previous recipe, this would have needed us to define two conditional variables. Then attach each to the corresponding column as 'Style variable' to define the styles. With one more such numeric column, the author had to define one more variable and repeat the exercise. Let's see how the new conditional styling feature solves this problem.

Getting ready

Write a new report similar to the one shown in the previous screenshot. Use Report Studio version 8.3 or later.

How to do it...

Conditional block: Many reports in one


The purpose of this recipe is to introduce you to a very useful and powerful control of Report Studio called Conditional block.

Users want a report on sales figures. They want the facility to split the numbers by product lines, periods, or retailer region, any one at a time. For convenience purposes, they don't want three different reports, instead they are looking for one report with the facility to choose between the report types.

Getting ready

Create a report with three list objects. Define the list columns as follows:

  • List 1: Product | Product lines and Sales fact | Quantity.

  • List 2: Time dimension | Current year, Time dimension | Current month and Sales fact | Quantity.

  • List 3: Retailer site | Region and Sales fact | Quantity.

Define appropriate grouping, sorting, and aggregation for all the list objects. Make sure that all objects use different queries.

How to do it...

  1. We will start by creating a prompt for report type. Go to Page Explorer and add a...

Drill-through from crosstab intersection


We have a crosstab report that shows sales quantity by month and order method. We need to create drill-through links from months and sales values.

Getting ready

Create two target reports for the drill-throughs. One takes only Month as parameter. The other takes Month and Order Method (Henceforth referred to as Drill-1 and Drill-2 reports respectively).

Create a simple crosstab report to be used as main report. Pull Time dimension | Month on rows, Order method | Order method on columns, and Sales fact | Quantity as measure.

How to do it...

  1. Select the Month item placed on crosstab rows. Click on the drill-through definition button from the toolbar.
  2. This opens the drill-through definitions dialog. Create a new definition. Select Drill-1 as Target Report. Map the month parameter with the month data item.

  3. Now click on the unlock button from toolbar to unlock the items.
  4. Select the text item from crosstab intersection. Hit the drill-through button again.

  5. Create...

Overriding crosstab intersection drill-through definition


Let us consider an extension of the last recipe. Let us say the users want to see a Discontinuous crosstab as main report. Instead of just Order methods on column, we need to display Order methods and Product lines as columns.

The rows display Month. Measure is sales quantity.

The drill-through from the intersection has to go to the appropriate report depending on whether the column is Product line or Order method.

Getting ready

Create a new drill-through target that accepts Month and Product line as parameters. We will call it as Drill-3 from now on.

For the main report, we will use the same crosstab report as in the last recipe.

How to do it...

  1. We will start by creating the discontinuous crosstab on the main report. We already have Order method on columns. Drag Product line also on the crosstab, as column. The report will look like this.

  2. Now select the intersection cells under the product line column.

  3. From its Properties, set Define contents...

Left arrow icon Right arrow icon

Key benefits

  • Learn advanced techniques to produce real-life reports that meet business demands
  • Tricks and hacks for speedy and effortless report development and to overcome tool-based limitations
  • Peek into the best practices used in industry and discern ways to work like a pro
  • Part of Packt's Cookbook series-each recipe is a carefully organized sequence of instructions to complete the task as efficiently as possible

Description

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, when it comes to developing more sophisticated, fully functional business reports for wider audiences, report authors will need guidance. This book 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. This book covers all the basic and advanced features of Report Authoring. It begins by bringing readers on the same platform and introducing 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. Provide richness to the user interface by adding JavaScript and HTML tags. The main focus is on the practical use of various powerful features that Report Studio has to offer to suit your business requirements.

Who is this book for?

If you are a Business Intelligence or MIS Developer (programmer) working on Cognos Report Studio who wants to author impressive reports by putting to use what this tool has to offer, this book is for you. You could also be a Business Analyst or Power User who authors his own reports and wants to look beyond the conventional features of Report Studio 8. This book assumes that you can do basic authoring, are aware of the Cognos architecture, and are familiar with Studio.

What you will learn

  • Learn basic and advanced options around sorting, filtering, and aggregation of data
  • Explore the powerful features of Report Studio—Conditional Formatting, Cascaded Prompts, Master-Detailed queries, and more
  • Achieve real business demands such as dynamic drill-though links, showing tooltips, minimum column width, prompt manipulation, and merged cells
  • Learn to edit reports outside Report Studio using XML editing
  • Develop sophisticated printer friendly reports
  • Overcome tool limitations such as row level formatting, timing report execution, handling missing images, and so on
  • Learn tips on report development time
  • Introduce best practices such as inserting comments, regression testing, and version controlling in your reports
  • Enhance the features of Report Studio by using macros
  • Work with dimensional models (DMRs and Cubes)

Product Details

Country selected
Publication date, Length, Edition, Language, ISBN-13
Publication date : Jun 01, 2010
Length: 272 pages
Edition : 1st
Language : English
ISBN-13 : 9781849680349
Vendor :
IBM
Category :

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing

Product Details

Publication date : Jun 01, 2010
Length: 272 pages
Edition : 1st
Language : English
ISBN-13 : 9781849680349
Vendor :
IBM
Category :

Packt Subscriptions

See our plans and pricing
Modal Close icon
€18.99 billed monthly
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Simple pricing, no contract
€189.99 billed annually
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just €5 each
Feature tick icon Exclusive print discounts
€264.99 billed in 18 months
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just €5 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total 121.97
IBM Rational Team Concert 2 Essentials
€45.99
IBM Cognos 8 Report Studio Cookbook
€46.99
IBM Cognos 10 Framework Manager
€28.99
Total 121.97 Stars icon
Banner background image

Table of Contents

11 Chapters
Report Authoring Basic Concepts Chevron down icon Chevron up icon
Advanced Report Authoring Chevron down icon Chevron up icon
Tips and Tricks: JavaScripts Chevron down icon Chevron up icon
Tips and Tricks: Report Page Chevron down icon Chevron up icon
XML Editing Chevron down icon Chevron up icon
Writing Printable Reports Chevron down icon Chevron up icon
Working with Dimensional Models Chevron down icon Chevron up icon
Macros Chevron down icon Chevron up icon
Using Report Studio Better Chevron down icon Chevron up icon
Some More Useful Recipes Chevron down icon Chevron up icon
Best Practices Chevron down icon Chevron up icon

Customer reviews

Rating distribution
Full star icon Full star icon Full star icon Empty star icon Empty star icon 3
(3 Ratings)
5 star 33.3%
4 star 0%
3 star 33.3%
2 star 0%
1 star 33.3%
Duane A. Rochelle Jul 02, 2010
Full star icon Full star icon Full star icon Full star icon Full star icon 5
I would definitely recommend this book to anyone looking for ideas to overcome some of many challenges encountered while trying to learn Cognos Report Studio.I work for a company which has recently made a decision to use the Cognos software platform for its performance reporting and data analysis activities. This encouraged me to attended training classes and read numerous technical books\articles on using Report Studio.For the last few months, I have been struggling to master some techniques needed to produce exceptional business reports using Report Studio. Most of the technical guides that I have read contained long drawn-out explanations that were difficult to understand.Within 1 week after purchasing the "IBM Cognos 8 Report Studio Cookbook", I was able to use 3 techniques to overcome some challenges I was having in developing some reports.The author of this book describes his techniques in a simple but effective form using "Getting Ready", "How to do it...", "How it works..." and "There's more...". This makes it easy for readers to easily interpret how to apply the technique being described. I really like the section describing "How it works..." because this is usually not included in most technical guides.This is an excellent book and well worth the price.
Amazon Verified review Amazon
Vic Oct 06, 2010
Full star icon Full star icon Full star icon Empty star icon Empty star icon 3
There are a number of helpful hints and tips here. Some are dead on the money. However, trying to correlate his partial explanations of his examples, to actually applying it to closely related real world issues, is a no go. On one of the pages, he refers you back to IBM's documentation, that has no explanation of the code, and worse, he uses newer code and refers back to old code that won't work on other versions.I'm surprised they didn't edit the book for grammatical errors. Someone else posted on it. For the price, its really sad.
Amazon Verified review Amazon
rvfizher995 Jul 22, 2015
Full star icon Empty star icon Empty star icon Empty star icon Empty star icon 1
Pure Garbage !! Never buy it please !!
Amazon Verified review Amazon
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

What is included in a Packt subscription? Chevron down icon Chevron up icon

A subscription provides you with full access to view all Packt and licnesed content online, this includes exclusive access to Early Access titles. Depending on the tier chosen you can also earn credits and discounts to use for owning content

How can I cancel my subscription? Chevron down icon Chevron up icon

To cancel your subscription with us simply go to the account page - found in the top right of the page or at https://subscription.packtpub.com/my-account/subscription - From here you will see the ‘cancel subscription’ button in the grey box with your subscription information in.

What are credits? Chevron down icon Chevron up icon

Credits can be earned from reading 40 section of any title within the payment cycle - a month starting from the day of subscription payment. You also earn a Credit every month if you subscribe to our annual or 18 month plans. Credits can be used to buy books DRM free, the same way that you would pay for a book. Your credits can be found in the subscription homepage - subscription.packtpub.com - clicking on ‘the my’ library dropdown and selecting ‘credits’.

What happens if an Early Access Course is cancelled? Chevron down icon Chevron up icon

Projects are rarely cancelled, but sometimes it's unavoidable. If an Early Access course is cancelled or excessively delayed, you can exchange your purchase for another course. For further details, please contact us here.

Where can I send feedback about an Early Access title? Chevron down icon Chevron up icon

If you have any feedback about the product you're reading, or Early Access in general, then please fill out a contact form here and we'll make sure the feedback gets to the right team. 

Can I download the code files for Early Access titles? Chevron down icon Chevron up icon

We try to ensure that all books in Early Access have code available to use, download, and fork on GitHub. This helps us be more agile in the development of the book, and helps keep the often changing code base of new versions and new technologies as up to date as possible. Unfortunately, however, there will be rare cases when it is not possible for us to have downloadable code samples available until publication.

When we publish the book, the code files will also be available to download from the Packt website.

How accurate is the publication date? Chevron down icon Chevron up icon

The publication date is as accurate as we can be at any point in the project. Unfortunately, delays can happen. Often those delays are out of our control, such as changes to the technology code base or delays in the tech release. We do our best to give you an accurate estimate of the publication date at any given time, and as more chapters are delivered, the more accurate the delivery date will become.

How will I know when new chapters are ready? Chevron down icon Chevron up icon

We'll let you know every time there has been an update to a course that you've bought in Early Access. You'll get an email to let you know there has been a new chapter, or a change to a previous chapter. The new chapters are automatically added to your account, so you can also check back there any time you're ready and download or read them online.

I am a Packt subscriber, do I get Early Access? Chevron down icon Chevron up icon

Yes, all Early Access content is fully available through your subscription. You will need to have a paid for or active trial subscription in order to access all titles.

How is Early Access delivered? Chevron down icon Chevron up icon

Early Access is currently only available as a PDF or through our online reader. As we make changes or add new chapters, the files in your Packt account will be updated so you can download them again or view them online immediately.

How do I buy Early Access content? Chevron down icon Chevron up icon

Early Access is a way of us getting our content to you quicker, but the method of buying the Early Access course is still the same. Just find the course you want to buy, go through the check-out steps, and you’ll get a confirmation email from us with information and a link to the relevant Early Access courses.

What is Early Access? Chevron down icon Chevron up icon

Keeping up to date with the latest technology is difficult; new versions, new frameworks, new techniques. This feature gives you a head-start to our content, as it's being created. With Early Access you'll receive each chapter as it's written, and get regular updates throughout the product's development, as well as the final course as soon as it's ready.We created Early Access as a means of giving you the information you need, as soon as it's available. As we go through the process of developing a course, 99% of it can be ready but we can't publish until that last 1% falls in to place. Early Access helps to unlock the potential of our content early, to help you start your learning when you need it most. You not only get access to every chapter as it's delivered, edited, and updated, but you'll also get the finalized, DRM-free product to download in any format you want when it's published. As a member of Packt, you'll also be eligible for our exclusive offers, including a free course every day, and discounts on new and popular titles.