Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Access Rules and Your Model

Describes the things to consider when creating or editing access rules and how they affect your general model and your experience using the sheets, reports, and charts.

Access rules define specific intersections of data that users or groups can edit or view.

Access rules affect your model and how your team can use your model. If your instance secures just levels or just levels and accounts, expect some subtle changes in your model. If your instance also secures custom dimensions, expect some prominent changes in your model. In either case, understand the affects and use best practices for smooth planning.  

Before you read this article, read: 

To learn how to create access rules, see Create Access Rules.

Guiding Principles

Data Values:

  • In sheets, values are the same for anyone who can view the value at a specific intersection.  
  • In reports and charts, values may be different than what others view at the same intersection.

Rollups and Totals:

  • Blank totals occur in sheets when you don't have access to all contributing data at the rollup intersection.
  • Partial rollups occur only in reports when the report must display a rollup cell that you don't have access to. The total displayed contains only the contributors that you can access. 

Invalid Intersections: You might come across invalid intersections when you have access to the secured dimension values at other intersections, but not at the specific intersection. For example, you have access to Expense accounts at the Engineering level and Revenue accounts at the Sales level. Expenses at Sales is an invalid intersection for you. 

Messages: You might see various messages when you come across invalid intersections. You may be able to create intersections in reports, sheet views, and formulas that aren't valid for you. This results in errors or blank data. If you feel that you can't get to data you need to see or edit, talk to your admin about your access rules. 

Modeling and Administration Areas and Permissions

What to Know: Best Practices:
  • In general, modeling and administration permissions allow you to see the entire hierarchies of levels, accounts, and custom dimensions.
  • One exception is the publishing plans permission. This permission lets the user publish only the areas they have Edit access to.
  • To let users manage the structure of the model, the permissions alone are sufficient. You don't need to give the view or edit access to data.
  • To let users publish the entire Adaptive Insights plan to another platform, give Edit access to all secured dimension values in the model. 

See Available Permissions for details. 

Imports and Import Permissions

What to Know: Best Practices:
  • If you have the Import Capabilities > Import to All Locations permission, you can edit data at all intersections of the model through an API import or a regular import. 
  • The permission alone does not allow you to see the data intersections in sheets and reports.
  • When you start imports for modeled sheet data by spreadsheet, the Import Mode options are slightly different (see Spreadsheet Import): 
    • Add the imported rows to the sheet: Adds imported rows and leaves existing rows untouched.
    • Replace all editable rows: Replaces all existing rows with imported rows. 
    • Replace only rows with matching secured dimensions: Replaces all existing rows that match the level or any secured custom dimension. If matches aren't found, adds rows. 
    • Update existing rows: Updates specific data in existing rows that match the Import Key. Not available for sheets with Allow Splits selected.  
    • Update existing rows and add new rows: Updates specific data in existing rows that match the Import Key. If matches aren't found, adds rows. Not available for sheets with Allow Splits selected.  
To let users with this permission validate their data or meta-data imports, give them at least Full View access to all secured dimension values in the model. Otherwise, they can't see all that they imported in the sheets and reports and may think that the import failed.

Your Model with Secured Accounts

Level-Assigned Standard and Cube Sheets

Applies to sheets and Excel Interface for Planning sheets.

What to Know: Best Practices:
  • Standard and cube sheets remove intersections you can't access. As a result, rows, columns, and drop-down options disappear and reappear as you make selections and pivot the sheet.

For example, when you choose a level from the level drop-down, accounts you can't access at the level disappear from the rows. 

  • In cube sheets, when you click the Change Dimension button the options you select from the drop-downs do not filter the options available in the other drop-downs. You can potentially create invalid intersections. If all intersections are invalid, you get an error.
  • To let users view data, give them Limited View access to one level and one account on the sheet.
  • To let users view splits, give them Full View access to the account and level. 
  • To prevent users from viewing the splits, give them Limited View access to the account and level. They see the total, but not contributing splits.
  • To help your users avoid errors in cube sheets, tell them which combinations are invalid.

 

Level-Assigned Modeled Sheets

Applies to sheets and Excel Interface for Planning sheets.

