Database Reference
In-Depth Information
Working with the Time dimensions
In the previous section you learned how to navigate dimension hierarchies. Date- and
time-related dimensions are somewhat special because they are a part of nearly all
business intelligence implementations, and much of the analysis focuses on examin-
ing the trends over time. MDX offers a number of functions for working specifically
with date dimensions. In this section I will provide a couple of examples of the most
frequently exploited time intelligence functions.
How to do it...
A very common reporting requirement is to display the running total of values for each
timespan. Yet another frequent requirement is to compare the current values with that
of an equivalent value during the previous week, month, quarter, or year. The follow-
ing recipe shows the steps to display quarter-to-date and year-to-date running totals,
in addition to reporting internet sales' values for each month. You will also learn how
to compare the current measure's values with the corresponding values from a previ-
ous timespan:
1. Use the
PeriodsToDate
function to get a year-to-date value. This function
accepts the attribute (or level) at which you wish to aggregate the measure as
well as the member to which the values should be aggregated. The calcula-
tion will once again refer to the
CurrentMember
function to advise SSAS to
aggregate year-to-date values to the current member of the
calendar
hier-
archy:
WITH MEMBER [Measures].[YTD] AS
SUM(PERIODSTODATE([Date].[Calendar].[Calendar
Year], [Date].[Calendar].CurrentMember),
[Measures].[Internet Sales Amount])
2.
PeriodsToDate
can work at any level of the time dimension. MDX also
provides special cases of
PeriodsToDate
that work on specific levels:
MTD
function is for month-to-date,
QTD
for quarter-to-date, and
YTD
for year-to-
date. Let's use
QTD
to also report the subtotals for each calendar quarter.
Since
QTD
only works at the quarter level, you do not have to explicitly specify
the attribute on which data needs to be summed:
Search WWH ::
Custom Search