MDX code for authorization in a cube

[DannyDizzy]

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

[FredrikE]

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 · Accepted solution]

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)

)

}