What to Know: Best Practices:
  • You can give access to a modeled sheet, which includes all its accounts, or you can give access to each account, including Timespan and Initial Balance.
  • You can see the row, but you can't explore the contributing details if they include accounts that you can't access.
  • If an account is in the columns or is a display column, your access can prevent you from viewing the data or editing it. If you have no access to the account at any level, the column doesn't appear.
  • Rows with levels that you can't access disappear from the sheet for you.
  • Columns with accounts that you can't see disappear from the sheet for you.
  • If you have Full View access to the parent row, you can see all the split rows, even if the split has dimensions that you can't access. 
  • When you split rows with secured dimensions, the parent row's selected dimension locks upon save. 
  • To give or restrict access to all the accounts, enter the modeled sheet name in the access rule template. 
  • To let users open a modeled sheet, give them at least Full View access to one level and one modeled account. 
  • To let users add or delete rows, give them Edit access to modeled sheet name. 
  • To limit View access to only some of the modeled sheet's accounts, consider creating general ledger or custom accounts that link to the modeled accounts you want visible. Give users access to the linked accounts, but not the source. See the Linked and Source Account section below.
  • To prevent users from seeing levels and accounts that they can't access,  give them Limited View access to the modeled sheet accounts. They can still see the total row, but not the contributing splits.
  • To prevent confusion with parent rows, tell your team to edit the parent dimension values before adding splits. If the splits each have different values, it's best to leave the parent selection blank.

 

User-Assigned Sheets

Applies to sheets and Excel Interface for Planning sheets.

What to Know: Best Practices
Anyone with access to a user assigned sheet can view and edit all the data on that sheet unless the version is locked.  Use caution when assigning users to a user-assigned sheet. 

 

Sales Planning Refresh Linked Modeled Sheets

Applies to sheets.

What to Know: Best Practices

When you refresh linked modeled sheets, you synch the data between two instances. You need edit access to all the sheet's dimension values of the receiving instance. To validate your work, you need Full View access to all the sheet's dimension values in source instance.

  • Give users who can refresh linked modeled sheets Edit access to all the secured dimension values on the receiving sheet. Otherwise, their synch fails.
  • To let users validate their work, give them at least Full View access to all the secured dimension values on the source sheet. This way they can check it against their receiving sheet.

Consolidation Percentage Sheet

Applies to sheets.

What to Know: Best Practices
The consolidation percentage sheet is not restricted by access rules.  To let users open and edit the conslidation percentage sheet, give them a role with the Model Management Access > Consolidation permission. 

 

Time Rollup Breakback Methods

Applies to sheets.

What to Know: Best Practices:
  • If your model uses an auto-breakback assumption, anyone can trigger the auto-breakback without any access to the assumption or its terms. 
  • If you can choose from a list of assumptions, you need Limited View access to all the terms in the assumption to see it as an option. 
  • To let users choose an assumption for breakback in sheets, give them at least Limited View access to all the terms in the assumption.
  • If your breakback assumption uses terms that not everyone can access, consider using auto-breakback in the General Settings. Auto-breakback applies the formula automatically for any user. 

 

Building Reports and Charts

Applies to reports and OfficeConnect reports.

What to Know: Best Practices:
  • You need at least View access to an account at some level to build a report. 
  • In the Elements tab, you can see all the accounts that you have at least Limited View access to at some level, and you can see all the levels that you have access to.
  • Because you can drag these dimensions into the report, you can create invalid intersections that you can't access. 
  • Invalid intersections don't necessarily prevent you from running report.
  • Report cells with invalid intersections display blanks or zeros.

To help your users avoid confusion, tell them which combinations are invalid.

 

 

Viewing Shared Reports and Charts

Applies to reports, OfficeConnect reports, and charts.

What to Know: Best Practices:
  • Shared reports and charts strip out data you can't access. Shared reports and charts may look very different to each user. 
  • You may see:
    • Entire columns and rows that others can't see. 
    • Options in the parameters and filters that others can't see. 
    • Data where others see blanks and zeros. 
    • Rollups with different values.
  • Report calculations show different results per user because you can't access certain terms. The calculation filters out terms that you can't access. This can result in different numbers, partial rollups, f(x) restricted, and zeros. 
