Reputation: 8138
I have an OLAP Basically there is a dimension that has parent-child relationship. So the dimension has a parent-id and a child-id.
There is a fact table that exists that has the child-id. I would like to get data for a child and all its given children when I provide the parent id.
How could I achieve this in a MDX query ?
<Dimension foreignKey="child_id" name="SUPPLIER">
<Hierarchy hasAll="true" allMemberName="all" allMemberCaption="all" primaryKey="child_id" >
<Table name="suppliers">
</Table>
<Level name="SUPPLIER_L" column="child_id" nameColumn="child_id" parentColumn="parent_id"
uniqueMembers="true" levelType="Regular" hideMemberIf="Never" >
</Level>
</Hierarchy>
</Dimension>
I have my dimension where this hierarchy occurs.
Upvotes: 1
Views: 2967
Reputation: 9375
The following MDX should give you the sub-tree under 'your-parent-id' :
SELECT [Measures].[your-measure] on 0, Descendants( [Supplier].&[your-parent-id], [Supplier].&[your-parent-id].level, SELF_AND_AFTER) ON 1 FROM [your-cube]
Upvotes: 0
Reputation: 5164
Take a look at the DESCENDANT MDX function.
You just say where you want to start in the hierachy and then where you want to stop at what level. Then it will give you all the level between start and end points.
Post the part of your mondrian schema cube so I can give you the exact syntax
Upvotes: 2