Skip to main content

References Configuration

Before you can pull data from a data model into Excel using the NecAccess formula, you need to create a reference. A reference maps a short name to a specific data model and the environment it should retrieve data from. It also tells the Add-in how to cache that data, which has a direct impact on how fast your formulas calculate.

Without a properly configured reference, the NecAccess formula has no way of knowing which data model to connect to or where to get the data from. Create one reference per data model you need to access. There is no limit on how many references a single Excel file can have.

Access References Configuration from the Add-ins tab.

Excel Add-in References Configuration

Understand references, cache, and performance​

Every reference you create feeds into the Add-in's caching system. When a formula runs for the first time, the cache stores the result. The next time Excel recalculates, the Add-in reads from the cache instead of going back to the data model. This is what makes recalculations fast.

Fewer, broader references generally perform better than many small ones. A single large cache is more efficient to build and reuse than several smaller ones running separately. In most cases, one reference per data model is the right approach. You can try different configurations to find what works best for your file, or use the Cache Optimizer to get a recommendation.

Some types of logic can slow down cache generation regardless of how your references are set up. Expect slower performance when cached fields include:

  • Exclusion logic such as Exclusion or NOT (for example, Account NOT 40000)
  • Alphanumeric ranges (for example, Company between AAAA and BBBB)
  • Date filters (for example, 2019-01-01)

Sage Intacct Real Time

If your reference uses the Sage Intacct data source, at least one dimension must match a filter used in your formula. See Sage Intacct Real Time Formula Errors for a list of errors you may encounter.

Create a reference​

The dimensions you select when creating a reference directly shape how the cache is built. Choosing the right dimensions focuses the cache on exactly the values your formulas need, which avoids unnecessary overhead and keeps calculations fast.

  1. Click Add.
  2. Select the Data Model you want to access.
  3. Choose the Environment the reference should use.
  4. Select one or more Dimensions where values are expected to change across your formulas.
  5. Enter a short, descriptive name in the Reference field.
  6. Click Ok.
  7. Select the reference and click Ok to close the window.
Example

Calculate the sum of sales for a year

Scenario

Your report calculates the Sum of Sales for a specific Product during the Current Year. When the formula runs, the cache does not just store that one result. It pre-calculates additional values in advance so future calculations load faster.

Details

Option 1 — Every product, current year only
Pre-calculates sales for every product, locked to the current year. Best when your report switches between different products.

Option 2 — Selected product, all years
Pre-calculates sales across all years for the product your formula uses. Best when your report compares data over time.

Option 3 — Every product, every year
Pre-calculates everything. Covers all possible combinations but takes the longest to generate and uses the most resources.

Keep in mind

The dimensions you select tell the cache which direction to expand. The cache pre-calculates only what your Excel file actually needs, so picking the right dimension keeps calculations fast without unnecessary overhead.

Set data model parameters​

Data model parameters let you control what data a formula retrieves without editing the formula itself. For example, you can set the value 7 for the Month parameter of the Budget Entry data model. That value is then passed to the variable $$month inside SQL scripts in the Data Model Designer. You can update parameters directly in the Add-in without logging out, and changes apply immediately the same way they do in SEI.

When to set data model parameters​

  • Adjusting results for a specific period without editing any formula directly in the Excel file.
  • Testing different scenarios by swapping parameter values to compare results across different conditions.

Steps​

  1. Select Set Data Model Parameters.
  2. Update the Value column for each parameter you want to change.
  3. To bypass the parameter screen entirely, select Skip.
  4. Click Ok to apply. This clears the cache and refreshes worksheet data.

Execute stored procedures​

Stored procedures are predefined database operations configured in SEI. You can trigger them directly from the Excel Add-in without logging out, and pass parameter values the same way you would from the main interface. This is useful when you need to run a backend operation, such as updating a budget or triggering a data refresh, as part of your Excel workflow.

When to execute stored procedures​

  • Triggering a backend operation from Excel without switching to SEI.
  • Passing specific values to a procedure to control exactly what it does when it runs.

tip

Some stored procedures support ranges. When available, the Value (To) column becomes active and lets you define an upper boundary for the parameter, for example from month 1 to month 6.

Steps​

  1. Select Execute Stored Procedure.
  2. Update the Value (From) column for each parameter you want to change. Required parameters are marked with an exclamation icon. Select the prompt icon to pick from a list of available values.
  3. If the stored procedure supports ranges, update the Value (To) column as well.
  4. To leave a value unchanged, click Skip.
  5. Click Ok to apply. This clears the cache and refreshes worksheet data.