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
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)
)
}
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)
)
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)
)
}
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!
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.
Ready to level-up your skills? Choose your own adventure.