Children Function in MDX

Friends,

This acts similar to AllMembers function when you use this with attributes. PFB sample query of using Children function with attribute.

select [Measures].[Sales Amount] on 0,
[Dim Product].[Products].children on 1
from [Adventure Works]

or

select [Measures].[Sales Amount] on 0,
[Dim Product].[Products].AllMembers on 1
from [Adventure Works]

Both the above queries gives the same output. The power of CHILDREN function will come into picture when you are using this function with Hierarchies. Children function returns the next level members of a specified member or simply to say it returns Children of a member. 

Syntax:

“Member_Expression.Children”

If you have a hierarchy defined then this Children function returns the naturally ordered set which is the children of the member specified. If the specified member has no children, this function returns an empty set.

To demonstrate Children function I created a Dimension named “Dim Time” and defined a Hierarchy with the following levels.

• Calendar Year
• Calendar Quarter
• English Month Name

Now, in the below query I passed 2002 from “Calendar year” level as member_expression to the children function and the query returned the children of 2002 member i.e Quarters 1,2,3,and 4 as shown in below Screenshot.

select [Measures].[Sales Amount] on 0,
[Dim Time].[Hierarchy].[Calendar Year].&[2002].Children on 1
from [Adventure Works]

Now in the below Example I am passing Member from “English Month Name” level of the hierarchy and as this is the last level of the hierarchy, there won’t be any children and hence it returns empty result set as shown in the below screenshot.

select [Measures].[Sales Amount] on 0,
[Dim Time].[Hierarchy].[Calendar Year].&[2002].&[4].&[December].Children on 1
from [Adventure Works]

Note: It is not that the Result set will be empty when you take a member from LAST level of the hierarchy. When you pass ANY MEMBER from ANY LEVEL which doesn’t have children then the result set will be empty. This looks very simple one but it is very useful and powerful when working on MDX Calculations.

That’s it .. Happy Coding !!

Regards,

Roopesh Babu V