CODEWITHSUNDEEP
scopeexpressionssasmdx
CoS
CoS

Reputation: 35

SSAS: calculations in the cube

I need to divide my calculations into three groups: - which applied to base cells only (leaves) - which applied to all cells - which applied to consolidating cells only (cells of upper hierarchy levels) For example, I have hierarchy: Year-Quarter-Month. Base cells of this hierarchy in the "Month" and consolidating cells in a "Year" and "Quarter".

For base cells I can write this "scope":

SCOPE (
   [Measures].[Value] ,
   [DimProducts].[Name].[Donut] , 
   [DimTime].[Month].CHILDREN       // month only
      );
THIS = [Measures].[Value]*2+100500;
END SCOPE;

And I don't know how to write the "scope" for consolidating cells? I can't to create a "scope" for each hierarchy level such as:

SCOPE (
   [Measures].[Value] ,
   [DimProducts].[Name].[Donut] , 
   [DimTime].[Quarter].CHILDREN    // quarter only
      );
...
END SCOPE;
SCOPE (
   [Measures].[Value] ,
   [DimProducts].[Name].[Donut] , 
   [DimTime].[Year].CHILDREN       // year only
      );
...
END SCOPE;

Because there are many dimensions and hierarchy levels and calculations must be applied to all of them. Maybe there is a simply way for my problem?

Upvotes: 1

Views: 6740

Answers (1)

gonsalu
gonsalu

Reputation: 3184

You can use MDX functions in SCOPE's subcube expression.

Supposing your Year-Quarter-Month user hierarchy has Year, Quarter and Month levels, you can use the following expression to address levels above the Month level.

SCOPE ( [Measures].[Value]
      , [DimProducts].[Name].[Donut]
      , Descendants( [DimTime].[Year-Quarter-Month].[All]
                   , [DimTime].[Year-Quarter-Month].[Quarter]
                   , SELF_AND_BEFORE
                   )
      );
...
END SCOPE;

Reference:

Upvotes: 2

Related Questions