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


Hi!

I would like to add a authorization for people belonging to certain groups in a olap cube to be used for a wrs-report, one person can belong to multiple groups. This part-code is currently working fine:

{

Ancestors(Filter({[D_OrgArende].[D_OrgArende].MEMBERS},IN (SUBSTR([D_OrgArende].[D_OrgArende].CurrentMember.name,1,4), "SUB::SAS.IdentityGroups")).item(0)),

Ancestors(Filter({[D_OrgArende].[D_OrgArende].MEMBERS},IN (SUBSTR([D_OrgArende].[D_OrgArende].CurrentMember.name,1,4), "SUB::SAS.IdentityGroups")).item(1)),

Ancestors(Filter({[D_OrgArende].[D_OrgArende].MEMBERS},IN (SUBSTR([D_OrgArende].[D_OrgArende].CurrentMember.name,1,4), "SUB::SAS.IdentityGroups")).item(2)),

Ancestors(Filter({[D_OrgArende].[D_OrgArende].MEMBERS},IN (SUBSTR([D_OrgArende].[D_OrgArende].CurrentMember.name,1,4), "SUB::SAS.IdentityGroups")).item(3)),

Ancestors(Filter({[D_OrgArende].[D_OrgArende].MEMBERS},IN (SUBSTR([D_OrgArende].[D_OrgArende].CurrentMember.name,1,4), "SUB::SAS.IdentityGroups")).item(4))

}

The problem is that I'm limited to the number of rows I put in the code. Is it possible somehow to get all Ancestors for a set of members? Now the code picks out a single group (member) at the time from the returned set of the filter function.

Thanks!

/Daniel

1 ACCEPTED SOLUTION

Accepted Solutions
DannyDizzy
Fluorite | Level 6

Thanks Fredrik! The generate function worked magic for me!

This was the result of my code:

{

/********************************************************

Lets you se all the members below a certain point of authurization.

*********************************************************/

(Filter({[D_OrgArende].[D_OrgArende].MEMBERS},IN (SUBSTR([D_OrgArende].[D_OrgArende].Parent.Name,1,4),"SUB::SAS.IdentityGroups")))

,

/********************************************************

Lets you see all the members which leeds to the point of authorization (needed to be able to drill down/expand to the point of authorization)

*********************************************************/

generate(

   Filter({[D_OrgArende].[D_OrgArende].MEMBERS}, IN (SUBSTR([D_OrgArende].[D_OrgArende].CurrentMember.name,1,4) , "SUB::SAS.IdentityGroups"))

   ,

   Ascendants( [D_OrgArende].[D_OrgArende].CurrentMember)

)

,

/********************************************************

Lets you see the point which is connected to your id and the way leding to it.

*********************************************************/

generate(

   Filter([D_OrgArende].[D_OrgArende].AllMembers,[D_OrgArende].[D_OrgArende].CurrentMember.Level.Name = 'KORTID' and [D_OrgArende].[D_OrgArende].CurrentMember.Name = 'SUB::SAS.PersonName')

   ,

   Ascendants([D_OrgArende].[D_OrgArende].CurrentMember)

)

}

View solution in original post

2 REPLIES 2
FredrikE
Rhodochrosite | Level 12

I have used descendants together with ascendants to achieve that.

I combined the two using <!--CONDITION-->:

generate(

Filter([DIM_Organisation].[DIM_Organisation].[koststal].members,IN ([DIM_Organisation].CurrentMember.name,"SUB::SAS.IdentityGroups"))

,

ascendants([DIM_Organisation].[DIM_Organisation].currentmember)

)

<!--CONDITION-->

generate(

Filter([DIM_Organisation].[DIM_Organisation].[koststal].members,IN ([DIM_Organisation].CurrentMember.name,"SUB::SAS.IdentityGroups"))

,

descendants([DIM_Organisation].[DIM_Organisation].currentmember)

)

DannyDizzy
Fluorite | Level 6

Thanks Fredrik! The generate function worked magic for me!

This was the result of my code:

{

/********************************************************

Lets you se all the members below a certain point of authurization.

*********************************************************/

(Filter({[D_OrgArende].[D_OrgArende].MEMBERS},IN (SUBSTR([D_OrgArende].[D_OrgArende].Parent.Name,1,4),"SUB::SAS.IdentityGroups")))

,

/********************************************************

Lets you see all the members which leeds to the point of authorization (needed to be able to drill down/expand to the point of authorization)

*********************************************************/

generate(

   Filter({[D_OrgArende].[D_OrgArende].MEMBERS}, IN (SUBSTR([D_OrgArende].[D_OrgArende].CurrentMember.name,1,4) , "SUB::SAS.IdentityGroups"))

   ,

   Ascendants( [D_OrgArende].[D_OrgArende].CurrentMember)

)

,

/********************************************************

Lets you see the point which is connected to your id and the way leding to it.

*********************************************************/

generate(

   Filter([D_OrgArende].[D_OrgArende].AllMembers,[D_OrgArende].[D_OrgArende].CurrentMember.Level.Name = 'KORTID' and [D_OrgArende].[D_OrgArende].CurrentMember.Name = 'SUB::SAS.PersonName')

   ,

   Ascendants([D_OrgArende].[D_OrgArende].CurrentMember)

)

}

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 1013 views
  • 3 likes
  • 2 in conversation