June 24, 2014 at 4:05 am
Hi I have got parent child hierarchy set up on a cube in one of my projects.
Now i have a requirement to show distinct objects in that hierarchy i.e distinct parent + distinct child
can some one help me for MDX that i can use for calculated measure to give me desired result?
I tried using Descendants function in calculated measure
But i don't get count of parent and childs in parent-child hierarchy
SUM(Descendants([SQL Dependency].[SQL Dependency Hierarchy].CurrentMember , 0, SELF_AND_AFTER),
[Measures].[Object Instance Count] )
Just go summarize the requirement i need to get distinct object in my parent child hierarchy:
e.g
M1 -> E1 ->E2 i need to get 3
June 24, 2014 at 4:34 am
If you are after what I think you are (the hierarchy outputted onto different columns for each level) then I don't think you can do that with MDX, it's not really what it's for.
One option would be to connect to your cube using excel and then use the classic pivot table layout to create this output.
Another would be to query the cube using openquery and return the results that way. See an example of this about half way down this page: http://sqlblogcasts.com/blogs/drjohn/archive/2008/09/27/mdx-and-sql-combining-relational-and-multi-dimensional-data-into-one-query-result-set.aspx
June 25, 2014 at 11:05 am
Run this on AdventureWorks cube.
Example of Product hierarchy.
I think it is what you are describing.
Basically show all members and showing no measure.
SELECT { } ON COLUMNS, { ([Product].[Product Model Lines].[Model].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply