Formula Structure

Microsoft Visual Basic.NET With OneStream API and Member Scripts

All formulas and business rules run as compiled VB.NET code. In a VB.NET function or subroutine, calls are made to specific API functions which enable the rule writer to interact with the Analytic Engine.  Specific API functions are used to process member script expressions, and create calculated values in the analytic data model.

Common Finance APIs That Use Member Scripts

api.Data.GetDataCell(“A#Cash”) 

This returns a single numeric cell value.

api.Data.GetDataCellEx(“A#Cash”) 

This returns a single numeric or text cell value.

api.Data.SetDataCell(“A#Cash”, 5.00, False) 

This saves/writes a single data cell value.

api.Data.Calculate(“A#Cash = A#Sales * 0.10”) 

This saves multiple data cells as stored values.

Basic Dynamic Cell Calculation

The following return a single cell.

Calculate Current Ratio

Return api.Data.GetDataCell("A#15000:O#Top / A#22000:O#Top")

Formula Composition:

VB.NET     Language Keyword(s)
Return

OneStream API    Function

api.Data.GetDataCell("Member Script”)

OneStream Member Script

A#15000:O#Top / A#22000:O#Top

TIP: When using a nested function (YearPrior(|POVTime|) in a GetDataCell call, you must enclose the function in square brackets []. This is necessary because GetDataCell and the functions use “(“and “)” as indicators of the start/end points in the script. The inner functions must be wrapped in square brackets.
GetDataCell("S#[|!ScenarioActual_GRT!|]:T#[YearPrior1(|CVTime|)][Period(|CVTime|)]"):Name("PYR Var")

Basic Stored Calculation

The following calculate and store multiple cells.

Pull Prior Period Retained Earnings

api.Data.Calculate("A#28000 = A#28999:T#POVPrior1")

Formula Composition

OneStream API    Function
api.Data.Calculate("Member Script”)

Member Script

A#28000=A#28999:T#POVPrior1

You can use formula variables in member scripts to significantly improve performance when the same formula is used for multiple members. When using formula variables, the formula text remains the same, so there is no need for continued parsing and evaluation.

Using variables can also improve performance if a member Id is used instead of a member name as the ID can also be used as the value in a formula variable. To use a formula variable in a member script, use a dollar sign $ instead of a pound # sign before the member name, and use the variable name after the dollar sign.

Example 1

api.Data.FormulaVariables.SetTextVariable(“variableAccount”, “8150”)
api.Data.Calculate(“A#8250=A$variableAccount * 10”)

Example 2

Dim acctMember As Member = api.Members.GetMember(DimType.Account.Id, “8150”)
api.Data.FormulaVariables.SetMemberVariable(“variableAccount”,acctMember)
api.Data.Calculate(“A#8250= A$variableAccount * 100”)

Example 3

Dim acctMember As Member = api.Members.GetMember(DimType.Account.Id, “8150”)
Dim acctId As Integer = acctMember.MemberId
api.Data.FormulaVariables.SetIntegerVariable(“myAccount”,acctId)
api.Data.Calculate(“A#8250 = A$myAccount * 1000”)