Saturday, January 5, 2013

COGNOS Report with Dynamic Columns


This is the first blog post I am writing in year 2013. I want to be little more active with respect to blog writing this year and my goal is to write at least 20 posts in 2013. Primarily my target is to write articles related to COGNOS, Informatica, Microsoft Business Intelligence, SharePoint 2013 and Data Analytics.

The first one I am going to start with COGNOS where I am going to showcase the use of macros and conditional statements in COGNOS Report Studio. The requirement is to develop a report which should display the columns dynamically based on the options selected. For example a report has three options to run the report.

     Option A – Show Quantity Detail
     Option B – Show Revenue Detail
     Option C – Show Sales Detail

The report should show the below mentioned columns based on selected option

Option A – Year, Order Method (dynamic column) and Quantity (dynamic column)
Option B – Year, Product Line (dynamic column) and Revenue (dynamic column)
Option C – Year, Product Description (dynamic column) and Total Sales Amount (dynamic column)



















The simplest but the dirty way to achieve this would be having three list objects with in the same report and hide two of them based on prompt selection.

This could be done dynamically without having the need of three separate list objects…we just need to write a macro and Query calculation columns where we can write the conditional statements to make the columns dynamic.

Here are the steps to make this working

a) Drag a List object in the report page

b) Place the “Year” data item in the first column on the List object. I am using sample “Sales Query” package.

c) Now create a Query Calculation column as a next column in the report. This column will be used to display the second column (Order Method, Product Line or Product Description) based on the selected prompt. The macro expression should look something like below

#prompt('Report Type','token','[Sales (query)].[Order method].[Order method type]')#

Please note that we are creating a new parameter called “Report Type” which we will use in the Value Prompt as a parameter

d) Create another Query Calculation column for the measures (third column). We need to write the Case expression and assign the column accordingly. The expression should be something like below 
     
      case
when(?Report Type?='[Sales (query)].[Order method].[Order method type]') then ([Sales (query)].[Sales fact].[Quantity])
when(?Report Type?='[Sales (query)].[Products].[Product line]') then ([Sales (query)].[Sales fact].[Planned revenue])
else
([Sales (query)].[Sales fact].[Product cost])
End


e) Now we need to create a prompt page…go to prompt page and drag and drop the “Value Prompt “in the prompt page. Change the “Select UI” property of value prompt to the “Radio Button Group”


f) Assign the values Option A, Option B and Option C in the “Static Choices” option in the Value Prompt.

Option A - [Sales (query)].[Order method].[Order method type]
Option B -   [Sales (query)].[Products].[Product line]
Option C -   [Sales (query)].[Products].[Product Description]

That’s all, run the report and you should be good to go. If we select “Option B”, we should see the Year, Product Line and Revenue columns in the report.