In Chapter 1 of our book, MDX with SSAS 2012, we’ve devoted a section “Setting a default member of a hierarchy in the MDX script” to show three places where you can set up Default members for hierarchies.
For more information about how to define a default member for hierarchies and whether you should have a default member or not for hierarchies, please also refer to the following Microsoft Developer Network article:
MDX script is an easy place to maintain the default member definition
As the MDX book pointed out that the MDX script is an easy place to maintain the default member definition.
Below is an example of how to define a default member for the Destination Currency hierarchy by issuing an UPDATE DIMENSION command.
/*– Set default member for the Destination Currency cube dimension —-*/
Alter Cube
CurrentCube
Update Dimension [Destination Currency].[Destination Currency],
Default_Member = [Destination Currency].[Destination Currency].[US Dollar];
MDX query is also an easy place to find what the default members are for each hierarchy
As it turned out that MDX query is also an easy place to find what the default members are for each hierarchy. For every report developer, knowing how to find the default measure is also crucial. You can obviously check the dimension and measure group structures in SQL Server Data Tools, or the cube’s MDX script. But the easiest way to find default members for hierarchies and the cube’s default measure is to write your own MDX query using the DefaultMember function. Please refer to the following MSDN article:
The general syntax is:
Hierarchy_Expression.DefaultMember
Three Sample MDX queries to find default members for hierarchies and cube default measure
The following are three examples to find default members for hierarchies and the cube’s default measure.
Find Default Member of Destination Currency hierarchy
WITH MEMBER Measures.[Default Member] AS
[Destination Currency].[Destination Currency].DefaultMember.Name
SELECT
Measures.[Default Member] ON 0
FROM
[Adventure Works]
— Default member for Destination Currency hierarchy: US Dollar
Find Default Member of Geography hierarchy
WITH MEMBER Measures.[Default Member] AS
[Geography].[Geography].DefaultMember.Name
SELECT
Measures.[Default Member] ON 0
FROM
[Adventure Works]
— Default member for Geography hierarchy: All Geographies
Find Default Measure of the Adventure Works cube
WITH MEMBER Measures.[Default Measure] AS
[Measures].DefaultMember.Name
SELECT
Measures.[Default Measure] ON 0
FROM
[Adventure Works]
— Default cube measure: Reseller Sales Amount
Showing default measure and the value in one query
By placing the default measure directly on the X axis, you can get the default measure name and the value in one query.
SELECT
–Measures.[Default Measure] ON 0
[Measures].DefaultMember on 0
FROM
[Adventure Works]
Reseller Sales Amount
$80,450,596.98
