Finance Rules

Writing Calculations

Finance Business Rules store calculated results into the cube. This guide covers the two primary calculation techniques, the difference between Calculate and CustomCalculate, and when to choose Business Rules over Member Formulas.

Stored Calculation Techniques

The api.Data.Calculate Approach

The simplest way to write a stored calculation is the Calculate method. It accepts a formula-style string that reads like an equation:
1' Simple formula — calculates Profit as Sales minus Costs
2api.Data.Calculate("A#Profit = A#Sales - A#Costs")
3
4' You can reference multiple dimensions using the standard POV syntax
5api.Data.Calculate("A#NetRevenue:F#Input = A#GrossRevenue:F#Input - A#Discounts:F#Input")
This approach is concise and easy to read. Use it when your calculation can be expressed as a straightforward formula.

The Get/Set Data Buffer Approach

For more complex scenarios — conditional logic, intermediate calculations, or working with data cell-by-cell — use the Data Buffer pattern:
1' Step 1: Get data into a buffer using a formula expression
2Dim myBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("A#Sales - A#Costs")
3
4' Step 2: Register the buffer as a formula variable
5api.Data.FormulaVariables.SetDataBufferVariable("myBuffer", myBuffer, False)
6
7' Step 3: Use the variable in a Calculate expression
8api.Data.Calculate("A#Profit = $myBuffer")
You can also inspect and manipulate individual cells in a buffer before writing them back:
1Dim salesBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("A#Sales")
2For Each cell As DataBufferCell In salesBuffer.DataBufferCells.Values
3  ' Apply a 10% adjustment to each cell
4  cell.CellAmount = cell.CellAmount * 1.1D
5Next
6api.Data.FormulaVariables.SetDataBufferVariable("adjusted", salesBuffer, False)
7api.Data.Calculate("A#AdjustedSales = $adjusted")

When to Use Each

api.Data.CalculateGet/Set Data Buffer
Best forSimple formulas and direct expressionsComplex logic, conditional adjustments, loops
ReadabilityVery readable, formula-like syntaxMore verbose but more flexible
PerformanceOptimized internally by the engineSlightly more overhead due to buffer operations
Use whenThe calculation is a straight formulaYou need to inspect or transform data before storing

Calculate vs. CustomCalculate

Both Calculate and CustomCalculate are used for stored calculations, but they fire at different points and serve different purposes.
Calculate runs inside the Data Unit Calculation Sequence (DUCS). Every time an entity is calculated or consolidated, your Calculate logic fires. This is the standard approach for most calculations.
CustomCalculate runs outside the DUCS on a separate pass. It was designed for Planning projects with large volumes of calculations where you need more control over when and how calculations execute. CustomCalculate can be triggered from:
  • A Data Management Custom Calculate step — the most common approach for running calculations on demand
  • A Dashboard Parameter Component server task action
  • A Forms Event Handler (e.g., when a user clicks Save on a Form in Workflow)

Running CustomCalculate from Data Management

The Custom Calculate Data Management step lets you run a Finance Business Rule's CustomCalculate function on a specific slice of data without triggering a full Calculate or Consolidation. This is particularly useful for What-if analysis — a user can make changes in a Form, run the Custom Calculate step, and immediately see the results.
The step has two key properties:
  • Business Rule / Function Name — the Finance Business Rule and the function name to execute. Your rule matches on args.CustomCalculateArgs.FunctionName to run the correct logic.
  • POV Settings — non-Data Unit dimensions (View, Account, Flow, Origin, IC, UD1–UD8) that can be referenced in your rule via api.Pov. This makes your rule reusable across multiple steps with different POV configurations.
Because CustomCalculate runs outside the normal sequence, you must manually clear previously calculated data at the top of your rule to avoid stale results:
1If api.FunctionType = FinanceFunctionType.CustomCalculate Then
2  If args.CustomCalculateArgs.FunctionName.XFEqualsIgnoreCase("CalcSalary") Then
3      ' Clear previously calculated data before recalculating
4      api.Data.ClearCalculatedData(True, True, True, True, "A#SalaryExpense")
5
6      ' Your calculation logic here
7      api.Data.Calculate("A#SalaryExpense = A#Headcount * A#AvgSalary")
8  End If
9End If
⚠️Warning
Data saved by a Custom Calculate step will be cleared during a normal Calculate or Consolidation unless it is saved with Durable storage. Pass True as the second argument to api.Data.Calculate to use Durable storage (e.g., api.Data.Calculate("A#Profit = A#Sales - A#Costs", True)). Durable data persists through standard calculations and is only cleared by an explicit ClearCalculatedData call.
ℹ️Info
Use Calculate for standard calculation needs. Reserve CustomCalculate for Planning scenarios where you need to trigger calculation passes independently — such as on-demand What-if analysis from a Dashboard or Form — or where sequential control over calculation order is critical.

Business Rules vs. Member Formulas

OneStream offers two approaches to writing calculations — Business Rules and Member Formulas. Understanding when to use each is important:
Business RulesMember Formulas
LocationCentralized in the Business Rule libraryWritten directly on individual members
Best forCross-dimensional dependencies, complex sequential logic, custom translation/consolidationStandard calculations tied to specific members
MaintenanceAll logic in one place, easier to review holisticallyDistributed across members, closer to the data they affect
PerformanceRuns for each Data Unit (entity) in the workflow profile's scopeScoped to the member automatically
Drill-downRequires separate CalcDrillDownMemberFormula logicDrill-down built in
💡Tip
The primary reasons for writing formulas in a Finance Business Rule rather than a Member Formula are: the formula requires extensive cross-dimensional dependencies, it requires complex sequential logic with variables or conditional statements affecting multiple dependent calculations, or the application requires custom algorithms for Currency Translation, Share, or Intercompany Eliminations.
💡Tip
When writing Member Formulas, remember to set the Formula Type property on the member. Dynamic calculation members also need DynamicCalc set on both the Formula Type and Account Type properties. Forgetting these settings is a common source of "my formula isn't running" issues.

