COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Framework Manager => Topic started by: saumil287 on 18 Apr 2012 11:32:41 PM

Title: Framework manager question
Post by: saumil287 on 18 Apr 2012 11:32:41 PM
Hi All,
I am having QS containing QItems as follows:

BU
Cust_Name
Product
Location
Year-Month
Revenue

Year-Month contains values as 201102, 201101, 201002,201001 as char data type

Instead of displaying this values in rows,I want to display the same in columns.

It is possible using RS pivot but I also need to calculate Month_LY_Var which is subtraction of (201102-201002)

Is this possible in Framework manager.

Your guidance is appreciated

Thanks in Advance



Title: Re: Framework manager question
Post by: MFGF on 19 Apr 2012 06:40:55 AM
The easiest solution is probably to model your data as DMR, then you can locate the measure values for the specific date members and subtract them easily.

Regards,

MF.
Title: Re: Framework manager question
Post by: saumil287 on 19 Apr 2012 06:47:16 AM
Hi MFGF,

Thanks for your reply.
Can you give me some startup guidance .
Since I am not having any unique columns nor primary /for keys.
How should I create heirarchies and dim.

Thanks again
Title: Re: Framework manager question
Post by: cognostechie on 20 Apr 2012 03:48:26 AM
So you are taking the approach of putting the Year-Month in columns only because you want to subtract two months ?

If that is the case, you don't need to create it as columns and you can do this in the report instead of doing it in FM

1> Create a Data Item with the calculation -

     If ( Year-Month = '201102' ) then (Measure) else (0)

2> Create another Data Item as -

     If ( Year-Month = '201002' ) then (Measure) else (0)

3> Create another Data Item as [Data Item1] - [Data Item2]

This will give you the difference.

If you want to make it dynamic then you can use the extract , cast and substring functions to do it.

Ex:

If (
     [Year-Month] =

     cast(extract(year,current_date),varchar(4)) + cast(extract(month,current_date),varchar(2))
   )
then ([Measure] )
else (0)
Title: Re: Framework manager question
Post by: saumil287 on 20 Apr 2012 06:33:10 AM
Hi CognosTechie

Thank you very much for your reply.

Appreciated
a lot
Thanks again