Archive for the ‘Calculated Member’ Category

How to hide Calculated Members in MDX SSAS



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”


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.