September 8, 2014 at 12:40 pm
Hi all,
I am trying to pull a corporate hierarchy, and there are multiple levels (level 2 (CEO), level 3(SVP) up to level 13).
If I use this query, it only returns the records where level 13 is not null. For example, if the record ends on level 9, it will not get returned.
SELECT
[Measures].[Records] ON COLUMNS,
(
DESCENDANTS([Employee Info].[Employees].[Level 13].ALLMEMBERS)
* [Employee Info].[Employee ID].[Employee ID].ALLMEMBERS
* [Employee Info].[Grp].[Grp].ALLMEMBERS ) DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS
FROM [CUBE] CELL PROPERTIES VALUE
Can someone teach me how to get all records regardless whatever level they are null at?
Thanks,
September 10, 2014 at 4:35 am
Without knowing the structure of your hierarchy it's difficult, but at a guess I'd say that your query is doing exactly what you are asking it to - showing descendants of level 13. If this is the last level in the hierarchy then that would be itself. Try using your lowest level in the hierarchy instead.
Your problem is then that due to the descendants function you'll be bringing through values for Employee ID and Grp from each existing level below the hierarchy you are looking at. I would suggest the approach of making these values member properties. Have a look at the Employee hierarchy on the AdventureWorks cube to get an idea of how this is done. Then your query could look like this (example against adventureworks):
WITH MEMBER Login AS
[Employee].[Employees].Properties( "Login ID" )
MEMBER Email AS
[Employee].[Employees].Properties( "Email Address" )
SELECT
{Login,
Email,
[Measures].[Reseller Sales Amount]} ON 0,
DESCENDANTS([Employee].[Employees].[Employee Level 02])
ON 1
FROM
[Adventure Works]
Apologies if this isn't what you are looking to ultimately achieve....
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply