Report Portal

Cell Security and Calculated Members Defined in the WITH Clause or Session

Reposted from Chris Webb's blog with the author's permission.

I was asked an interesting question today about cell security – how can you get it to work with calculated members defined in the WITH clause or the session? If, for example, you create a role on the Adventure Works database with the following expression in the Read Permissions box for cell security:

[Geography].[Geography].currentmember is [Geography].[Geography].[Country].&[Australia]
( [Measures].currentmember IS [Measures].[Reseller Sales Amount]
[Measures].currentmember IS [Measures].[Reseller Order Count])

If you then run the following query when connecting via the role:

[Measures].[Reseller Sales Amount] * [Measures].[Reseller Order Count]
{MEASURES.TEST, [Measures].[Reseller Sales Amount], [Measures].[Reseller Order Count], [Measures].[Reseller Gross Profit]}
ON 0,
ON 1
FROM [Adventure Works]

You’ll see the the following results:


This is pretty much what you’d expect – you only see values for Australia for Reseller Sales Amount and Reseller Order Count. The problem here is how you can grant access so the user can see the measure TEST which has been defined in the WITH clause. If you try the following expression in the role:

[Geography].[Geography].currentmember is [Geography].[Geography].[Country].&[Australia]
( [Measures].currentmember IS [Measures].[Reseller Sales Amount] OR [Measures].currentmember IS [Measures].[Reseller Order Count] OR [Measures].currentmember IS [Measures].TEST)

You see the following error in a few cells in the query above:
#Error CellPermission (3, 162) The member '[TEST]' was not found in the cube when the string, [Measures].[TEST], was parsed.

And in any case, how can you know in advance what the name of the calculated measures you want to grant access to are going to be anyway?

What we actually want to do is retain control of all measures, calculated or otherwise, that are defined on the cube, but allow access to any calculated measures defined in the WITH clause or the session. The way to do it is to rely on the fact that cell security is evaluated after the MDX Script and do the following. First define a named set on the cube at the end of the MDX Script something like this:


This set contains all of the measures, including calculated measures, that have been created on the MDX Script – except those that have their Visible property set to False (these you’d have to add to the set manually).

Then use an expression such as this in your role:

[Geography].[Geography].currentmember is [Geography].[Geography].[Country].&[Australia]
( [Measures].currentmember IS [Measures].[Reseller Sales Amount] OR [Measures].currentmember IS [Measures].[Reseller Order Count] OR

The last line is the new part: it checks to see whether the currentmember on Measures is included in the set we’ve just defined. If it is, we can be sure it’s defined on the cube. If it isn’t, it must be defined either in the WITH clause or in the session so we can grant access to it. If you then run the query again, you get the following results:


As you can see, we can now see the value for TEST. Of course you also need to be extremely careful you’re not using a security hole here by doing this, test thoroughly and use Read Contingent permissions as appropriate.


Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at and his blog can be found at .

Tags: mdx, security


2007-2015 VidasSoft Systems Inc.