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.

Contains preview content for the upcoming 2019.3 release.

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.  

Some guiding principles to help: 

  • Invalid Intersections occur 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. 
  • Partial rollups occur only in reports when you don't have access to the full rollup value, but the report must display the rollup cell. The total displayed contains only the contributors that you can access. 
  • In sheets, if you can see an intersection at all, you see the same values as anyone else who can see the intersection.
  • In reports and charts, the values you see may be different than what others see.

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. 

Before you read this article, read: 

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

Modeling and Administration Areas

Your access rules do not affect the permissions that allow you to change your model's structure. In general, modeling and administration permissions allow you to see the entire hierarchies of levels, accounts, and custom dimensions. For example, if you have modeling permissions, you may not be able to see account data in sheets, but you can still manage the settings of all accounts.

See Available Permissions for details. 

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.
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

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

What to Know: Best Practices:
  • You must give access to all a modeled sheet's accounts or none of the modeled sheets's accounts.
  • Rows with levels that you can't access 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.
  • 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 View access to one level and the modeled sheet name. 
  • 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.

 

User-Assigned 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

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

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

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

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

What to Know: Best Practices:
  • Shared reports strip out data you can't access. Shared reports 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

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 and Reports

What to Know: Best Practices:

If you're viewing data in sheets or reports:

  • 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

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

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

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

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

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

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

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 specificied, 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. 

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

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

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 prevent errors, tell users what combinations are valid for them.

 

 

 

Allocations Accounts 

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

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, 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

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

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

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.
  • When you run the report, the element holds only the values you have access to.
  • 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 builders to use the entire secured custom dimension in the reports they share. The access rules filter data and calculations for each team member. 

 

 

  • Was this article helpful?