book

IBM Cognos 10 Report Studio Cookbook - Second Edition

Name: IBM Cognos 10 Report Studio Cookbook - Second Edition
ISBN: 9781849688208

by Ahmed S Lashin, Abhishek Sanghani

August 2013

Intermediate to advanced

364 pages

7h 35m

English

Packt Publishing

Read now

Unlock full access

IBM Cognos 10 Report Studio Cookbook - Second Edition
Table of Contents
IBM Cognos 10 Report Studio Cookbook - Second Edition
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Support files, eBooks, discount offers and moreWhy Subscribe?Free Access for Packt account holdersInstant Updates on New Packt Books
Preface
What this book covers
What you need for this book
Who this book is for

Conventions
Reader feedback
Customer support
ErrataPiracyQuestions
1. Report Authoring Basic Concepts
Introduction
Summary filters and detail filters
Getting readyHow to do it...How it works...The detail filterThe summary filterThere's more...Before/after aggregationSee also
Sorting grouped values
Getting readyHow to do it...How it works...There's more...
Aggregation and rollup aggregation
Getting readyHow to do it...How it works...The aggregation propertyThe rollup aggregation propertyMultiple aggregationThere's more...
Implementing if-then-else in filters
Getting readyHow to do it...How it works...There's more...
Formatting data – dates, numbers, and percentages
Getting readyHow to do it...How it works...Date formatNumerical formatThe % marginThere's more...
Creating sections
Getting readyHow to do it...How it works...There's more...See also
Hiding columns in crosstabs
Getting readyHow to do it...How it works...There's more...
Prompts – display value versus use value
Getting readyHow to do it...How it works...There's more...
2. Advanced Report Authoring
Introduction
Adding cascaded prompts
Getting readyHow to do it...How it works...Cascaded sourceThere's more...Auto-SubmitSee also
Creating a nested report – defining the master-detail relationship
Getting readyHow to do it...How it works...The master-detail relationshipThere's more...See also
Writing back to the database
Getting readyHow to do it...How it works...There's more...
Adding conditional formatting
Getting readyHow to do it...How it works...The String variableThe Boolean variableThe report language variableThere's more...The style variable propertyThe text source variable property
Formatting negative values
Getting readyHow to do it...How it works...Running DifferenceShowing negative values in red and brackets
Playing with conditional styles
Getting readyHow to do it...How it works...There's more...
Using conditional blocks – many reports in one
Getting readyHow to do it...How it works...There's more...
Defining drill-through from crosstab intersection
Getting readyHow to do it...How it works...
Overriding crosstab intersection drill-through definitions
Getting readyHow to do it...How it works...There's more...
3. Using JavaScript Files – Tips and Tricks
Introduction
Defining dynamic default values for prompts
Getting readyHow to do it...How it works...SpanGetElementsByTagNameSelectedIndexCanSubmitPromptThere's more...
Changing the title of the value prompt
Getting readyHow to do it...How it works...There's more...
Validating textbox prompts
Getting readyHow to do it...How it works...
Showing/hiding prompt controls at runtime
Getting readyHow to do it...How it works...There's more...
Selecting and submitting values automatically
Getting readyHow to do it...How it works...There's more...
Manipulating the Date Time control
Getting readyHow to do it...How it works...There's more...
Creating a variable width bar chart using JavaScript
Getting readyHow to do it...How it works...There's more...
4. The Report Page – Tips and Tricks
Introduction
Showing images dynamically (traffic light report)
Getting readyHow to do it...How it works...There's more...
Handling the missing image issue
Getting readyHow to do it...How it works...There's more...
Dynamic links to an external website (a Google Maps example)
Getting readyHow to do it...How it works...There's more...
Alternating drill links
Getting readyHow to do it...How it works...Render VariableThere's more...
Showing tooltips on reports
Getting readyHow to do it...How it works...There's more...See also
Merged cells in Excel output
Getting readyHow to do it...How it works...
Worksheet name in Excel output
Getting readyHow to do it...How it works...There's more...
Conditional column titles
Getting readyHow to do it...How it works...
5. Working with XML
Introduction
Changing drill targets quickly
Getting readyHow to do it...How it works...There's more...
Copying and pasting drill links
Getting readyHow to do it...How it works...There's more...
Removing references to old packages or items
Getting readyHow to do it...How it works...
A hidden gem in XML – row level formatting
Getting readyHow to do it...How it works...There's more...See also
6. Writing Printable Reports
Introduction
Controlling the container size and rows per page
Getting readyHow to do it...How it works...There's more...
Eliminating orphan rows
Getting readyHow to do it...How it works...There's more...
Defining page orientation and size (and overriding them for one page)
Getting readyHow to do it...How it works...
Avoiding page title repetition
Getting readyHow to do it...How it works...There's more...
Horizontal pagination
Getting readyHow to do it...How it works...There's more...
Page numbering with horizontal pagination
Getting readyHow to do it...How it works...
Choosing the output format from a prompt
Getting readyHow to do it...How it works...There's more...
Choosing the right colors
Getting readyHow to do it...How it works...There's more...
Defining page sets
Getting readyHow to do it...How it works...
Cautions about HTML items and JavaScript files
Getting readyHow to do it...How it works...
Displaying the report name in a footer
Getting readyHow to do it...How it works...
7. Working with Dimensional Models
Introduction
List report or crosstab report
Getting readyHow to do it...How it works...
Filtering rows or columns
Getting readyHow to do it...How it works...There's more...
Filtering a whole report
Getting readyHow to do it...How it works...There's more...
Adding a prompt into Slicer and its limitations
Getting readyHow to do it...How it works...There's more...More Info
Achieving zero suppression
Getting readyHow to do it...How it works...There's more...
Aggregation versus preaggregated values
Getting readyHow to do it...How it works...There's more...
The roleValue() function
Getting readyHow to do it...How it works...There's more...
Swapping dimensions using MUN manipulation
Getting readyHow to do it...How it works...See also
8. Working with Macros
Introduction
Adding data-level security using the CSVIdentityMap macro
Getting readyHow to do it...How it works...There's more...
Using the Prompt macro in native SQL
Getting readyHow to do it...How it works...There's more...
Making prompts optional
Getting readyHow to do it...How it works...Optional_defaultValuePure_OptionalThere's more...
Adding a token using macros
Getting readyHow to do it...How it works...
Using the prompt() and promptmany() macros in query subjects
Getting readyHow to do it...How it works...
Showing the prompt values in a report based on security
Getting readyHow to do it...How it works...
String operations to get it right
Getting readyHow to do it...How it works...There's more...
Showing a username in the footer
Getting readyHow to do it...How it works...There's more...
9. Using Report Studio Efficiently
Introduction
Using Report Studio's environmental options
Getting readyHow to do it...How it works...View | Visual AidsStructure | Create Crosstab NodesTools | Options | View | Reuse IBM Cognos Viewer windowTools | Options | ReportThere's more...
Copying and pasting partial or full reports
Getting readyHow to do it...How it works...
Upgrading report specifications
Getting readyHow to do it...How it works...
Setting the execution timeout
Getting readyHow to do it...How it works...There's more...
Setting the maximum row limit
Getting readyHow to do it...How it works...
Handling slow report validation
Getting readyHow to do it...How it works...There's more...
Capturing a query
Getting readyHow to do it...How it works...There's more…Why make filters mandatory?Query formattingCapturing the query for the database
Browsing values from the data expression window
Getting readyHow to do it...How it works...There's more...
Viewing the page structure
Getting readyHow to do it...How it works...
Picking up and applying styles
Getting readyHow to do it...How it works...
Using the "grab them all" practice
Getting readyHow to do it...How it works...See also
Using Default Data Formats
Getting readyHow to do it...How it works...
10. Working with Active Reports
Introduction
Building tabbed reports
Getting readyHow to do it...How it works...There's more...
Working with Decks
Getting readyHow to do it...How it works...There's more...
Working with the Data Deck
Getting readyHow to do it...How it works...
Filtering data using Data Check Box Group
Getting readyHow to do it...How it works...There's more...
11. Charts and New Chart Features
Introduction
Chart formatting options
Getting readyHow to do it...How it works...Chart Palette PresetsBackground Effects PresetsThere's more...
Converting a chart to another chart type
Getting readyHow to do it...How it works...
Working with pie charts
Getting readyHow to do it...How it works...The Summarize Small Slices propertyThe First Slice Angle propertyThe Exploded Slices property
Getting started with bullet charts
Getting readyHow to do it...How it works...There's more...
Getting started with scatter charts
Getting readyHow to do it...How it works...
12. More Useful Recipes
Introduction
Timing report execution
Getting readyHow to do it...How it works...The report viewSchedulingThere's more...
Missing values versus zero values
Getting readyHow to do it...How it works...There's more...
Overriding data formatting using patterns
Getting readyHow to do it...How it works...
Setting up conditional drill-throughs
Getting readyHow to do it...How it works...There's more...
Dynamically switching between reports using an iFrame
Getting readyHow to do it...How it works...There's more...
Freezing column titles
Getting readyHow to do it...How it works...There's more...
13. Best Practices
Introduction
Reducing the number of query items
Getting readyHow to do it...How it works...Alias member unique nameDelete unreferenced query objectsDragging items from the Data Items pane
Highlighting hidden items
Getting readyHow to do it...How it works...
Using relative paths for images
Getting readyHow to do it...How it works...There's more...
Controlling JavaScript file execution
Getting readyHow to do it...How it works...
Customizing classes for report-wide effect
Getting readyHow to do it...How it works...
Creating templates
Getting readyHow to do it...How it works...
Regression testing
Getting readyHow to do it...How it works...There's more...
Commenting the code
Getting readyHow to do it...How it works...
Naming data containers (lists, crosstabs, and charts) for use in Cognos Workspace
Getting readyHow to do it...How it works...There's more...
Enabling a larger worksheet size for Excel 2007
Getting readyHow to do it...How it works...There's more...
A. Recommendations and References
Introduction
Version controlling
Recommendations for prompt types
Cognos Mashup Service
SDK and third-party tools for Cognos
IBM Cognos Analysis For Excel (CAFE)
IBM Cognos for Microsoft Office
IBM Cognos Workspace
IBM Cognos Workspace Advanced
IBM Cognos Cloud
Index

Content preview from IBM Cognos 10 Report Studio Cookbook - Second Edition

Using the Prompt macro in native SQL

In this recipe, we will write an SQL statement to be fired on the data source. We will use the Prompt macro to dynamically change the filter condition.

We will write a report that shows a list of employees by region and country. We will use the Prompt macro to ask the users to enter a country name. Then, the SQL statement will search for the employee belonging to that country.

Getting ready

Create a new blank list report with the GO Data Warehouse (query) package.

How to do it...

In this recipe we will see how to use macros to pass a parameter to an SQL statement, as follows:

Go to Query Explorer and drag an SQL object onto the query subject that is linked to the list (Query1 in most cases):
Select the SQL object ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781849688208Other

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills