Hello Friends of SAS,
Below we have the nifty MDX filter expressions that gets all descendants from 2 levels above where the user id can be found in the hierarchy.
The MDX works but it gets all the siblings as well not only the main agency that the user is actually in (at bottom level).
General Agency
------Main Agency
---------Team
------------Seller_ID
with Acensors.....2 we want to go 2 levels up and get only the Main Agency
the seller is part of and not all main agencies-
First option of MDX:
Descendants(
Ancestor(
Head(
Filter(
[DIM_
Sales].AllMembers,[DIM_Sales].CurrentMember.Level.Name = Seller_ID' and [DIM_Sales].CurrentMember.Name = 'SUB::SAS.Userid')
).Item(0
)
,2)
)
Second option of MDX:
Generate
(
Filter
(
[Dim_Sales].ALLMEMBERS
,[Dim_Sales].CurrentMember.LEVEL.NAME = 'Seller_ID'
AND
[Dim_Sales].CurrentMember.Name = 'SUB::SAS.Userid'
)
,Descendants
(
Ancestor([Dim_Sales].CurrentMember,2)
)
)
Problem:
We noticed that if the user is in the hierarchy SAS gets/returns everything (even the main agency the user is not part of).
Both MDX Filters behave the same.
So the user will be able to see not only all the descendants of his
agency (2 levels above) but can also see all the other agencies.
How would an enhanced MDX statement look that only the main agencies the userID is in the hierarchy (instead of everything).
Thanks,
Bob
We figured out that there was an error in the hierarchy.
We dont have to problem with the team level. A team lead, also part of the level, sees only his/her team and not the other teams that are silbings to that team.
Team (specific user group) MDX:
Descendants(
Ancestor(
Head(
Filter(
[DIM_
Sales].AllMembers,[DIM_Sales].CurrentMember.Level.Name = Seller_ID' and [DIM_Sales].CurrentMember.Name = 'SUB::SAS.Userid')
).Item(0
)
,1)
)
We figured out that there was an error in the hierarchy.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.