Go to Administration > General Setup and enter a message in the Data Access Message for Reports field. This message appears in the information note of all reports to help users understand how their access rules affect the data they see in reports.

 

Modifying Shared Reports

Applies to reports and OfficeConnect reports.

What to Know: Best Practices:

If you have the permission you can edit reports that other users created and shared with you.

When you run the report, dimensions and dimension values that you have no access to are stripped from the report but they persist for other users. 

When you edit the report, your access rules remove dimensions and dimension values that you don't have access to. Your saved changes also remove those dimensions for others. 

For example, the report includes the HQ, Engineering, and Marketing levels.

You only have access to Engineering. When you run the report, you only see Engineering. Behind the scenes, HQ and Marketing are still on the report. Others with access to those levels can still see them. 

For example, a shared report shows expenses for the entire organization. You only have access to Engineering expenses. When you edit the report, you only see Engineering in the builder. You don't know that other levels are on the report.  When you save the report to shared folders, behind the scenes, the report removes all levels, except Engineering for everyone. 

To avoid accidental changes to a shared report's structure, give the Modify All Reports permission to users who generally have more access rather than less access to secured dimensions in the model. 

 

Formula-Driven Data in Sheets, Reports, and Charts

Applies to sheets, Excel Interface for Planning sheets, reports, OfficeConnect reports, and charts.

What to Know: Best Practices:

If you're viewing data in your model:

  • Shared formulas, formulas entered in cells, master formulas, and calculated accounts evaluate the same value for all users, regardless of access.
  • The formula itself appears as "f(x) restricted" when you don't have at least Limited Read access to all the terms in the formula.  
N/A

 

Writing Formulas in Sheets

Applies to sheets.

What to Know: Best Practices:
  • Limitations on the levels you can reference for each account are controlled by the data privacy setting of the account. See the Data Privacy section that follows.
  • To use a term in a formula, you need at least Limited View access to the term. 
  • If you enter a term that you can't access, you get a formula error when you  save the sheet. The error persists for all users.
  • If you use Formula Assistant, you see all your accessible dimensions in the formula assistant lists. For example, your rules prevent you from viewing  the Revenue account at the Engineering level. In Formulas Assistant, you see Revenue in the account list and Engineering in the level list.  You can attempt a formula with Engineering and Revenue, but Formula Assistant will not allow you to save the formula. 
To help your users avoid errors, tell them which combinations are invalid.

 

Writing Formulas in Reports

Applies to reports and OfficeConnect.

What to Know: Best Practices:

In addition to the above information about Writing Formulas in Sheets:

  • The Display as % calculation uses the Expenses account by default. If you don't have access to Expenses at all, the formula uses the next general ledger account in your general ledger account list. You can edit the the element properties to change this.
  • Because you can create invalid intersections on the report (see the section on Building Reports), you can create report calculations that reference these invalid intersections. In these cases, the calculations treat the invalid intersection as a zero. 

N/A

Linked and Source Accounts

Applies to sheets, Excel Interface for Planning sheets, reports, OfficeConnect reports, and charts. For general information about linked accounts, see Linked Accounts Overview.

What to Know: Best Practices:
  • Linked accounts are read-only. Edit access to a linked accounts results in Full View access.
  • You don't need access to the source account to view the data in a linked account. 
  • You do need access to the source account to drill into the data of the linked account. 
  • With access to both the linked and source account, you may see different values for each account in reports. This could be due to any custom dimension filters within the linked account or on the report. It could also be a result of your access rules for each account.
  • To let users edit the data they see in linked accounts, create Edit access rules to the source account. 
  • To limit a users view of the account details and dimension breakdown, give Full or Limited View access to only the linked account, and not the source.
  • For users who have permission to build reports, give Full View access to both the linked account and the source account. Even if you restricted the source, the user can use reports to see the custom dimensional breakdown of the data in the linked account. 

 

Assumption Accounts

Applies to sheets, Excel Interface for Planning sheets, reports, OfficeConnect reports, and charts.

