Cube returns all ancestor siblings if member cant be found in hierarchy.

metalray — Tue, 28 Jun 2011 09:04:26 GMT

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

Cube returns all ancestor siblings if member cant be found in hierarchy.

metalray — Tue, 28 Jun 2011 09:32:30 GMT

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

Cube returns all ancestor siblings if member cant be found in hierarchy.

metalray — Wed, 29 Jun 2011 15:09:29 GMT

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

topic Cube returns all ancestor siblings if member cant be found in hierarchy. in SAS Web Report Studio

Cube returns all ancestor siblings if member cant be found in hierarchy.

Cube returns all ancestor siblings if member cant be found in hierarchy.

Cube returns all ancestor siblings if member cant be found in hierarchy.