BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
metalray
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
metalray
Calcite | Level 5

We figured out that there was an error in the hierarchy.

View solution in original post

2 REPLIES 2
metalray
Calcite | Level 5

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)
)

metalray
Calcite | Level 5

We figured out that there was an error in the hierarchy.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 764 views
  • 0 likes
  • 1 in conversation