Model Calcs
Model calcs are calculations that Una generates from the model definition instead of being written by hand. They appear in the Calculations list with a Built-in tag, next to the model they belong to, and are read-only: their text is the generated SQL procedure, and Compile regenerates it rather than parsing it. In every other respect they are ordinary calculations — they run from the Calculations screen, a dashboard calculation trigger, a workflow Run Calculation activity or the API (see Running Calculations), and their writes are audited like any other calc's.
| Calculation | Created for | Exists when |
|---|---|---|
<Model> Retained Earnings | a standalone model | an account is tagged #retainedearnings# or #ppre# |
<Model> Currency Conversion | a standalone model | the model has a dimension with the Currency role |
<Submodel> Calculation | a relational submodel | always — it is the submodel's own calculation (sync, item and item-period passes, aggregation into the parent model) |
The first two are generated only for models whose Generate the built-in calculations setting is on (see below). The submodel calculation needs no setting; it is the procedure Relational Planning has always generated, now visible and runnable as a calc.
Model calcs are (re)generated whenever the model's code is: when the model is processed, and whenever calculations are compiled (Compile All, or Compile on the calc itself). The accounts behind the tags are resolved at generation time, so after moving a tag, compile.
Model Settings
The settings live in the model dialog (Settings → Model Definitions → Models), in the Built-in calculations section of a standalone model. A change takes effect at the next Process or Compile All.
| Setting | Values | Effect |
|---|---|---|
| Generate the built-in calculations | on / off | The switch. On for a model created after this feature; off for a model that existed before it, until you turn it on. Turning it off removes the model's built-in calcs. |
| Retained Earnings accumulation | Year (default) / Period | Year: current-period Retained Earnings is the year-to-date Net Income and rolls into prior-period Retained Earnings at year end (the year is the period's level-1 ancestor in the Time hierarchy, so fiscal years follow the hierarchy). Period: it is the period's Net Income and rolls over every period, the NetSuite way. |
| FX rates by entity | off (default) / on | Where the exchange rates are stored, see Where the rates are. Available only when the model has both a Currency and an Entity dimension. |
| Convert into | All currencies (default) / Needed currencies only | Which currencies Currency Conversion writes, see Which currencies. Available only when the model has a Currency dimension. |
Account Tags
The calculations find the accounts they read and write through tags on the members of the model's Account dimension (the Tag column in the Hierarchy Manager; a tag is written #tag#, and a member may carry several). Each tag goes on exactly one member — a duplicated tag is an error.
| Tag | Put it on | Used by |
|---|---|---|
#netincome# | the Net Income node — its leaf descendants, with their roll-up operators, are the P&L | Retained Earnings |
#retainedearnings# | the current-period Retained Earnings account (a leaf, outside Net Income) — written by the calc | Retained Earnings; its presence (or #ppre#'s) makes the calc exist |
#ppre# | the prior-period Retained Earnings account (a leaf, outside Net Income) — written by the calc | Retained Earnings |
#balancesheet# | the balance sheet node | the CTA part of Retained Earnings |
#cta# | the Cumulative Translation Adjustment account (a leaf) — written by the calc | the CTA part of Retained Earnings |
#fxavg# | the account holding the average rate of the period (a leaf) | Currency Conversion, CTA |
#fxeom# | the account holding the end-of-month rate (a leaf) | Currency Conversion, CTA |
#reporting# | a Currency member (not an account): a currency every entity is converted into when Convert into is Needed currencies only; any number of members may carry it | Currency Conversion |
Three standard member properties take part as well:
| Property | On | Used for |
|---|---|---|
| Debit Credit (Debit / Credit) | Account | the sign the Retained Earnings and CTA accounts are stored with, and the balance sheet imbalance behind the CTA. Without the property every account counts as credit. |
| General Rate Type (None / Average / Current / Historical) | Account | which rate converts each account. An account with no rate type (or None) is not converted. |
| Local Currency (a Currency member) | Entity | the currency an entity keeps its books in; required for the global rate table (FX rates by entity off). |
The seven tags are in the reserved-tag catalogue, so they are offered when editing a member's tags.
Retained Earnings
<Model> Retained Earnings keeps the two Retained Earnings accounts in step with Net Income for every scenario, period and combination of the model's other dimensions:
- Net Income is the sum of the leaves under
#netincome#, each with the operator that rolls it up to the node. - Current-period RE accumulates Net Income over the current bucket (the year, or the period); prior-period RE holds everything before it. The two always add up to all Net Income through the period.
- The run starts from the period before the first period in range, so a mid-year recalculation picks up the stored balances. A scenario with no Retained Earnings before the range starts from the prior-period RE stored at its first period — the loaded opening balance.
- Values are stored with each account's Debit/Credit sign; zero results are not stored.
When the model has a Currency dimension and #cta#, #balancesheet# and #fxeom# are set, the calc also writes the CTA per scenario, period, entity and reporting currency: the balance sheet imbalance of the converted view less the local imbalance translated at the end-of-month rate (the imbalance is the debit-positive sum over the balance sheet leaves and the two RE accounts). Without those tags the CTA is skipped, and the calc says so in its warnings.
Parameters: @scenarioId and @periodFromId (required), then @entityId and @currencyId when the model has those dimensions (0 = all). A non-leaf member means its leaf descendants. The run ends at the scenario's End Period, or at its last period with data when it has none. Locked scenarios are skipped.
Currency Conversion
<Model> Currency Conversion converts values from the Currency dimension's default write member (the local currency) to every other currency, account by account according to its General Rate Type:
| Rate type | Converted value |
|---|---|
| Average | the period's local value × the period's average rate (#fxavg#) |
| Current | the period's local value × the period's end-of-month rate (#fxeom#) |
| Historical | a running balance: the previous period's converted value + the change in the local balance × the period's average rate, carried through to the scenario's end — a balance that is locally gone keeps its converted value |
The rate, Retained Earnings and CTA accounts are never converted. A missing rate converts nothing. Zero results are not stored.
Parameters: @scenarioId (required), @periodFromId (0 = the first period), @periodToId (null = the scenario's End Period; historical accounts always run to the end), then one parameter per other dimension — @accountId, @entityId, @currencyId (the target currencies) and @<Dimension>Id for the rest (0 = all).
Which currencies
The Convert into setting decides which currencies each entity is converted into. Converting into every currency multiplies the data by the number of currencies, and most of it nobody reads: a parent reads its children in the parent's currency, not in every currency there is.
| Convert into | Each entity is converted into |
|---|---|
| All currencies (default) | every currency in scope but the local one — what the calc always did |
| Needed currencies only | its own currency (its Local Currency), the currency of every parent above it along every chain (every hierarchy instance when the hierarchy is effective-dated), and every Currency member tagged #reporting#. A member without a Local Currency adds nothing, so an entity with no currency anywhere on its chain is converted into the tagged currencies alone; without an Entity dimension the tagged currencies are the whole set, and at least one is required. |
Switching to Needed clears the converted values in the currencies that are no longer needed at the next run — the model holds exactly what the calc produces. Switching back regenerates them. A currency that becomes needed later (an entity moved under a parent in another currency, a new #reporting# tag) has no converted history: run the conversion from the first period after such a change, as after adding a currency.
Run Currency Conversion before Retained Earnings: the Retained Earnings and CTA of a currency view build on the converted values.
Both calcs run to the scenario's End Period (a month start runs from that month to the end of the horizon, not just that month). Keep the property current: after a year rollover, a scenario whose End Period still says last December stops both calcs there, and newly loaded periods are neither converted nor rolled into Retained Earnings until the End Period is advanced.
Where the rates are
Rates are the values of the #fxavg# and #fxeom# accounts, by scenario and period, stored at the default write member of every dimension other than Currency and Entity.
| Model | A rate is stored at | Rate used for entity e → currency T |
|---|---|---|
| FX rates by entity on | entity e × currency T: from e's local currency to T | the stored value |
| FX rates by entity off | the default Entity member × currency C: from the global currency G (that member's Local Currency) to C | triangulated as R(T) ÷ R(L), with L = e's Local Currency; R(G) = 1; an entity without a Local Currency is taken to be in G |
| no Entity dimension | currency T: from the model's single local currency | the stored value |
Submodel Calculation
<Submodel> Calculation is the calculation of a relational submodel: it syncs the item-period rows, runs the item and item-period passes and aggregates the mapped measures into the parent model. Its parameters are one @<dimension field> per keyed dimension (0 = all), @startPeriodId and @endPeriodId (empty = every period), so a run with no parameters calculates the whole submodel. A submodel whose definition cannot be built — a formula that does not parse, a procedure measure naming a procedure that does not exist — shows the problem as the calc's error, and cannot run until it is fixed.
Running Model Calcs
- From the Run button of the Calculations screen, a dashboard calculation trigger or a workflow Run Calculation activity: the parameters above are offered as member pickers, one per dimension. See Running Calculations.
- Retained Earnings and Currency Conversion need a scenario (and Retained Earnings a starting period): a run without them is refused. In a dashboard trigger, give them from the dashboard's filters, for instance
{Parameter.Scenario.0.Key}. The submodel calculation needs nothing: with no parameters it calculates the whole submodel. - Both standalone calcs write straight to the fact table, the way a
direct_writecalc script does: cells they no longer produce are cleared, unchanged cells are not written, and one audit operation is recorded per run.
When Something Is Off
- A calc that cannot be generated stays in the list with its errors (a missing or duplicated tag, a Retained Earnings account under Net Income, a missing property) and cannot run. Fix the model, then Compile.
- Warnings are kept on the calc and do not stop it: accounts with no rate type, balance sheet accounts with no Debit/Credit, entities with no Local Currency, a CTA skipped for missing tags.
- When a model has the switch on but a calc does not apply — no Retained Earnings tag at all, no Currency dimension — nothing is registered, and Process says why in the notification of the "Generating Model Code" step (for instance Cannot generate the Retained Earnings calculation: no Account member is tagged #retainedearnings# or #ppre#).
Customizing
Each standalone model calc looks for three optional stored procedures in the tenant's client schema and calls them with its own parameters: Before_<stem>_<Model> runs first, <stem>_<Model> runs instead of the generated calculation, and After_<stem>_<Model> runs last, where <stem> is CalcRE or ConvertCurrency and <Model> is the model name with spaces replaced by underscores. The submodel calculation keeps the Relational Planning hooks (BeforeItem_, InsteadItem_, AfterItem_, BeforeAgg_, InsteadAgg_, AfterAgg_). A calculation may not be given one of these names.