Pages

Monday, December 1, 2014

SSAS Scope function examples

Define scope to a base measure.
SCOPE ([Measures].[Meetings]);
    this = ([Measures].[Meetings], [Contact Detail].[Event Type].&[Meeting]);
END SCOPE;

Creating a calculated member which use mutilple measures for different hierarchies.
Following calculated member can be used to define measures for different hierarchies. When [Target] measure is selected against  [Territory].[Territory] hierarchy,  it returns [Measures].[Territory Target].Otherwise it returns [Measures].[Area Target].

CREATE MEMBER CURRENTCUBE.[Measures].[Target]
    AS [Measures].[Area Target],
FORMAT_STRING = "#,#0",
NON_EMPTY_BEHAVIOR = [Measures].[Area Target],
VISIBLE = 1;
SCOPE ([Measures].[Target], [Territory].[Territory].[Territory].members);
    this = [Measures].[Territory Target];
    NON_EMPTY_BEHAVIOR(this) = [Measures].[Territory Target];
END SCOPE;

Create sub scope statementIn following calculated member, measure is limited to Efient products. (i.e defined in the sub scope statement). it returns null for other products.
CREATE MEMBER CURRENTCUBE.[Measures].[Stg - Retail Targets]
    AS NULL,
FORMAT_STRING = "#,#0",
VISIBLE = 1;
SCOPE ([Measures].[Stg - Retail Targets]);
    SCOPE( [Product].[Product].[EFIENT]);
    this = ([Measures].[Area Target] - [Measures].[Stg - SCM Hospital Targets]);
    END SCOPE;
END SCOPE;