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)
)
}
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.