What to Know: Best Practices:
  • The data in assumptions is the same for all levels and editable at the top level (Only) level. See Hierarchies and Access Rules for terms. 
  • With view access to an assumption account at any one level, you can reference the assumption in a formula at any editable level. For example, you have access to the Assumption Account at the Engineering level. You also have access to some other account, say Revenue, at the Sales level. In a formula, you can reference the Assumption at either Sales or Engineering. This is because the value in the assumption is the same at all levels.  
  • To let users edit an assumption account value, you must give them access to  the account at all levels.
  • For rules that give access to the assumption account, include every level the user or group can access to provide admins with clarity.

 

System Accounts

Applies to sheets, Excel Interface for Planning sheets, reports, OfficeConnect reports, and charts.

What to Know: Best Practices:

When you have access to a system account, the values you see may differ between sheets, charts, and reports.

CTA, allocations, and elimination difference accounts follow the rules of formula-driven accounts.  To see the account's value in sheets, you don't need access to all the terms of the formula.  Reports and charts filter out the formula terms that you can't access.

N/A

 

Exchange Rate Accounts

Applies to sheets, Excel Interface for Planning sheets, reports, OfficeConnect reports, and charts.

What to Know: Best Practices:

Access to the Exchange Rate Sheet (Modeling > Exchange Rates) requires the Model Management Access > Currency permission. Access rules aren't necessary.

Access to the Exchange rates accounts on sheets, reports, and charts requires View access to the accounts. To learn how to find the account codes of the exchange rate accounts, see Create Access Rules.

N/A

 

Data Privacy Settings for Accounts

Applies to sheets, Excel Interface for Planning sheets, reports, OfficeConnect reports, and charts.

What to Know: Best Practices:
  • Data Privacy is an account setting that controls how users can reference accounts in formulas. 
  • The intersection of the account and level per rule defines access to the account data. The Data Privacy account setting defines how you can use the account in formulas.

If you don't want users to know certain levels exist, don't give them access to accounts with the Public at all levels data privacy  setting. 

Some examples of access and data privacy combinations when you have access to the Revenue account at the Sales level:

Data Privacy Setting for Revenue Outcome

Public at all levels:

Lets you reference account data from any level.

You can create formulas that reference Revenue from all levels in the model, including Engineering . 

In the formula assistant, you can see the entire level hierarchy of the model.

 

Top level only:

Lets you reference your company's total Revenue. 

You can create formulas that reference the top level rollup value of Revenue, which includes the sum of all levels.

You can't see the level hierarchy beyond your explicit access.

Private:

Default for all accounts. Lets you reference the account only from  the current level (and its descendants). 

For data at the Sales level, you can reference Revenue  from the Sales level or any of the Sales level descendants. You can't reference the accounts from any other levels. 

 

Your Model with Secured Custom Dimensions

When your model secures custom dimensions:

  • All access rules automatically include the uncategorized value of all custom dimensions.
  • All data intersects at every custom dimension. If the custom dimension isn't specified, the data intersects at the Uncategorized value of each custom dimension. 
  • All rollup values, including split rollups, intersect at the All value of each custom dimension.
  • Without access to the All value of secured custom dimensions, your capabilities are limited for formulas, accounts, sheet access, reports, and charts. 

See Hierarchies and Access Rules for information about the All and Uncategorized values. 

See Access Rule Overview for information about custom dimensions that qualify for access rules.

Consider the following best practices in addition to the ones listed above for models with just secured accounts and levels. 

Level-Assigned Standard and Cube Sheets

Applies to sheets and Excel Interface for Planning sheets.

What to Know: Best Practice:
  • Any cell that displays a rollup through a link, or a level, or account hierarchy, or splits, intersects at the All value of all custom dimensions.
  • If you don't have access to the All value of secured custom dimensions, once a row is split, you no longer see the rollup row at all.  This can make accounts and levels that you can access disappear from the sheet.

Create rules that include the All value of each secured custom dimension to let users: 

  • Add or edit splits.
  • View any split rollup or the contributing splits. 
  • View any cube and modeled accounts on the standard sheet
  • View the data in any linked accounts.

To prevent errors, tell your team members what dimension combinations are valid for them.

Use cube sheets more than standard sheets to prevent confusion. 

 

Level Assigned Modeled Sheets

Applies to sheets and Excel Interface for Planning sheets.

