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

Calculate measure from different dimension hierarchies

Started by manias, 01 Nov 2016 08:36:40 AM

Previous topic - Next topic

manias

Hi Guru's, i have a difficult report, which i can not calculate the overall measure.
I hope someone show me the way how i can resolve my problem

i have 3 measures from different fact tables (without conformed dimension), and two dimesnions in column, for each measure i have own set of members with which I have to calculate the measure.
-----------------------------------------------------------------------
crosstab1:
[measure1] in rows, and [dim1] in columns, where:

[measure1] is - total(measure1 within set [set1],[set2],[set3]...)
[dim1] is - filter(dim1; [dim1].[name] = 'abc')
-----------------------------------------------------------------------
crosstab2:
[measure2] in rows, and [dim2] in columns, where:

[measure2] is - total(measure2 within set [set3],[set4],[set5]...)
[dim2] is - filter(dim2; [dim2].[name] = 'bcd')
-----------------------------------------------------------------------
crosstab3:
[measure3] in rows, and [dim2] in columns, where:

[measure3] is - total(measure3 within set [set5],[set6],[set7]...)
[dim2] is - filter(dim2; [dim2].[name] = 'efg')
-----------------------------------------------------------------------
I tried to realize report by create only one crosstab, but each measure calculate by her own set of members in dimesion.
Now i need to create calculete measure:
measure4 = measure1 - measure2 - measure3

Measure 1 caluclated on her own dimesnion
Measure 2 and Measure 3 calculated on a common dimension with the different member sets for each measure.

Now i create 3 different crosstab and different query for each crosstab and tried to create union between them, but i think it is wrong way.

help with advice for choosing the path of resolving the question

Thx, Egor