Traditional web-based reporting with SAS BI tools

MDX code for authorization in a cube

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

MDX code for authorization in a cube


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:Smiley FrustratedAS.IdentityGroups")).item(0)),

Ancestors(Filter({[D_OrgArende].[D_OrgArende].MEMBERS},IN (SUBSTR([D_OrgArende].[D_OrgArende].CurrentMember.name,1,4), "SUB:Smiley FrustratedAS.IdentityGroups")).item(1)),

Ancestors(Filter({[D_OrgArende].[D_OrgArende].MEMBERS},IN (SUBSTR([D_OrgArende].[D_OrgArende].CurrentMember.name,1,4), "SUB:Smiley FrustratedAS.IdentityGroups")).item(2)),

Ancestors(Filter({[D_OrgArende].[D_OrgArende].MEMBERS},IN (SUBSTR([D_OrgArende].[D_OrgArende].CurrentMember.name,1,4), "SUB:Smiley FrustratedAS.IdentityGroups")).item(3)),

Ancestors(Filter({[D_OrgArende].[D_OrgArende].MEMBERS},IN (SUBSTR([D_OrgArende].[D_OrgArende].CurrentMember.name,1,4), "SUB:Smiley FrustratedAS.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


Accepted Solutions
Solution
‎04-04-2013 07:38 AM
Contributor
Posts: 28

Re: MDX code for authorization in a cube

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:Smiley FrustratedAS.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:Smiley FrustratedAS.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:Smiley FrustratedAS.PersonName')

   ,

   Ascendants([D_OrgArende].[D_OrgArende].CurrentMember)

)

}

View solution in original post


All Replies
Regular Contributor
Posts: 187

Re: MDX code for authorization in a cube

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:Smiley FrustratedAS.IdentityGroups"))

,

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

)

<!--CONDITION-->

generate(

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

,

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

)

Solution
‎04-04-2013 07:38 AM
Contributor
Posts: 28

Re: MDX code for authorization in a cube

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:Smiley FrustratedAS.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:Smiley FrustratedAS.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:Smiley FrustratedAS.PersonName')

   ,

   Ascendants([D_OrgArende].[D_OrgArende].CurrentMember)

)

}

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 431 views
  • 3 likes
  • 2 in conversation