What to Know: Best Practice:
  • To view a row, you need Full View access to the levels, and every custom dimension value selected for that row.
  • Rows with combinations that you can't access disappear from the sheet for you.
  • In the rows, an error prevents you from selecting dimension values that create invalid intersections for you. You can try again with different values. 
  • To change secured custom dimension values on the sheet, you need access to all the modeled accounts.

To prevent errors, tell users what combinations are valid for them.

To let users change custom dimension values in rows, give them edit access to all accounts on the sheet.

 

 

 Level Assigned Modeled Sheets with Splits

Applies to sheets and Excel Interface for Planning sheets.

What to Know: Best Practice:
When a parent row has the uncategorized value of a custom dimension, all of the row's children are visible and editable. N/A

Allocations Accounts 

Applies to sheets, Excel Interface for Planning sheets, reports, OfficeConnect reports, and charts.

What to Know: Best Practice:

When allocations accounts preserve custom dimensions they intersect at the All value of the custom dimensions.

Include the All value of all custom dimensions for any rule that includes the allocation accounts.

 

Linked Accounts with Secured Custom Dimensions

Applies to sheets, Excel Interface for Planning sheets, reports, OfficeConnect reports, and charts.

What to Know: Best Practice:
  • In standard sheets, linked accounts intersect at the All value of all secured custom dimensions.
  • In cube sheets, linked accounts intersect at the Uncategorized value of any secured custom dimensions not on the sheet itself.
  • In reports and charts, the report filters the linked account data. The linked account only shows the data tagged with the custom dimension values each user can access. 

 

  • To view access to linked accounts in standard sheets, include the All value of all custom dimensions in the access rule.
  • For view access in reports to general ledger or custom linked accounts, include only the custom dimension values that you want the user to access. 
  • For view access to the linked account in cube sheets, you don't need to make any special considerations. The linked account shows data only at the uncategorized values of all custom dimensions on the sheet.
  • Source accounts often use custom dimension values to break down data. To block users from viewing the breakdown of the data, exclude the source account from their rules. They can see the source's total values through the linked account view but they won't be able to drill into the source account details. 
  • If users cannot have access to the All value of all secured custom dimensions, give them access to the source account only. This allows them to see the account data in the cube or modeled sheet only at the custom dimension values they can access. 

 

Metric Accounts

Applies to sheets, Excel Interface for Planning sheets, reports, OfficeConnect reports, and charts.

What to Know: Best Practice:
  • Metric accounts on standard sheets intersect at the All value of all custom dimensions.
  • Cube metric accounts (metric accounts in cube sheets) intersect at the Uncategorized value of any secured custom dimensions not on the cube sheet itself. For secured custom dimensions on the cube sheet, each cell intersects at the selected dimension value. 
  • To let users view metric accounts on standard or modeled sheets, include access to the All value of all secured custom dimensions.
  • If the instance has secured custom dimensions, you don't have to define the custom dimensions for the rules for modeled and cube accounts unless those custom dimensions are on the sheet. 

 

Formulas

Applies to sheets.

What to Know: Best Practice:
  • In standard and modeled sheets,  formulas you write reference the All value of all custom dimensions by default. If you don't have access to the All value, you must qualify the formula with the dimension values you have access to or you will get an error. 
  • In cube sheets, formulas you write for cube account cells that reference accounts on the same cube sheet,  imply that the dimension equals "This". 
To avoid errors and confusion, tell your team what to expect. 

 

Building and Sharing Reports and Charts

What to Know: Best Practice:

When you don't have access to All of a secured custom dimension's values:

  • You can see the custom dimension in your Elements tab. You can drag it into reports and charts.
  • When you run the report or view the chart, the element holds only the values you have access to.
  • When you run the report or view the chart, you can't drill through to transactions.

For example, you only have access to the Product dimension value, T-shirts. You drag and drop the Product dimension into the report with the Revenue account. You see the revenue for T-shirts.

When you share the report, another user has access to T-shirts and Sweaters. That user sees the Revenue total for both T-shirts and Sweaters.

  • The same logic affects report formulas. 

Encourage report and chart builders to use the entire secured custom dimension in the reports they share. The access rules filter data and calculations for each team member. 

Inform your team that they may not be able to drill into transactions from reports and charts.