The following calculated members return accumulated figures for a given measure and selected level in period dimension (you need to have a period (time) utility dimension in the cube in order to create this member).
YTD (Year To Date Calculation)
CREATE MEMBER CURRENTCUBE.[Period Utility].[Period Accumulations].[YTD (Operational)]
AS NULL,
FORMAT_STRING = "#,#.0",
VISIBLE = 1;
SCOPE ([Period Utility].[Period Accumulations].[YTD (Operational)]);
this = SUM(
PeriodsToDate(
[Period].[Operational Year - Quarter - Month].[Year],
[Period].[Operational Year - Quarter - Month].CurrentMember
), ([Measures].CurrentMember, [Period Utility].[Period Accumulations].DefaultMember)
);
END SCOPE; Following MDX can be used to validate figures.
SELECT
(
{[Measures].[Stg - Measure Name]} * {[Period Utility].[Period Accumulations].[YTD (Operational)]}
)
On Columns,
Non Empty {
[Period].[Operational Year - Quarter - Month].[Month].members
} On Rows
From [Cube Name]
QTD (Quater To Date Calculation)
CREATE MEMBER CURRENTCUBE.[Period Utility].[Period Accumulations].[QTD (Operational)]
AS NULL,
FORMAT_STRING = "#,#.0",
VISIBLE = 1;
SCOPE ([Period Utility].[Period Accumulations].[QTD (Operational)]);
this = SUM(
PeriodsToDate(
[Period].[Operational Year - Quarter - Month].[Quarter],
[Period].[Operational Year - Quarter - Month].CurrentMember
), ([Measures].CurrentMember, [Period Utility].[Period Accumulations].DefaultMember)
);
END SCOPE;
Following MDX can be used to validate figures.
SELECT
(
{[Measures].[Stg - Measure Name]} * {[Period Utility].[Period Accumulations].[QTD (Operational)]}
)
On Columns,
Non Empty {
[Period].[Operational Year - Quarter - Month].[Month].members
} On Rows
From [Cube Name]
No comments:
Post a Comment