WITH
MEMBER [Measures].[MemberName] AS
([Branch].[Business Hierarchy].CurrentMember.MEMBER_NAME)
MEMBER [Measures].[MemberUniqueName] AS
([Branch].[Business Hierarchy].CurrentMember.MEMBER_UNIQUE_NAME)
--Measures use in the report graph
MEMBER [Measures].[MTD_Actual] AS
SUM([Date Calculation].[Aggregation].&[MTD], [Measures].[Actual])
MEMBER [Measures].[MTD_Target] AS
SUM([Date Calculation].[Aggregation].&[MTD], [Measures].[Target])
SELECT NON EMPTY
{ [Measures].[Target]
, [Measures].[Actual]
, [Measures].[MTD_Target]
, [Measures].[MTD_Actual]
, [Measures].[MemberName]
, [Measures].[MemberUniqueName]
} ON COLUMNS
, NON EMPTY
--Added filter to remove non trading branches
{ FILTER([Branch].[Business Hierarchy].[Branch], [Measures].[Actual] <> 0 )
* [Date].[Date Hierarchy].[Date]
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT STRTOMEMBER(@parmDate).Parent.FirstChild:STRTOMEMBER(@parmDate) ON COLUMNS
FROM ( SELECT STRTOSET(@parmBranch) ON COLUMNS
FROM [SampleCube]
)
)
WHERE ([Branch].[Is Main Office].&[No]
MEMBER [Measures].[MemberName] AS
([Branch].[Business Hierarchy].CurrentMember.MEMBER_NAME)
MEMBER [Measures].[MemberUniqueName] AS
([Branch].[Business Hierarchy].CurrentMember.MEMBER_UNIQUE_NAME)
--Measures use in the report graph
MEMBER [Measures].[MTD_Actual] AS
SUM([Date Calculation].[Aggregation].&[MTD], [Measures].[Actual])
MEMBER [Measures].[MTD_Target] AS
SUM([Date Calculation].[Aggregation].&[MTD], [Measures].[Target])
SELECT NON EMPTY
{ [Measures].[Target]
, [Measures].[Actual]
, [Measures].[MTD_Target]
, [Measures].[MTD_Actual]
, [Measures].[MemberName]
, [Measures].[MemberUniqueName]
} ON COLUMNS
, NON EMPTY
--Added filter to remove non trading branches
{ FILTER([Branch].[Business Hierarchy].[Branch], [Measures].[Actual] <> 0 )
* [Date].[Date Hierarchy].[Date]
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT STRTOMEMBER(@parmDate).Parent.FirstChild:STRTOMEMBER(@parmDate) ON COLUMNS
FROM ( SELECT STRTOSET(@parmBranch) ON COLUMNS
FROM [SampleCube]
)
)
WHERE ([Branch].[Is Main Office].&[No]