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

Dimension Security in SSAS

A nice video on how to implement dimension security in SSAS

http://channel9.msdn.com/posts/Philo589/Dimension-Security-in-SQL-Server-Analysis-Services/

An excellent tool to troubleshoot whether Kerberos authentication is properly configured or not

If you are facing problems while configuring kerberos delegation in your environment then use the below link to troubleshoot the problems.

http://blogs.iis.net/brian-murphy-booth/archive/2007/03/09/delegconfig-delegation-configuration-reporting-tool.aspx

About me

I am a BI Consultant and have worked on various Microsoft tools which includes SSIS, SSAS, SSRS, PPS,MOSS,Proclarity and excel reporting. I have a passion for Microsoft Technologies. Microsoft Community contributor 2011 - MCITP and MCTS in SQL server 2008. I like playing basket ball and watching sports like cricket, football and formula 1. I also love to read books and listening music.