Note that the custom settings column acquires a new ellipsis button. Clicking on this button will enable us to set new restrictions on the dimension.

Click Ok to return to the list of dimensions in the cube. Note that a custom security definition is complex and cannot be displayed in the grid for a role. The description that appears is only a reminder that there is a purpose for the custom definition. Browse the data.

Dimensions can be filtered further to include or exclude individual members from values that are visible to end users.

Click the ellipsis button in the Restricted dimensions column and select Product dimension and click the ellipsis button in the custom settings column. Leave the Select all members option selected and expand the All Product and Diary nodes. Clear the check boxes next to members. The select all option implies that all members which are not specifically excluded will be included.

The advanced tab displays the impact of the choices on the basic tab. Click the advanced tab.

Note that the Advanced tab contains MDX expressions. Selection of values based on the basic tab simply inserts MDX constant expressions into the boxes. The top two boxes can include any MDX expression that returns a valid level of the current dimension. The last two boxes includes MDX expressions that return a valid set for the current dimension. Click Ok to close the custom dialog box and browse the data. The bread category will not appear in the data displayed. However the totals appear wrong as it is calculated for all the values in the cube and hence does not match the values shown in the report. This can be overcome by selecting the enable option in the Common tab of this dialog box.

Now let us examine the Common tab in the Custom Dimension Security dialog box. Under visual totals let us click Enable.

Note that the default is not to show visual totals. Enabling visual totals determines whether displayed, aggregated, cell values are calculated according to only the viewable members, all of the dimensions members or an MDX expression. The totals will now be calculated only for the displayed values.

Often default members are created in a dimension for use as a filter in a dimension that does not have an explicit value selected. The default member for a dimension is placed within the All member level. This kind of definition enables the administrator give each group a default member of its own.

Next Page: Tutorial 66: MSAS - Applying security to a Dimension - Page 3