If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

2 measures in 1 column

Started by colt, 10 Jan 2014 07:53:03 AM

Previous topic - Next topic

colt

I have a cube with multiple dimensions. Some Measures are tied to corresponding dimensions.
Lets say cube has Dim1 and Dim2 as dimensions M1 is measure which has only values for Dim1 members and M2 is measure having only values vor Dim2,
B3 is another measure which has only values for Dim 1; B4 is another measure which has only values for Dim2; T1 is member of Time Dimension, T2 is T1 + 1 year.
This way it is no Problem to get crosstab in the following way:

       
           | T1                              |
           | M1  |   M2  | B3  |  B4  |
___________________________
Dim1   |  V11|         | V13 |        |
Dim2   |        |  V22 |        | V24 |



V11 and V22 are the values. It is ensured that V12 and V21 are always empty.

Now what I want to get is:

         |           T1            |
         |      M   |      B      |
_____________________
Dim1 |   V11   |    V13     |
Dim2 |   V22   |    V24     |


The Problem is: When I simply make a calculation M1 + M2 , then result is empty because of NULL values.
When I try to include coalesce, Report runs forever.

The real Report Looks much more complex and alredy includs 6 crosstabs from 6 different queries (different companies).
That's the reason why I have not yet tried to solve it by a Union of 2 different queries.
My Goal would be to find a Definition for column M that says:

M= If Dim1 THEN M1 ELSE M2  .

Any help apreciated.