Skip to main content

Calc Scripts

Calc scripts are used to perform complex, parametrized calculations. Conceptually, a calc script consists of an optional parameter declaration followed by one or more statements.

Example​

param @scenario, @dept

scope ([Scenario].&[@scenario], [Period].[2025].LeafDescendants, [Account].[Risk])
  freeze ([Department].&[@dept])
    [New] = myfunction(([Account].[Balance], [Market].SMB), [Account].[CreditLimit].Parent * 1.2) 
               - [Account].[Balance].Ancestor(2).Children.First * 0.2
  end freeze
end scope

Parameters​

If the script supports parameters, it must start with the keyword param followed by a list of parameters.

Statements​

After the optional parameter declaration, a calc script can contain one or more statements. There are five statements supported: Scope, Freeze, Dense, Sparse, and Assignment.

Scope​

Scope is a compound statement that limits the scope of enclosed statements:

scope (set_exp [, set_exp...])
  statement...
end scope

The scope keyword introduces in parentheses a list of set expressions which will limit the scope of the enclosed statements. Each set expression in the list must refer to a different dimension.

Embedded scope statements are regressive—each statement will further restrict the calculation:

scope ([Period].[2025].SelfAndDescendants)
  scope ([Period].[*].LeafDescendants)
     -- inner statements will apply to Jan 2025 through Dec 2025
  end scope
end scope

Embedded scope statements for different dimensions are equivalent to a single scope statement listing all dimensions:

-- These are equivalent:
scope ([Account].[Test])
  scope ([Scenario].[Actual])
    scope([Period].[Jan 2025])
       -- do something
    end scope
  end scope
end scope

scope ([Account].[Test], [Scenario].[Actual], [Period].[Jan 2025])
  -- do something
end scope

Freeze​

Freeze is a compound statement that protects certain cells from being updated:

freeze (set_exp [, set_exp...])
  statement...
end freeze

The freeze keyword introduces in parentheses a slice of the model which will remain unaffected by the subsequent statements.

Example:

scope ([Period].[2025].LeafDescendants, [Account].[Test])
  freeze([Period].[Q1 2025].Children, [Market].[SMB])
    [New] = 1
  end freeze
end scope

Embedded freeze statements are cumulative—each subsequent freeze statement adds another model slice that is frozen. For this reason, unlike scope statements, one freeze statement that refers to multiple dimensions is NOT equivalent to multiple embedded freeze statements.

Dense/Sparse​

Controls how assignments handle cells:

dense dimension[, dimension...]
sparse dimension[, dimension...]

Dimensions listed in the dense statement will become dense for the rest of the scope or until listed in a sparse statement. All dimensions are by default assumed to be sparse until listed in a dense statement.

See the Assignment section below for an explanation of dense vs. sparse behavior.

Assignment​

Assignment statements calculate values:

measure = expression

The expression can use:

Important differences from formulas:

  • Member references must include the dimension name, e.g., [Account].[Test] or [@Measure].[Opening]
  • Dimensions can be referenced by role, using an @ prefix, e.g., [@Time]
  • Member references can use keys, e.g., [Account].&[1], and tags, e.g., [Account].#[netincome]
  • Tuples can be truly cross-dimensional, e.g., ([Account].[Rent], [Period].[2015])

Assignments are executed for all cells in scope simultaneously. If the expression references cells that are updated by the statement, the old value of the cell will be used for calculation.

Dense vs. Sparse Assignments​

When all dimensions are sparse (the default), assignments are only performed on cells that already exist in the model—similar to an SQL UPDATE statement that only updates existing records.

Example:

scope([Scenario].[Actual], [Period].[Jan 2026], [Account].[Rent])
  [New] = 1000
end scope

If there is no data in the database for Actual scenario and Jan 2026 period, the statement will do nothing.

To force creation of cells, set one or more dimensions to dense. Cells will be created for all in-scope members for dense dimensions, but only where data already exists for the sparse dimensions.

scope([Scenario].[Actual], [Period].[Jan 2026], [Account].[Rent])
  dense [Period]
  [New] = 1000
end scope

This will create new records for the Rent account, Actual scenario in period Jan 2026, but only where data exists in the Rent account, Actual scenario for the combination of members from all other dimensions.

Warning

Do NOT set every dimension to dense. This will attempt to create a very large number of cells in the database, take a very long time, and likely result in a server crash.

For example, if you have 100 markets, 100 departments, 100 regions, and 1000 products, setting all dimensions to dense could attempt to create 1 billion records in the fact table.