With some calculated members in MDX it only makes sense to see the calculation if a certain Hierarchy is used.
For example:
Aggregate(YTD([Date].[Calendar].CurrentMember),[Internet Sales Amount])
This calculation only works in the Date.Calendar Hierarchy. I wanted to show the end users a message informing them about this and hide the calculation at the same time.
Here is how I did this:
Case when ([Date].[Calendar].level is [Date].[Calendar].[Calendar Year] or
[Date].[Calendar].level is [Date].[Calendar].[Calendar Semester] or
[Date].[Calendar].level is [Date].[Calendar].[Calendar Quarter] or
[Date].[Calendar].level is [Date].[Calendar].[Month]) then
aggregate(ytd([Date].[Calendar].currentmember),[Internet Sales Amount])
else “Use Date Hierarchy”
End
Now the user will see the message “Use Date Hierarchy” if they are not at a level in the correct Hierarchy. The only downside to this is the grand total shows the message instead of the total.
If you can figure out a way to show the grand total when the user is in the correct hierarchy, let me know.
MikeDavisSQL 
Hi Mike,
I used a scope statement when I want to hide a measure. This sounds similar to your situation……
SCOPE({[Measures].[Baseline Amt], [Measures].[Goal Amt]});
SCOPE(DESCENDANTS([Product].[Product Tree].CURRENTMEMBER, [Product].[Product Tree].[Product Division], SELF_AND_AFTER));
THIS = NULL;
END SCOPE;
SCOPE(DESCENDANTS([Product].[Product Line Product Tree].CURRENTMEMBER, [Product].[Product Line Product Tree].[Product Line], SELF_AND_AFTER));
THIS = NULL;
END SCOPE;
END SCOPE;
SCOPE({[Measures].[L30 Days Invoice Sales]});
SCOPE( [Transaction Date].[Transaction Date YQMD].[Calendar Year]);
THIS = NULL;
END SCOPE;
SCOPE( [Transaction Date].[Transaction Date YQMD].[Calendar Quarter]);
THIS = NULL;
END SCOPE;
SCOPE( [Transaction Date].[Transaction Date YQMD].[Calendar Month]);
THIS = NULL;
END SCOPE;
END SCOPE;
I would much prefer to use scope statements due to the perfromance hit with “case when”. But how do you scope to all of the cube except one hierarchy? There is no anti-scope function that I know of.And how do you get the total to still show. There is no way that I know of except to write a scope statement that covers every hierarchy and attrbute in the cube except the one you want it to show in, and that would be a huge scope statment.
Peg: I have the exact same issue. I’m not so hot with MDX. Do you think you could provide an example of how that would look with just a simple hiding of a measure based on a user name? For example:
SCOPE([Measures].[Employee Hours %])
IF (USERNAME = ‘OLSON\OlsonProjectManagers’)
THEN [Measures].[Employee Hours %] = NULL
END IF
END SCOPE
I tried this and the cube fails to process.
Any help would be greatly appreciated!!
This still isn’t exactly what you want, but you could create a measure that is zero, then set the measure in the scope statement. But, Grand Total Still comes out Zero. :(
CREATE MEMBER CURRENTCUBE.[MEASURES].[TESTING]
AS 0,
VISIBLE = 1 ;
SCOPE(DESCENDANTS([Date].[YQMD].CURRENTMEMBER, [Date].[YQMD].[Year], SELF_AND_AFTER));
[Measures].[TESTING] = 10;
END SCOPE;