How to filter with hierarchy

Hi,

I have a cube which has a "Relationship Hierarchy" dimension which is hierarchical. I have written the following MDX query which has a filter on the rows axis...

SELECT
NON EMPTY { Hierarchize({[Cost Centre].[L01 Cost Centre Name].members}) } ON COLUMNS,
NON EMPTY {
FILTER(
Hierarchize({[Relationship Hierarchy].[Level1 Full Name].members}),
[Relationship Hierarchy].currentmember is [AXA SA]]
)
}
ON ROWS
FROM [MyMart] WHERE ([Measures].[Costs])

The query works as expected, but now I want to do something a little more advanced and I can't figure out how. I would like to have all children of 'AXA SA' returned too. So basically, I want to say this...

FILTER(<set>, [Relationship Hierarchy].currentmember is [AXA SA] or [Relationship Hierarchy].currentmember is [AXA SA].children)

Obviously the "is [AXA SA].children" syntax is not valid. But does anyone know how to do this?

Similarly, is possible to say I want any members from "[Relationship Hierarchy]" who have a parent of "[AXA SA]"?

Thanks,

Lachlan

In your example, the Filter function is redundant. You can simply use { [AXA SA] } or [AXA SA].children to get the sets you want.|||

Hi,

Sorry, maybe my question didn't really make sense. What I wanted to do was only filter on the level 1 members in my [Relationship Hierarchy] dimension. The problem with my query above is that the children are never retrieved because the filter is also applied to them too. I just needed to move the filter to the correct place so the children are not filtered too...

SELECT
NON EMPTY { Hierarchize({[Cost Centre].[L01 Cost Centre Name].members}) } ON COLUMNS,
NON EMPTY {
Hierarchize({ FILTER([Relationship Hierarchy].[Level1 Full Name].members, [Relationship Hierarchy].currentmember is [AXA SA]), AddCalculatedMembers( {[Relationship Hierarchy].[All Relationship Hierarchy].[AXA SA].children} )})
}
ON ROWS
FROM [mymart]
WHERE ([Measures].[Costs])