Database Reference
In-Depth Information
Not
■
The
NonEmpty
function in MDX uses parentheses similar to most programming languages.
The
Non Empty
clause does not. Additionally, as you may have noticed, there is no space in the name of the
NonEmpty
function, but there is a space in the
Non Empty
clause!
Listing 14-25.
Using the
Non Empty
Clause and the
NonEmpty
Function
Select
-- Start with Lots of nulls in eight columns and six rows
{ [DimDates].[Year-Qtr-Month-Day].[Year].AllMembers } On Columns,
{ [DimTitles].[TitlesByType].[TitleType].AllMembers } On Rows
From [CubePubsSales]
GO
Select
Non Empty -- Now has less nulls (5 columns removed)
{ [DimDates].[Year-Qtr-Month-Day].[Year].AllMembers } On Columns,
{ [DimTitles].[TitlesByType].[TitleType].AllMembers } On Rows
From [CubePubsSales]
GO
Select
Non Empty -- Even Less nulls (1 more row removed)
{
NonEmpty
( [DimDates].[Year-Qtr-Month-Day].[Year].AllMembers ) } On Columns,
{
NonEmpty
( [DimTitles].[TitlesByType].[TitleType].AllMembers ) } On Rows
From [CubePubsSales]
Member and Level Paths
Developers may find it challenging that MDX code can be written with many variations. For example, to locate a
particular member or level, you provide its name, like
June
, or a full path, like
DimTime.1992.June
. And the path
can include various combinations of the dimension, hierarchy, level, member, and various functions.
Unfortunately, when you incorrectly indicate a path to a member, SSAS does not return an error. It returns
an empty result set. This oddity makes developers scratch their head all the time, wondering what exactly
went wrong.
One way to avoid path issues is to memorize typical patterns used to access a member or level.
The pattern to memorize is this:
Dimension
[Optional].
Hierarchy
[Optional].
Level
[Optional].
Member
.<
ChildMember>
.<
ChildMember>
The code in Listing 14-26 displays examples of what the different paths may look like. Notice that each
statement in this listing returns the same result, as shown in Figure
14-21
.
