Hi,
I'd like to apologise in advance for any weird grammer that may occur - this is not my native language.
I am not sure if what I/we want/need is possible but here we go:
I was tasked with remanufacturing some reports which so far have been sent out manually as Excel-Sheets. We intend to use a WRS-Report based on an OLAP cube instead in their place. However, the data inside is fairly sensitive which means I need to filter by User-ID in regards to who gets to see what report. This is going to happen via User-groups.
Now my problem is following:
There is a 3-tier hierarchy regarding the divisions.
Tier1 could be "A" and "B".
Tier2 could be "AA", "BB" and "CC"
Tier3 could be "AAA", "BBB", "CCC", "DDD", etc all the way to "KKK".
However, the hierarchy doesnt look neat like in Ex.1 but more like Ex.2:
(Ex.1)
(Ex.2)
So far, I've tried (and failed) to ONLY give access to the (DDD)-report without giving access to the (A)-report and (A-empty) report as well.
My best-working try so far has been achieved with the following MDX expression in the SAS OLAP Cube Studio, however I'd like (and need) to prevent the User to see anything in the "empty" division.
{
[Abteilung].[Abteilung].[Alle Abteilung],
[Abteilung].[Abteilung].[Alle Abteilung].[A],
[Abteilung].[Abteilung].[Alle Abteilung].[A].[ ],
[Abteilung].[Abteilung].[Alle Abteilung].[A].[ ].[DDD]
}
When leaving out the third line inside the {}, all I see is the A-report but with no way to as well look at the DDD-report.
There are also cases when the recipients of the reports may look at AA and CCC (but not A or AAA/BBB) or B and III (not BB or HHH).
So: Is it at all possible to do what I need and if yes, how?
I already tried to "disable" the middle tier view with a <!--condition, however I might've misunderstood what use those have/bring as my result didn't change.
Hi!
I dont know if this helps you, it was a while ago since I worked with cubes and MDX....:)
Here is a sample code on how we solved access to different levels of a dimension, I remeber that the <!--Condition--> was important to get it to work :). We defined groups in metadata tha correspond to the data in the cube and depending on which group a person belonged to that information was retrieved from the cube :
generate(
Filter([Customer].[Customer Unit ID].[Customer_id].members,IN ([Customer].CurrentMember.name,"SUB::SAS.IdentityGroups"))
,
ascendants([Customer].[Customer Unit ID].currentmember)
)
<!--CONDITION-->
generate(
Filter({[Customer].[Customer Unit Name].[Customer_id].members},IN ([Customer].[Customer Unit Name].CurrentMember.name,"SUB::SAS.IdentityGroups"))
,
ascendants([Customer].[Customer Unit Name].currentmember)
)
generate(
Filter([Customer].[Region ID].members,IN ([Customer].[Region ID].CurrentMember.name,"RECA"))
,
ascendants([Customer].[Region ID].currentmember)
)
<!--CONDITION-->
generate(
Filter({[Customer].[Region Name].members},IN ([Customer].[Region Name].CurrentMember.name,"N Europe & Cent Asia"))
,
ascendants([Customer].[Region Name].currentmember)
)
generate(
Filter([Customer].[Customer Unit ID].members,IN ([Customer].[Customer Unit ID].CurrentMember.name,"100017"))
,
ascendants([Customer].[Customer Unit ID].currentmember)
)
<!--CONDITION-->
generate(
Filter({[Customer].[Customer Unit Name].members},IN ([Customer].[Customer Unit Name].CurrentMember.name,"Verizon"))
,
ascendants([Customer].[Customer Unit Name].currentmember)
)
Hi!
I dont know if this helps you, it was a while ago since I worked with cubes and MDX....:)
Here is a sample code on how we solved access to different levels of a dimension, I remeber that the <!--Condition--> was important to get it to work :). We defined groups in metadata tha correspond to the data in the cube and depending on which group a person belonged to that information was retrieved from the cube :
generate(
Filter([Customer].[Customer Unit ID].[Customer_id].members,IN ([Customer].CurrentMember.name,"SUB::SAS.IdentityGroups"))
,
ascendants([Customer].[Customer Unit ID].currentmember)
)
<!--CONDITION-->
generate(
Filter({[Customer].[Customer Unit Name].[Customer_id].members},IN ([Customer].[Customer Unit Name].CurrentMember.name,"SUB::SAS.IdentityGroups"))
,
ascendants([Customer].[Customer Unit Name].currentmember)
)
generate(
Filter([Customer].[Region ID].members,IN ([Customer].[Region ID].CurrentMember.name,"RECA"))
,
ascendants([Customer].[Region ID].currentmember)
)
<!--CONDITION-->
generate(
Filter({[Customer].[Region Name].members},IN ([Customer].[Region Name].CurrentMember.name,"N Europe & Cent Asia"))
,
ascendants([Customer].[Region Name].currentmember)
)
generate(
Filter([Customer].[Customer Unit ID].members,IN ([Customer].[Customer Unit ID].CurrentMember.name,"100017"))
,
ascendants([Customer].[Customer Unit ID].currentmember)
)
<!--CONDITION-->
generate(
Filter({[Customer].[Customer Unit Name].members},IN ([Customer].[Customer Unit Name].CurrentMember.name,"Verizon"))
,
ascendants([Customer].[Customer Unit Name].currentmember)
)
This was indeed what we were looking for, thank you!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: