Search code examples
ssasolap

SSAS hide measure for certain dimension

How to hide measure or a scope if user choose certain dimension or go to certain dimension hierarchy level?

Thanks.

Solution

  • Try something like this in your MDX script:

    FREEZE([Measures].[My Measure], [Product].[Subcategory].[All]);
([Measures].[My Measure], [Product].[Product].Members, [Product].[Subcategory].[Subcategory].Members) = null;

    Freeze ensures the next statement won't null out the category level totals. The next statement bulls out that measure for the whole product dimension up to the subcategory totals but not above.

    Note this is fine for nulling out meaningless numbers but isn't a security feature. A savvy user could do a drillthrough command to get the product level numbers. Or a savvy user could connect in a special way and clear the whole MDX script for his session so he sees the detailed product data.

    For a more secure approach:

    1. If you can null out product data for all measures then setup role based security. In dimension data security only grant access to member Subcategory.All only but uncheck visual totals on the advanced tab so that the subcategory grand total is the real total.
    2. Or setup a second slimmed down Product dimension that only has the top levels not the detailed product levels. Then only tie that dimension to this measure group.
    3. Or create a second measure group that does a group by in SQL and joins to the Product dimension only at the Category level. Thus there is no detailed data only rollups. Then with security control whether a used sees the detailed measures or the summary measures.