The Member Formula Skeleton

Member formulas are VB.NET only — you write them in the UI formula editor on a dimension member. OneStream wraps your code in auto-generated boilerplate; you only edit what goes inside the Try block. The boilerplate differs depending on whether the formula is a stored calculation or a dynamic calculation.
For comparison, the Finance Business Rule skeleton is covered in Getting Started with Finance Rules.

Stored Calculation Member Formula

A stored calculation member formula writes data into the cube during calculation. The entry point is a Sub (void) — it does not return a value.
vbnet
1' ── Auto-generated (you do not edit this) ──────────────────────────
2Imports System
3Imports System.Data
4Imports System.Data.Common
5Imports System.IO
6Imports System.Collections.Generic
7Imports System.Globalization
8Imports System.Linq
9Imports Microsoft.VisualBasic
10Imports OneStream.Shared.Common
11Imports OneStream.Shared.Wcf
12Imports OneStream.Shared.Engine
13Imports OneStream.Shared.Database
14Imports OneStream.Stage.Engine
15Imports OneStream.Stage.Database
16Imports OneStream.Finance.Engine
17Imports OneStream.Finance.Database
18
19Namespace <auto-generated>
20  Public Class MainClass
21      Public Sub Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs)
22          Try
23' ── Your code ──────────────────────────────────────────────────────
24
25              api.Data.Calculate("A#Profit = A#Sales - A#Costs")
26
27' ── Auto-generated (you do not edit this) ──────────────────────────
28          Catch ex As Exception
29              Throw New XFException(si, ex)
30          End Try
31      End Sub
32  End Class
33End Namespace
Key points:
  • Public Sub Main — void, no return value. Writes data via api.Data.Calculate or api.Data.SetDataCell.
  • No DivideByZeroException catch — you handle divide-by-zero yourself if needed.
  • Same FinanceRulesApi and FinanceRulesArgs parameters as Finance Business Rules.
ℹ️Info
In the formula editor you only see and edit the code between Try and Catch. The surrounding boilerplate is generated automatically.

Dynamic Calculation Member Formula

A dynamic calculation member formula computes a value on the fly every time a cell is viewed. The entry point is a Function that must return the displayed value.
vbnet
1' ── Auto-generated (you do not edit this) ──────────────────────────
2Imports System
3Imports System.Data
4Imports System.Data.Common
5Imports System.IO
6Imports System.Collections.Generic
7Imports System.Globalization
8Imports System.Linq
9Imports Microsoft.VisualBasic
10Imports OneStream.Shared.Common
11Imports OneStream.Shared.Wcf
12Imports OneStream.Shared.Engine
13Imports OneStream.Shared.Database
14Imports OneStream.Stage.Engine
15Imports OneStream.Stage.Database
16Imports OneStream.Finance.Engine
17Imports OneStream.Finance.Database
18
19Namespace <auto-generated>
20  Public Class MainClass
21      Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object
22          Try
23' ── Your code ──────────────────────────────────────────────────────
24
25              Return api.Data.GetDataCell("A#Sales - A#Costs")
26
27' ── Auto-generated (you do not edit this) ──────────────────────────
28          Catch dbzEx As DivideByZeroException
29              Return 0.0
30          Catch ex As Exception
31              Throw New XFException(si, ex)
32          End Try
33          Return 0.0
34      End Function
35  End Class
36End Namespace
Key points:
  • Public Function Main(...) As Object — must return a value (the displayed cell value).
  • Catch dbzEx As DivideByZeroException — auto-catches divide-by-zero and returns 0.0.
  • Return 0.0 after End Try — fallthrough if your code doesn't explicitly Return.
⚠️Warning
Dynamic calc formulas execute every time a cell is viewed. Keep logic lightweight — avoid expensive API calls or deep loops.

Helper Functions in Member Formulas

Both formula types support helper functions via the ##XFHelperFunctions## separator. Code above the marker goes inside Main; code below becomes class-level methods on MainClass.
vbnet
1' Main formula code
2Return CalcGrossMargin()
3
4##XFHelperFunctions##
5
6Private Function CalcGrossMargin() As Decimal
7  Dim sales As Decimal = api.Data.GetDataCell("A#Sales").CellAmount
8  If sales <> 0 Then
9      Return (api.Data.GetDataCell("A#GrossProfit").CellAmount) / sales
10  End If
11  Return 0D
12End Function
💡Tip
Helper functions can reference si, globals, api, and args directly — they are instance methods on the same MainClass.

Summary: Business Rules vs. Member Formulas

Business RuleStored Calc FormulaDynamic Calc Formula
LanguageVB.NET or C#VB.NET onlyVB.NET only
Entry pointFunction Main(...) As ObjectSub Main(...)Function Main(...) As Object
ReturnsObject (typically Nothing)Nothing (void)Object (displayed value)
Divide-by-zeroYou handle itYou handle itAuto-catch, returns 0.0
FallthroughReturns your valueN/AReturns 0.0
HelpersClass methods##XFHelperFunctions####XFHelperFunctions##

Related Content