COGNOiSe.com - The IBM Cognos Community

Hi All,

So I Have a Cube

Dimension
Time: Year > Preiod
Account: Account Type > Account Number
Customer: Customer Type >  Customer

Measures:
Outstanding Value
Invoice Age

So imagine the underlying data is like this.

Year    Period   Account Type   Account Number   Cutomer Type    Customer      Value   Invoice Age(Days)
2014   10                 NORTH             N1                         GOOD                   Bill             100            5
2014   10                  SOUTH             S1                          BAD                    Jill              2500           50


I want to create a RS report that give me this:

Account Type   Invoice Age 1-7 Days   Invoice Age 30+ Days
NORTH                       100   
SOUTH                                                       2500

where the first column can be replaced with any other relevant dimension such as: -

Customer Type   Invoice Age 1-7 Days   Invoice Age 30+ Days
GOOD                            100   
BAD                                                                 2500


I have tried IF(Days<7) THEN (Value) ELSE 0 but it doesn't like this.


Can anyone please point me in the right direction in terms of what MDX expression i should be using or if i should be even doing it this way?

Thanks,

AM

OK so I have had some minor progress with this. 
By creating a new data item that says

CASE WHEN Days < 7 then '0-7 Days' ELSE '7+ Days' END

However this only works when you drill down to the lowest level of the cube.

Any help would be greatly appreciated :)

Thanks,

Andy