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

Framework manager question

Started by saumil287, 18 Apr 2012 11:32:41 PM

Previous topic - Next topic

saumil287

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




MFGF

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.
Meep!

saumil287

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

cognostechie

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)

saumil287

Hi CognosTechie

Thank you very much for your reply.

Appreciated
a lot
Thanks again