Traditional web-based reporting with SAS BI tools

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 207
Accepted Solution

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

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:Smiley FrustratedAS.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:Smiley FrustratedAS.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


Accepted Solutions
Solution
‎06-29-2011 11:09 AM
Regular Contributor
Posts: 207

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

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

View solution in original post


All Replies
Regular Contributor
Posts: 207

Cube returns all ancestor siblings if member cant be found in 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:Smiley FrustratedAS.Userid')
).Item(0

)
,1)
)

Solution
‎06-29-2011 11:09 AM
Regular Contributor
Posts: 207

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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