topic MDX code for authorization in a cube in SAS Web Report Studio

MDX code for authorization in a cube

DannyDizzy — Mon, 25 Mar 2013 15:21:47 GMT

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

Re: MDX code for authorization in a cube

FredrikE — Wed, 03 Apr 2013 14:00:07 GMT

I have used descendants together with ascendants to achieve that.

I combined the two using :

generate(

Filter([DIM_Organisation].[DIM_Organisation].[koststal].members,IN ([DIM_Organisation].CurrentMember.name,"SUB::SAS.IdentityGroups"))

ascendants([DIM_Organisation].[DIM_Organisation].currentmember)

)

generate(

Filter([DIM_Organisation].[DIM_Organisation].[koststal].members,IN ([DIM_Organisation].CurrentMember.name,"SUB::SAS.IdentityGroups"))

descendants([DIM_Organisation].[DIM_Organisation].currentmember)

)

Re: MDX code for authorization in a cube

DannyDizzy — Thu, 04 Apr 2013 11:38:30 GMT

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)

)

}