There are 2 measures columns TOTAL and DIVISOR within a raw data set and I
want a calculated measure with 2 requirements:

1. IF DIVISOR > 0 THEN TOTAL / DIVISOR ELSE TOTAL
2. Numeric Operation ( / ) only happens after rollup.

It seems impossible to achieve through Cognos Transformer. However, in
Excel, Pivot Table let you nicely have these 2 conditions
as following example


Data set

DATE NAME TOTAL DIVISOR
01/01/2004 A 1 0
01/01/2004 B 10 100
01/01/2004 B 20 100
01/01/2004 C 5 0
01/01/2004 D 6 10
01/01/2004 E 30 0
01/01/2004 D 4 10

EXCEL Pivot
Sum of METRIC NAME A B C D E
DATE
01/01/2004 1 0.15 5 0.5 30



This issue was way out of my initial expectation and my capacity of Cognos.

I found a solution to this issue either by inserting new columns for
name'A', 'B', 'C' with TOTAL/DIVISOR for 'B', 'D' and TOTAL for others.
Another way to do it is crosstab the data set with NAME as header and TOTAL
and DIVISOR are values (2 data sources).


"Dang" <mdang (AT) bigpond (DOT) com.au> wrote