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”
End

image

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.

image

If you can figure out a way to show the grand total when the user is in the correct hierarchy, let me know.

Advertisements
This entry was posted in mdx, SSAS, Syndication and tagged , . Bookmark the permalink.

4 Responses to How to hide Calculated Members in MDX SSAS

  1. Peg says:

    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;

    • MikeDavisSQL says:

      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.

    • Anthony says:

      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!!

  2. Peg says:

    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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s