Reporting with MDX Queries - Pro SQL Server 2012 BI Solutions - page 568

code in a professional manner, we recommend using curly braces and square brackets even when they are not
required. We also recommend using sentence, or Pascal, casing when defining objects and using white spaces
consistently to organize your code.
Ti ■ it is considered an unofficial industry standard to use curly braces and square brackets at all times in MDX
code. Most printed material demonstrating code, such as magazine articles, books, and web pages, do so as well.
Default Members
The code samples so far have included members of the Titles and Measures dimensions but have not explicitly
defined members from the others. Yet, the measured values returned in the results include members from each
and every dimension. Dimensions that are not explicitly specified within your MDX query are implicitly defined
using a default member.
The way this works is that a default member exists for each dimension, and unless you specify otherwise,
it will be the grand total of all the members within the dimensions. The grand total is referred to as the All
member. For example, if we do not specify which author we want information about, MDX will assume we want
information about all authors. And any totals that we receive as part of the measure will be an aggregation of all
authors combined.
It is possible to set a specific member as a default for a dimension, but this is probably not the best way to
handle it. For example, if you define the current year as the default member of the date dimension and somebody
forgot to include a specific date within their MDX query, Analysis Server returns data only for the current year. If
everyone in the client's company understood that rule, then everything would be OK. Otherwise, your users will
be under the assumption that they are receiving the total of every year, not just the current one, and the report
will be inaccurate. For this reason, we recommend leaving the default setting for your dimensions, which is the
total of all members for each dimension.
To explicitly tell Analysis Server to use the default member of a dimension in MDX, you can use the
DefaultMember function, as shown in Listing 14-6. Note that we are using the name of the dimension, and
the name of the leaf member in this example. But we could have used any of the attributes or user-defined
hierarchies defined in each dimension, because they all have the same ultimate parent, the All member.
Listing 14-6. The DefaultMember Function
Select
From [CubePubsSales];
-- Same As...
Select
{ [Measures]. DefaultMember } On Columns,
{( −- Parentheses are required to Define a SET of Coordinates
[DimAuthors].[Author]. DefaultMember
,[DimDates].[Date]. DefaultMember
,[DimOrders].[OrderNumber]. DefaultMember
,[DimStores].[Store]. DefaultMember
,[DimTitles].[Title]. DefaultMember
)} On Rows
From [CubePubsSales];