Sunday, January 8, 2012

SCOPE statement on a calculated measure for multiple hierarchies


I have a calculated measure called Total Amount in Adventure works sample and now based on 2 different time hierarchies I need to show two different amounts.
Say for Calendar hierarchy my total amount should be displayed as Internet Sales amount and for Fiscal Hierarchy the total amount should be equal to Reseller Sales Amount.

Here is my script for the same

CALCULATE;

CREATE MEMBER CURRENTCUBE.[Measures].[TotalAmount] AS
NULL,
FORMAT_STRING = "#,##0.00;-#,##0.00",
VISIBLE = 1  ;

SCOPE([Order Date].[Time Calendar].members,[Measures].[TotalAmount]);
THIS= [Measures].[Sales Amount - Fact Internet Sales];
END SCOPE;

SCOPE([Order Date].[Time Fiscal].members,[Measures].[TotalAmount]);
THIS = [Measures].[Sales Amount];
END SCOPE;

Everything goes well , I process the cube and I browse my cube















Now When I browse my cube for Calendar year hierarchy the total amount instead of showing as Internet Sales Amount it shows reseller sales amount.

Now I Interchange my Scope Statement

SCOPE([Order Date].[Time Fiscal].members,[Measures].[TotalAmount]);
THIS = [Measures].[Sales Amount];
END SCOPE;


SCOPE([Order Date].[Time Calendar].members,[Measures].[TotalAmount]);
THIS= [Measures].[Sales Amount - Fact Internet Sales];
END SCOPE;

This also does not work. when I drag my Fiscal Year hierarchy, instead of showing Reseller Sales Amount, It shows the Internet sales amount.















So only the second Scope is always evaluated and not the first one. This is because the "All" member in the dimension which is present in both the hierarchies is always evaluated first and only the second scope is evaluated.

So to overcome this we need to explicitly define the All member of one hierarchy in both the scope statements and to exclude the All member of the current hierarchy from the scope

SCOPE(Descendants([Dim Product].[Product Hierarchy],,After),[Dim Product].[Brand Hierarchy].DefaultMember,[Measures].[TotalAmount]);
This=[Measures].[Sales Amount - Fact SKU];
END SCOPE;

SCOPE(Descendants([Dim Product].[Brand Hierarchy],,After),[Dim Product].[Product Hierarchy].DefaultMember,[Measures].[TotalAmount]);
THIS=[Measures].[Fact SKU Count];
END SCOPE;

So I process the cube and everything works fine.
















HTH,
Ram