How to create a custom rule for dimension security in a database role

To create a custom rule for dimension security in a database role

1. In the Analysis Manager tree pane, right-click the database that contains the database role, and then click Manage Roles.
   
   
2. In Database Role Manager, select the database role in which you want to create a custom rule, and then click Edit. If the database role does not yet exist, you must create it before performing this procedure. For more information, see Creating Database Roles.
   
   
3. In the Database Role dialog box, click the Dimensions tab.
   
   
4. In the row displaying the dimension and permission for which you want to create a custom rule, in the Rule column, select Custom. For more information about permissions, see Dimension Security.
   
   
5. In the same row, in the Custom Settings column, click the edit (...) button.
   
   
6. (Optional.) In the Custom Dimension Security dialog box, in the Description box, type a description of the custom rule.
   
   
7. If you want to limit access to levels or members, decide whether to use the Basic tab or Advanced tab, which provide alternative methods. The Basic tab is easier to use and satisfies most needs. The Advanced tab provides a little more flexibility, but you must write Multidimensional Expressions (MDX).
   
   
8. (Optional.) To use the Basic tab to limit access to levels or members:
   1. Click the Basic tab.
      
      
   2. (Optional.) In the Top Level box, select the topmost accessible level.
      
      
   3. (Optional.) In the Bottom Level box, select the bottommost accessible level.
      
      
   4. Select Select all members to select all members in the Members box, or select Deselect all members to clear the selection of all members in the Members box.
      
      
   5. (Optional.) In the Members box, select a check box beside a member to allow access to it. (This action also selects the member's descendants and ancestors that are visible in the Members box.) Clear a check box beside a member to deny access to it. (This action also clears the selection of the member's descendants that are visible in the Members box.)

      Important  If you limit access to levels or members, consider selecting Enable - Show visual totals in the Common tab. If instead you use the default setting for visual totals (that is, Disable - Do not show visual totals), security exposures might be created. These exposures allow end users in the role to deduce values for members to which they are denied access. For more information, see Example 4, Example 6, Example 8, and Example 9 in Custom Rules in Dimension Security.

9. (Optional.) To use the Advanced tab to limit access to levels or members, click the Advanced tab, and use one or more of the following boxes:
   • In the Top Level box, type an MDX expression that evaluates to a level that will be the topmost accessible level.
     
     
   • In the Bottom Level box, type an MDX expression that will evaluate to a level that will be the bottommost accessible level.
     
     
   • In the Allowed Members box, type an MDX expression for the set of members that can be accessed. Descendants of these members can also be accessed unless they are below the bottom level or access to them is denied by the expression in the Denied Members box. The ancestors of the allowed members will be visible at the top level.
     
     
   • In the Denied Members box, type 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.

   For more information, see Custom Rules in Dimension Security.

   Important  If you limit access to levels or members, consider selecting Enable - Show visual totals in the Common tab. If instead you use the default setting for visual totals (that is, Disable - Do not show visual totals), security exposures might be created. These exposures allow end users in the role to deduce values for members to which they are denied access. For more information, see Example 4, Example 6, Example 8, and Example 9 in Custom Rules in Dimension Security.

10. (Optional.) To control visual totals, click the Common tab, and in the Visual Totals area, do one of the following:
    • To enable visual totals at all viewable levels, select Enable - Show visual totals.
      
      
    • To disable visual totals at all viewable levels, select Disable - Do not show visual totals.
      
      
    • To enable visual totals at and above a certain level but disable them below it, select Custom - Show visual totals starting at the following level and above, and in the box below it type an MDX expression for the level. Or, instead of typing, beside the box you can click the edit (...) button to access MDX Builder, where you can select the level in the Data box and drag it to the MDX expression box.

    Note  Visual totals cannot be enabled for a cube that contains a distinct count measure. For more information, see Using Aggregate Functions.

11. (Optional.) To select a default member, click the Common tab, select Define default member and specify using MDX, and in the box below it type an MDX expression for the default member. Or, instead of typing, beside the box you can click the edit (...) button to access MDX Builder, where you can select the default member in the Data box and drag it to the MDX expression box.
    
    
12. Click OK.
    
    
13. In the Database Role dialog box, click OK.

See Also

Defining Custom Rules for Dimension Security