Saturday, June 15, 2013

How to make conditional indicator in crosstab node member area

Please see attached screenshot below

It is requested to make indicator based on revenue of 2006:

if ([Revenue 2006]>1000000000) then ('Up') else ('Down')
It looks like very simple, as we can use simple conditional URL variable to handle it based on revenue 2006.  However, it won’t work as Cognos doesn’t support such combination in this nested node with measures.  You need to use a separated query to figure out URL value and then join with main query to get this report. Please download report specification (version10.1) for reference if needed.

A few cases are listed to explore this issue, there is no any problem to use conditional style variable for cases below, with single node.

with nested members from same hierarchy,

However, We can even get revenue 2006 at product line node member, conditional variable cannot be used

To get indicator for crosstab with masure, detail implementation is listed below,

  1. Basic Query 1
  2. Query 2 to get indicator

Query Result 

  1. Join query 1 and query 2 with product line

  1. Assign url variable to image

No comments:

Post a Comment