Advanced Tab (Custom Dimension Security Dialog Box)
Use this tab to define a
You can specify a range of dimension levels that are accessible to the role, and, within this range, which dimension members are accessible.
This topic contains an example MDX expression for each option in this tab. For more information about these options and for more examples, see Custom Rules in Dimension Security.
The expressions you supply in this tab dynamically refresh the Basic tab if the expressions can be translated successfully. If they cannot be translated, you are notified with a message in the Basic tab. For more information, see Basic Tab (Custom Dimension Security Dialog Box).
Options
To supply MDX expressions for the following options, you can either type in the expression or click the edit (...) button to open MDX Builder.
The Allowed Members and Denied Members boxes are optional.
Top Level
Supply an MDX expression that represents the topmost accessible level. For example, in the Warehouse dimension included with Microsoft® SQL Server™ Analysis Services, to specify State Province as the top level, type the following expression:
[Warehouse].[State Province]
The top level must be above or the same as the bottom level. Members above the top level cannot be accessed even if they are included in the expression in the Allowed Members box.
Bottom Level
Supply an MDX expression that represents the bottommost accessible level. For example, in the Warehouse dimension, to specify City as the bottom level, type the following expression:
[Warehouse].[City]
The bottom level must be below or the same as the top level. Members below the bottom level cannot be accessed even if they are included in the expression in the Allowed Members box.
Allowed Members
Supply an MDX expression for the set of members that can be accessed. Descendants of these members can also be accessed unless they are above the top level, below the bottom level, or access to them is denied by the expression in the Denied Members box. The only nondescendants of these members that can be accessed are their ancestors at and below the top level.
For example, in the Warehouse dimension, to allow access to the WA and OR members, type the following expression:
{[Warehouse].[All Warehouses].[USA].[WA],
[Warehouse].[All Warehouses].[USA].[OR]}
Denied Members
Supply an MDX expression for the set of members that cannot be accessed. Descendants of these members cannot be accessed unless access to them is allowed by the expression in the Allowed Members box. Nondescendants can be accessed unless they are above the top level or below the bottom level.
For example, in the Warehouse dimension, to deny access to the Seattle, Tacoma, and Bremerton members, type the following expression:
{[Warehouse].[All Warehouses].[USA].[WA].[Seattle],
[Warehouse].[All Warehouses].[USA].[WA].[Tacoma],
[Warehouse].[All Warehouses].[USA].[WA].[Bremerton]}
Combined Example
This example demonstrates the effect of using all the previous examples in this topic together in a read permission. If a user in the role issues an MDX query that projects the Warehouse dimension on the y-axis, the data set is returned, and the user fully expands the Warehouse dimension, it would appear as follows:
Members in the (All), Country, and Warehouse Name levels are not visible because they are above the top level or below the bottom level. The only members of the State Province level that are visible are OR and WA because they are specified in the Allowed Members box. The only members of the City level that are visible are descendants of OR and WA that are not denied by the expression in the Denied Members box.