Reporting on ‘% of base’ in Cognos10

February 11, 2013 | By | Add a Comment

A workaround for the lack of ‘% of base’ functionality in Business Insight Advanced

A common requirement is to be able to product % of total or ‘% of portfolio’ reports, for comparative analysis. Unfortunately, this capability is currently missing in Business Insight Advanced. A workaround exists, which is to use the ‘% of’ calculation provided in Business Insight Advanced to hard-code the total value. However this introduces excessive report maintenance implications.

An alternative approach is to use Analysis Studio, which does support % of base, to ‘author’ the basic report layout required, then finish the report in Business Insight Advanced. (Note, however, that it is currently not possible to open an Analysis Studio view in Business Insight Advanced – it is necessary to open the view in Report Studio first, save to Cognos Connection, from where it can then be opened in Business Insight Advanced)

Example

In the following example, we are looking to create a sectioned report, showing % of total for each column in the report. Starting in Analysis Studio, we have a crosstab like this:
pct_Base_1_edited
Since Analysis Studio does not report ‘Section’ functionality (as is available in Query Studio, Report Studio and BIA), this will have to be added later.

The first step in Analysis Studio will be to add the desired measure as a nested level within the crosstab view (this will simplify creation of the % calculations later):
pct_Base_2_edited
Next, the ‘% of total’ calculation will be added:
pct_Base_3_edited
Next, an additional dimension will be added, which will form the rows of each section in the final report:
pct_Base_4_edited
This view can now opened directly into Report Studio, or saved to Cognos Connection:
pct_Base_5_edited
From Report Studio, the report can now be saved back to Cognos Connection (under a new name), which will finally enable it to be opened in Business Insight Advanced:
pct_Base_8
(Note: it is hoped that Cognos will improve the inter-Studio workflow in future versions, to enable Analysis Studio view to be opened directly into Business Insight Advanced, either from within Analysis Studio or from Cognos Connection)

This view can now opened directly into Report Studio, or saved to Cognos Connection:
pct_Base_10b_edited
Note the automatic creation of subtotals for rows and columns:
pct_Base_11_edited
In the context of report sections, this is entirely unnecessary. However, currently within BIA there is no easy way to add/remove nested subtotals. After manually cutting out each of the superfluous totals, and sectioning the report (using the Section/Unsection button), we end up with the required report layout:
pct_Base_12b_edited
Summary
It had been hoped that Cognos 10 would introduce some level of Studio ‘rationalization’ in order to simplify the workflow for Business Analysts in particular. Unfortunately, the introduction of Business Insight Advanced (BIA) and Business Insight has thus far only added 2 new Studios without rendering any of the existing Studios redundant. It is to be hoped that in future versions, it will be less necessary to jump around several Cognos interfaces in order to construct what are relatively simply reports.

Footnote: It turns out this is trivially easy to do in Tableau (around 9 mouse clicks) and, ironically, in PowerPlay Client (5 mouse clicks).

Filed in: Business Intelligence, Cognos, Tableau

Patrick Spedding

About the Author (Author Profile)

Patrick Spedding is Senior Director of BI R&D for Rocket Software, and IBM Champion for IBM Collaboration Solutions. He is also a Non-Executive Director on the Board of Eastside Radio in Sydney, Australia. Prior roles include Director of Product Management for IBM Cognos, Director of Field Marketing for Cognos, Founder of Tableau partner See-Change Solutions, and SAS Solution Manager for BI and Strategy Management. Patrick's qualifications include an MBA degree in Marketing (AIU), Diploma in Management (University of Michigan), BSc (Hons) in Mathematics (Loughborough University, UK), Fellow of the Australian Institute of Management (FAIM), and member of the Australian Institute of Company Directors (AICD). Find Patrick on Google+

Leave a Reply

Trackback URL | RSS Feed for This Entry