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
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.
