Database Reference
In-Depth Information
Navigating dimension hierarchies
Earlier in this chapter you learned how to use the
CurrentMember
function to re-
trieve the name of the current hierarchy member. MDX allows browsing hierarchies
easily using similar functions,
PrevMember
and
NextMember
, which are extremely
useful for trend analysis. Additionally, you can obtain hierarchy members using rel-
ative functions, such as children, ancestors, descendants, and parent, to build sets
based on the members of interest. This section will list examples where these func-
tions are particularly beneficial.
How to do it...
Let's get started with navigating dimension hierarchies.
1. To implement the
[Year-over-Year Growth in Reseller Sales
Amount]
calculated measure, open the sample
Adventure Works 2012
database in
SQL Server Data Tools
(
SSDT
), navigate to
Adventure Works
cube's
Calculations
tab, and enter the expression that will follow. Note that
we have two nested IIF functions. The first IIF function uses an ordinal
function to determine whether we are at the
[Calendar Year]
level of the
[Date].[Calendar]
hierarchy and applies the calculation only at that level.
The second IIF function uses the
PrevMember
function to ensure the previ-
ous calendar year member exists and has a non-zero value for the
Reseller
Sales Amount
measure:
CREATE MEMBER
CURRENTCUBE.[Measures].[Year-To-Year
Reseller Sales Growth]
AS
IIF([Date].[Calendar].CurrentMember.Level.Ordinal
= 1,
IIF (
([Date].[Calendar].CurrentMember.PrevMember,
measures.[Reseller Sales Amount])=0
OR
ISEMPTY([Date].[Calendar].CurrentMember.PrevMember),
"N/A",
Search WWH ::
Custom Search