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