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

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:

Example1.png(Ex.1)         Example2.png(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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
FredrikE
Rhodochrosite | Level 12

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

View solution in original post

2 REPLIES 2
FredrikE
Rhodochrosite | Level 12

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

Ravera
Calcite | Level 5

This was indeed what we were looking for, thank you!

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
  • 1282 views
  • 0 likes
  • 2 in conversation