CODEWITHSUNDEEP
ssasmdxbusiness-intelligenceolap
lco
lco

Reputation: 21

How filter only the children members in MDX?

When I run this mdx query, works fine (get the children members from a hierarchy level):

select {} on columns,
[Dimension].[hierarchy].[level].children on rows
from [Cube]

But, when I add some tuple on rows, doesn't filter filter the children members (shows all the members) :S

select {} on columns,
[Dimension].[hierarchy].[level].children 
* [Dimension2].[hierarchy2].[level2].allmembers on rows
from [Cube]

Upvotes: 2

Views: 4268

Answers (3)

casenonsensitive
casenonsensitive

Reputation: 950

I guess you want only those rows where the children have a value on the default measure. In that case you could try the following:

select {} on columns,
Nonempty([Dimension].[hierarchy].[level].children 
* [Dimension2].[hierarchy2].[level2].allmembers) on rows
from [Cube]

Now if, for the children, you'd need all the members from Dimension2 then you could try:

select {} on columns,
Nonempty([Dimension].[hierarchy].[level].children, [Dimension2].[hierarchy2].[level2].allmembers)
* [Dimension2].[hierarchy2].[level2].allmembers) on rows
from [Cube]

In the second case the Nonempty function takes a second parameter and the cross join is done with the result of the Nonempty function. For the documentation on Nonempty including the usage of the second parameter see https://learn.microsoft.com/en-us/sql/mdx/nonempty-mdx

Upvotes: 0

Vhteghem_Ph
Vhteghem_Ph

Reputation: 154

if you look at the mdx language reference for children, you will also find another example of how to use the function with a hierarchy in stead of a member_expression.

http://msdn.microsoft.com/en-us/library/ms146018.aspx

but it won't work with a hierarchy level.

Maybe the row expression was initialy a hierarchy that you've have changed into a level expression.

in the following a similar working mdx with a hierarchy on rows:

select {} on 0,
[Product].[Model Name].children
*
[Geography].[Country].[All Geographies]
 on 1
FROM [Adventure Works

Philip,

Upvotes: 0

whytheq
whytheq

Reputation: 35557

* is a cross join - you will get the Cartesian product of [Dimension].[hierarchy].[level].children and [Dimension2].[hierarchy2].[level2].allmembers because they are different dimensions.

If they were two hierarchies from the same dimension then auto exist behaviour would limit the results e.g. Year2014 crossed with month should just show the months in 2014.

Try using DESCENDANTS function + you might not require NULLs so try the NON EMPTY

SELECT
  {} ON COLUMNS,
  NON EMPTY
  DESCENDANTS(
    [Dimension].[hierarchy].[level].[PickAHigherUpMember],
    [Dimension].[hierarchy].[PickTheLevelYouWantToDrillTo]
    ) 
  * 
  [Dimension2].[hierarchy2].[level2].allmembers ON ROWS
FROM [Cube]

Upvotes: 1

Related Questions