Formulas with secured dimensions

When a formula contains an off-sheet reference to members in a secured dimension, the formula can produce different results for restricted users than for unrestricted users. This can happen when a consolidation is run by a restricted user who does not have at least read-only access to a dimension member referenced by a formula. In this case, a null value is used during the formula evaluation.

For example, [Sales Ratio] can null and can display as a zero value in the view for this formula, unless the consolidating user has been given read-only or read-write access to [Company]:[Total Company].

[Sales Ratio] = [Sales] / [Sales] of [Company]:[Total
Company];

To work around this issue, arrange to store values that are input as global drivers or are computed for use in common formulas into a secured dimension member for which all users are allowed access. For example, add a member [Global Company] to the dimension and give all the users access to the new member [Global Company]. Then change the formula similar to this:

If MemberIs([Company]:[Global Company]) then
[Sales Ratio] = [Sales] of [Company]:[Total Company];
Else
If [Sales] of [Company]:[Total Company] >.0001 or
[Sales] or [Company]:[Total Company] < -.0001 then
[Sales Ratio] = [Sales]/[Sales Ratio] of [Company]:[Global Company]; 
EndIf

An unrestricted user first must consolidate for the member [Company]:[Global Company]. Subsequently, all other users can access the correct value from the [Company]:[Global Company] member.