AllMembers Function in MDX

Friends,

This will be used at either Hierarchy or Level of an attribute. Let us First see what is this Hierarchy and Levels. Lets say I have a dimension named “Time” and attributes Year,Quarter and Month. Then each attribute will be represented as shown below.

• [Time].[Year].[Year]
• [Time].[Quarter].[Quarter]
• [Time].[Month].[Month]

Each representation has three parts and the generalized representation of an attribute is  <Dimension>.<Hierarchy>.<Level>. So the levels will be splitted as ollows.

• <Dimension> – Dimension level
• <Dimension>.<Hierarchy> – Hierarchy Level
• <Dimension>.<Hierarchy>.<Level> – Level Level

Now lets come back to AllMembers Function and this mainly used at Hierarchy and Level levels. Lets see the syntax of both.

<Hierarchy_Exp>.AllMembers

<Level_Exp>.AllMembers

The AllMembers function returns a set that contains all members, which includes calculated members, in the specified hierarchy or level. The AllMembers function returns the calculated members even if the specified hierarchy or level contains no visible members.

PFB the sample query at Hierarchy level –

SELECT {} ON COLUMNS,
{[Time].[Year].AllMembers} ON ROWS
FROM [Adventure Works]

The above query returns all the members of the Year hierarchy. It is given at Hierarchy level and hence ALL member will also be returned if the “IsAggregatable” Property of this attribute is set to TRUE in BIDS.

PFB the sample query at Level level –

SELECT {} ON COLUMNS,
{[Time].[Year].[Year].AllMembers} ON ROWS
FROM [Adventure Works]

The above query returns all the members of the Year level. It is given at Hierarchy level and hence ALL member will NOT be returned.

When a dimension contains only a single visible hierarchy, the hierarchy can be either referred to by the dimension name or by the hierarchy name, because the dimension name in this case is resolved to its only visible hierarchy. For example, Measures.AllMembers is a valid MDX expression because it resolves to the only hierarchy in the Measures dimension and this AllMembers function is semantically similar to the AddCalculatedMembers (MDX) function when you use on MEASURES HIERARCHY. PFB sample query for the same.

select Measures.AllMembers on Columns,
[Dim Time].[Calendar Year].&[2004] on Rows
From [Adventure Works]

PFB the screenshot of the same –

That’s it .. Hope you understood the concept .. Happy Coding !!

Regards,

Roopesh Babu V