Remove Dimension Values from Formulas in Calculated Accounts

Security: Model includes: sheets, accounts, dimensions, and formulas
permission.

You might have many reasons to remove dimension values from the formulas of calculated accounts. These scenarios are the most common reasons:

You want to delete the dimension value and there's a dependency displayed in the

Calculated Accounts

tab of the

View Dependencies

table.

You have account formula errors as a result of a deleted dimension value.

Select

Modeling

from the main menu.

Click the account hierarchy:

Custom Accounts

General Ledger Accounts

Metric Accounts

For cube and modeled calculated accounts, click

Level Assigned Sheets

User Assigned

sheets, edit the sheet, and click the accounts link.

For removal of calculated account dependencies: Make note of the account type indicated in the

Calculated Accounts

tab to locate the account hierarchy. For cube or modeled account, open the sheet indicated as the

Location

in the tab.

From the toolbar, click

Export Account Structure

From the toolbar, click the arrow next to the

Export Account Structure

and select Export Formula Overrides
.

In both exports, use the Find function of Excel to locate the find any mention of the dimension value.

For resolving dependencies, use the Find function to locate the specific account listed in the

Calculated Accounts

tab of the

View Dependencies

table.

Update the formulas and override formulas to remove the dimension value.

Return to the account hierarchy.

Import the updated structures using the

Update and Append

option for each spreadsheet.

When you're resolving dependencies, you can continue to resolve the other dependencies. When calculated accounts are the only dependency, you can delete the dimension value. See Resolve View Dependencies to Delete Dimension Values.