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 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1181 views
  • 3 likes
  • 2 in conversation