Kasper de Jonge PowerPivot Blog

I was working with a client where i had some difficult security questions, that i had to solve without having to resort to advanced MDX query’s. The client has to be able to create reports without MDX knowledge. Take the following example from the AdventureWorks cube:

I want to give users a report that show all sales by state province of the country they are in, e.g. you are a sales person belonging to postal code 91801 and you want a report showing all the state-province’s of the United States since that is the country you belong to.
As seen in the dimension below:

But by default users are forbidden to see the sales of another state province when their postal code isn’t in it. When you place security on a country by using a role, autoexists take care of the security for the rest of the levels resulting in only the parents of the selected postal codes will be shown to the user with the role defined.

This will result in not being able to show the parent levels your postal code does not belong to, as seen below while browsing the report using the role:

When you create a report based on this dimension you get the following report (don’t mind the formatting)

The key to solving this problem is using two dimensions with the same levels. One with the security enabled by using a role and one dimension (you can just copy the original dimension) without security, you could remove the postal code level in this dimension to make sure the security is intact.

When you create your report you use levels from both dimensions, and this is where autoexists is your friend. Since you want to show only city’s from a sales persons own country you use the the country level from the secured dimension (only his own country is returned by the secured dimension) and the state province level from the security-less-dimension, autoexists takes care of showing only those state provinces of the country the sales person belongs to.

Resulting in the following desired report:

All in all reasonably simple when you understand autoexists.

I have a report where the client wants to exclude all but 3 members below a certain level from a hierarchy.

The easiest way to achieve this is to exclude them when designing the query in BIDS, but this is much work and not easy to maintain, especially when you want to use this in multiple reports. And when this hierarchy is changed it will automatically be included even when we don’t want it.

I thendecided to create a set that includes all the members that need to be excluded, so i can use a NOT IN while designing the query. After browsing the internet i found How to exclude a certain member from a MDX call that gets all descendants of a member at a higher level. Exactly what i needed. It works like this:

Select all the members from the level i need from the hierarchy by using DESCENDANTS :

DESCENDANTS([Product].[Product Categories].[Category].&[4],
[Product].[Product Categories].[Product],
SELF)

This will select all the Products from the Category “Accessories”,

Next we want to Exclude the Products we still want in our set, we do this by using the Except function, this function lets you distract the values from one set from another:

Except(
DESCENDANTS([Product].[Product Categories].[Category].&[4],
[Product].[Product Categories].[Product],
SELF)
,  { [Product].[Product Categories].[Product].&[483]
,[Product].[Product Categories].[Product].&[486]
}
)

The { } around the products make it a set so they can be distracted using Except. This statement can be added as a Set to the Cube, and thus used in the Query to build the dataset by using NOT IN and selecting the SET.

To test this I made the following MDX statement to execute at the Query analyzer:

SELECT
{} ON COLUMNS,
{
Except(
DESCENDANTS([Product].[Product Categories].[Category].&[4],
[Product].[Product Categories].[Product],
SELF)
,{[Product].[Product Categories].[Product].&[483]
,[Product].[Product Categories].[Product].&[486]})
}
ON ROWS
from [Adventure Works]