SQL Calcs

A SQL calc is a stored procedure you write yourself in T-SQL and keep in the Calculations list next to the formulas and calc scripts. Una compiles it, offers its parameters, and runs it through the same paths as any other calculation: the Run button, a dashboard calculation trigger and the workflow Run Calculation activity.

Use a SQL calc when the work does not fit a calc script: logic that reads relational or staging tables, writes to tables other than a model's, loops over periods, or calls other procedures. The people who run it need no database rights of their own.

Example

create or alter procedure [client].[Seed Forecast] (
    @username nvarchar(256),
    @FromScenario int,
    @ToScenario int,
    @Uplift float = 0
) as
begin
    set nocount on;

    declare @batchId uniqueidentifier = newid();
    insert into [dbo].[WM_Finance] ([BatchId], [Measure], [KpiId], [PeriodId], [ScenarioId], [S_EntityId], [Value_Float])
    select @batchId, 'V_New', f.[KpiId], f.[PeriodId], @ToScenario, f.[S_EntityId], f.[V_New] * (1 + @Uplift)
    from [dbo].[M_Finance] f
    where f.[ScenarioId] = @FromScenario;

    exec [dbo].[Appg_WriteM_Finance] @batchId;
    exec [dbo].[Appg_Audit_Finance] @batchId, @username, null, 2, 0, 'Calculation';
end

Saved as a SQL calc named Seed Forecast, this copies one scenario of the Finance model into another with an uplift, through the model's standard write path (see Writing model data). Running it asks for FromScenario and ToScenario; Uplift is optional.

Who Can Write Them

A SQL calc runs its author's SQL with the full rights of the tenant database, for whoever runs it. So creating, changing or deleting one requires the Relational Access permission (or superuser), on top of Model Management. Users without it see SQL calcs read-only and are not offered the SQL type. Running a SQL calc needs nothing more than running any other calculation.

Creating One

1. In Settings → Model Definitions → Calculations, click Add New and choose SQL.
2. Rename the calc. The name becomes the procedure's name, so it may use letters, digits, _, -, spaces and @, up to 128 characters.
3. Open it with the pencil button. A new SQL calc starts with an empty procedure of the right shape; write your code in its body.
4. Save. Saving compiles the calc: the procedure is created in the database, and any error is shown on the calc, with its line number. The Compiled column shows whether the calc compiled cleanly.

A SQL calc has no model, no scope and no dimension: those settings do not apply. Its execution type is always On Demand.

The Procedure

The text is exactly one create or alter procedure statement:

create or alter procedure [client].[<calc name>] (
    @username nvarchar(256),
    @Year int = 2026            -- any further parameters, any type
) as
begin
    set nocount on;
    -- your code here
end

Rule
One statement	No GO separators, nothing before or after the procedure.
Its name	[client].[<calc name>], matching the calc's name (case does not matter).
First parameter	@username nvarchar(256). Every run passes the user it runs as.
Other parameters	Any number, any type, with or without a default.

• Renaming a calc whose text you have edited makes it fail to compile until you change the name in the text too. While the starting text is untouched, it follows the rename.
• Deleting or renaming the calc drops its procedure. A SQL calc may take over an existing [client] procedure of the same name; from then on the procedure belongs to the calc.
• SQL Server resolves tables only when the procedure runs, so a table that does not exist is not a compile error, while a missing column of an existing table is.

Parameters

The parameters after @username are what a run asks for, by their names without @ (Year):

• A parameter without a default is required: a run that does not give it is refused.
• A parameter with a default is optional. Left empty, it takes its default.
• Values are checked against the declared type before the procedure starts: whole numbers, numbers, bit, dates and uniqueidentifier. Any other type is passed as text.
• @username is never asked for: it is the user who clicked Run, saved the widget, or started the workflow.

Parameters are plain values, not member pickers. To pass a member, take its key, for instance {Parameter.Scenario.0.Key} in a dashboard trigger. See Running Calculations.

Writing Model Data

A SQL calc can write to any table, and Una keeps no audit trail of those writes on its own. For a model's fact data, go through the model's standard write path, as calc scripts do. That way the write is audited, starts the automatic formulas that depend on it, and runs the model's custom-writeback hooks.

declare @batchId uniqueidentifier = newid();
insert into [dbo].[WM_<model>] ([BatchId], [Measure], <dimension key columns>, [Value_Float])
select @batchId, 'V_New', ... ;
exec [dbo].[Appg_WriteM_<model>] @batchId;
exec [dbo].[Appg_Audit_<model>] @batchId, @username, null, 2, 0, 'Calculation';

• Stage the cells in WM_<model> under a new batch id: one row per cell, with the dimension key columns of the fact table (KpiId, ScenarioId, PeriodId, S_<dimension>Id, …) and the value in Value_Float (Value_String for text).
• Measure is the stored measure column, usually V_New (the value a calc script writes by default). V_Value is calculated when queried and cannot be written.
• Use base member keys: a shared member's key must be replaced by the key of the member it shares.
• A row with a null value clears that cell.
• The write procedure applies only what a user could save: rows for a locked scenario are dropped silently, and so are rows for non-leaf members unless the dimension allows data on them.
• Line items go to WML_<model> (with a LIDDesc column) and Appg_WriteML_<model>, with 1 as the fifth argument of the audit call.

Write first, then audit

Call the write procedure, then the audit, for every batch. Una records the audit in the background from the batch's staging rows, after the write has applied them, so the two calls need no transaction around them. Calling the audit first still works but delays that batch's audit trail. In older versions of Una, calling the audit first could make the write save nothing.

Writing straight into M_<model> is faster for very large writes, but nothing is audited, automatic formulas do not run, and custom writeback is skipped.

After a Run

When a SQL calc finishes, Una processes the audit trail and the automatic formulas of every model, since the calc may have written to any of them.