Key Functions

The list below contains the most commonly used functions; however, this is not the complete list of all available functions.  Download the OneStream API Overview Guide and OneStream API Details and Database Documentation from MarketPlace for detailed Business Rule engine background, an API guide and information on each database.

Account

Name

Function

Description

Account Type

Public Function GetAccountType(MemberId As Integer) As AccountType

myAccountType = api.Account.GetAccountType(MemberId)

Retrieves the Account type for the Member.

Get Cell Account Type

GetCellLevelAccountType

Retrieves the Account type of the data cell based on its Account and flow settings.

Get Formula Type

Public Function GetFormulaType(MemberId As Integer) As FormulaType

myFormulaType = api.Account.GetFormulaType(MemberId)

Returns the Formula Type if the Account is calculated.

Get Plug Account

Public Function GetPlugAccount(MemberId As Integer) As Member
myMember = api.Account.GetPlugAccount(MemberId)

Retrieves the plug Account.

Consolidation

Name

Function

Description

Calculate

Public Sub Calculate(Formula As String, Optional onBeforeSetDataBuffer As BeforeSetDataBufferDelegate, Optional userState As Object, Optional arg0 As String, Optional arg1 As String, Optional arg2 As String, Optional arg3 As String, Optional arg4 As String, Optional arg5 As String, Optional arg6 As String, Optional arg7 As String)

api.Data.Calculate(Formula, onBeforeSetDataBuffer, userState, arg0, arg1, arg2, arg3, arg4, arg5, arg6, arg7)

Executes a calculation for a specifically qualified Point of View.

Execute Default Elimination

Public Sub ExecuteDefaultElimination()

api.ExecuteDefaultElimination()

Puts data into the Elimination Member of the Consolidation Dimension.

Second Pass Eliminations

Boolean argument for use in FinanceFunctionType.Calculate rules

Dim bValue As Boolean = args.CalculateArgs.IsSecondPassEliminationCalc

Used to calculate Data Units where Entity members are sibling members.  Used to ensure source Entities are fully calculated at Eliminations

Execute Default Share

Public Sub ExecuteDefaultShare()

api.ExecuteDefaultShare()

Puts data into the Share Member of the Consolidation Dimension.

Execute Default Translation

Public Sub ExecuteDefaultTranslation(fxRuleTypeRevenueExp As FxRuleType, rateForRevenueExp As Decimal, fxRuleTypeAssetLiab As FxRuleType, rateForAssetLiab As Decimal)

api.ExecuteDefaultTranslation(fxRuleTypeRevenueExp, rateForRevenueExp, fxRuleTypeAssetLiab, rateForAssetLiab)
or
api.ExecuteDefaultTranslation()

Calculates translation by bypassing consolidation and Business Rules.

Is Consolidated

Public Function IsConsolidated(MemberId As Integer) As TriStateBool

myTriStateBool = api.Account.IsConsolidated(MemberId)

Reveals if the Account is consolidated.

Percent Consolidated

Public Function PercentConsolidation(Optional EntityId As Integer, Optional ParentId As Integer, Optional varyByScenarioTypeId As Integer, Optional varyByTimeId As Integer) As Decimal

myDecimal = api.Entity.PercentConsolidation(EntityId, ParentId, varyByScenarioTypeId, varyByTimeId)

The percent that an Entity contributes to a Relationship.

Translated Currency Member

Public Function IsForeignCurrencyForEntity(Optional EntityId As Integer, Optional consId As Integer) As Boolean

myBoolean = api.Cons.IsForeignCurrencyForEntity(EntityId, consId)

Returns if the Consolidation Dimension Member is a translated currency Member. Used in translation rules.

Data

Name

Function

Description

Allocation

Use Journals for allocations

Allocates data across Dimensions (Entities, User Defined Dimensions, Accounts, etc.) with configurable weighting, all through Journals that can be previewed, are generated, are posted and can be unposted.

Convert Data Buffer

api.Data.ConvertDataBuffer

Modifies Dimension Members for the cells in a Data Buffer using mapping.

Convert Data Buffer Extended Members

api.Data.ConvertDataBufferExtendedMembers

Automatically aggregates the data for extended Members in order to create data cells for Parent Members that are Base-Level Members in the destination Dimensions.  This is used when copying data from a source Data Buffer created in another Cube or Scenario where one or more Dimensions have been extended.

Get Data Buffer (working with Data Units)

Public Function GetDataBuffer
(scriptMethodType
As DataApiScriptMethodType,
sourceDataBufferScript
As String, expressionDestinationInfo As ExpressionDestinationInfo) As DataBuffer

myDataBuffer = api.Data.GetDataBuffer
(scriptMethodType, sourceDataBufferScript, expressionDestinationInfo)

Retrieves a Data Unit's values during a consolidation, calculation, or translation.

Get Data Buffer Using Formula

Dim myDataBufer As DataBuffer = api.Data.GetDataBufferUsingFormula
("A#Sales-A#Costs)

Use an entire math expression to calculate a final data buffer.

Get Stored and Dynamically Calculated Values

Public Function GetDataCell(Formula As
String,
Optional arg0 As String, Optional arg1 As
String,
Optional arg2 As String, Optional arg3 As
String,
Optional arg4 As String, Optional arg5 As
String,
Optional arg6 As String, Optional arg7 As
String)
As DataCell

myDataCell = api.Data.GetDataCell(Formula,
arg0, arg1, arg2, arg3, arg4, arg5, arg6, arg7)

Retrieves the data contained in an intersection of the Cube. If this is a dynamically calculated value, the calculation will be run on the fly before returning the value.

Has Cell Notes or Data Attachments

api.Data.GetDataCell(Formula, Parameters).CellStatus.HasAttachment

Indicates whether the data cell has notes or file attachments. (Boolean)  To retrieve the data attachment, query the appropriate Member of the View Dimension (Annotation, AuditComment, Footnote, VarianceExplanation, Assumptions).

Has No Data

api.Data.GetDataCell(Formula, Parameters).CellStatus.IsNoData

Indicates if the cell has no data. (Boolean)

Has Valid Data

api.Data.GetDataCell(Formula,Parameters).
CellStatus.DataIsTimeLogicDerived

Indicates if the data in a cell contains valid data that was derived from time logic. (Boolean)

Get Data Buffer for Custom Share Calculation

Public Function
GetDataBufferForCustomShareCalculation
(Optional cubeId As Integer, Optional entityId
As Integer, Optional ParentId As Integer,
Optional scenarioId As Integer, Optional
timeId As Integer, Optional viewId As Integer)
As DataBuffer

 

myDataBuffer = api.Data.
GetDataBufferForCustomShareCalculation
(cubeId, entityId, ParentId, scenarioId, timeId,
viewId)

Use this function to assist in Custom Consolidations

Get Data Buffer for Custom Elim Calculation

Public Function
GetDataBufferForForCustomElimCalculation
(Optional includeICNone As Boolean, Optional includeICPartners As Boolean, Optional combineImportFormsAndAdjConsolidatedInto
Elim As Boolean, Optional cubeId As Integer,
Optional
entityId As Integer, Optional ParentId As Integer,
Optional scenarioId As Integer, Optional timeId
As Integer, Optional viewId As Integer)
As DataBuffer

 

myDataBuffer = api.Data.
GetDataBufferForForCustomElimCalculation(includeICNone, includeICPartners, combineImportFormsAndAdjConsolidatedInto
Elim, cubeId, entityId, ParentId, scenarioId,
timeId, viewId)

Use this function to assist in Custom Calculations

Rounding

Use the VB.NET function for rounding: 
Round(expression[, numdecimalplaces])

Controls the level of rounding that is used.

Set and Clear Data

Public Sub SetDataCell(Memberscript As
String, amount As Decimal, isNoData As
Boolean)

api.Data.SetDataCell(Memberscript, amount,
isNoData)

Sets data to a certain value.

Show Cell Text

Data Attachment Members in the View
Dimension: Annotation, Assumptions, AuditComment, Footnote and
VarianceExplanation

Use these View Members to display data attachment text within a Cube View, which can be used to edit text or display on Reports.

Variance

((A-B)/Abs(B))

Returns a Variance, but does not consider Account Type

BetterWorse Difference

Revenue Accounts:((A-B/Abs(B)))

Expense Accounts: ((-1*A)-(-1*B))

Returns a Variance based on the Account Type.

VariancePercent

((A-B) / Abs(B)) * 100

Returns a Variance Percent but does not consider Account Type.

BetterWorse

Percent

Revenue Accounts: ((A-B) / Abs(B)) * 100

Expense Accounts: (((-1 * A) – (-1 * B))/Abs(B))
* 100

Returns a Variance Percentage based on the Account Type.

Unbalanced Math: Add, Subtract, Multiply, and Divide

Api.Data.Calculate(“A#TargetAccount =
AddUnbalanced(A#60000,A#41000:O#Top,
O#Top)”)

Required to perform math using two Data Buffers where additional dimensionality must be specified for the second Data Buffer.

Entity

Name

Function

Description

Default Currency

Public Function GetLocalCurrency(Optional EntityId As Integer) As Currency

myCurrency = api.Entity.GetLocalCurrency(EntityId)

Retrieves the assigned Currency for the Entity or Parent.

Is Descendant

Public Function IsDescendent(dimPk As DimPk, ancestorMemberId As Integer, descendentMemberId As Integer, dimDisplayOptions As DimDisplayOptions) As Boolean

myBoolean = api.Members.IsDescendent(dimPk, ancestorMemberId, descendentMemberId, dimDisplayOptions)

Returns if the Member is a Descendant of another Member. (Boolean)

Is Intercompany

Public Function IsIC(Optional EntityId As Integer) As Boolean

myBoolean = api.Entity.IsIC(EntityId)

Returns if the Entity or Account Member is an intercompany Member.

Flow

Name

Function

Description

Switch Sign

Public Function SwitchSign(MemberId As Integer) As Boolean

myBoolean = api.Flow.SwitchSign(MemberId)

Flow Dimension only. Responds as to whether credits are switched to debits for the specified Member for Revenue / Expense Accounts. (Boolean)

Switch Type

Public Function SwitchType(MemberId As Integer) As Boolean

myBoolean = api.Flow.SwitchType(MemberId)

Flow Dimension only. Responds as to whether Account types are switched for the current or specified Member. This can drive translating this Member by a different FX Rate Type. (Boolean)

FX

Name

Function

Description

Currency Type

Public Function GetDefaultCurrencyId(Optional CubeId As Integer) As Integer

myInteger = api.Cubes.GetDefaultCurrencyId(CubeId)
or
Public Function GetCurrency(currencyName As String) As Currency

myCurrency = api.Cons.GetCurrency(currencyName)

Retrieves the currency type for the Cube or the Consolidation Dimension Member.

Current Exchange Rate

Public Function GetStoredFxRate(fxRateType As FxRateType, Optional timeId As Integer, Optional sourceCurrencyId As Integer, Optional destCurrencyId As Integer) As FxRate

myFxRate = api.FxRates.GetStoredFxRate(fxRateType, timeId, sourceCurrencyId, destCurrencyId)

Retrieves the current exchange rate for the specified Entity.

Exchange Rate Calculated

Public Function GetCalculatedFxRate(fxRateType As FxRateType, timeId As Integer) As Decimal

myDecimal = api.FxRates.GetCalculatedFxRate(fxRateType, timeId)

Calculates the exchange rate from the default currency to another.

Get FX Rate Type for Asset / Liability Accounts

Public Function GetFxRateTypeForAssetLiability(Optional CubeId As Integer, Optional ScenarioId As Integer) As FxRateType

myFxRateType = api.FxRates.GetFxRateTypeForAssetLiability(CubeId, ScenarioId)

Retrieves the default Rate Type for Asset and Liability Accounts in this Cube or Scenario (overrides Cube value).

Get FX Rate Type for Revenue/ Expense Accounts

Public Function GetFxRateTypeForRevenueExp(Optional CubeId As Integer, Optional ScenarioId As Integer) As FxRateType

myFxRateType = api.FxRates.GetFxRateTypeForRevenueExp(CubeId, ScenarioId)

Retrieves the default Rate Type for Revenue and Expense Accounts in this Cube or Scenario (overrides Cube value).

Get FX Rule Type for Asset / Liability Accounts

Public Function GetFxRuleTypeForAssetLiability(Optional CubeId As Integer, Optional ScenarioId As Integer) As FxRuleType

myFxRuleType = api.FxRates.GetFxRuleTypeForAssetLiability(CubeId, ScenarioId)

Retrieves the default translation Rule Type for Asset and Liability Accounts in this Cube or Scenario (overrides Cube value).

Get FX Rule Type for Revenue/ Expense Accounts

Public Function GetFxRuleTypeForRevenueExp(Optional CubeId As Integer, Optional ScenarioId As Integer) As FxRuleType

myFxRuleType = api.FxRates.GetFxRuleTypeForRevenueExp(CubeId, ScenarioId)

Retrieves the default translation Rule Type for Revenue and Expense Accounts in this Cube or Scenario (overrides Cube value).

Translate

Public Sub Translate(sourceDataBufferScript As String, destDataBufferScript As String, fxRuleType As FxRuleType, rate As Decimal)

api.Data.Translate(sourceDataBufferScript, destDataBufferScript, fxRuleType, rate)

Performs the translation method assigned to the Cube or Scenario (Periodic or Direct).

Journals

Name

Function

Description

Journal Postings Allowed

Public Function AllowAdjustments(Optional EntityId As Integer, Optional varyByScenarioTypeId As Integer, Optional varyByTimeId As Integer) As Boolean

myBoolean = api.Entity.AllowAdjustments(EntityId, varyByScenarioTypeId, varyByTimeId)

Results determine if Journal postings are allowed for the Member.

Journal Postings from Children Allowed

Public Function AllowAdjustmentsFromChildren(Optional EntityId As Integer, Optional varyByScenarioTypeId As Integer, Optional varyByTimeId As Integer) As Boolean

myBoolean = api.Entity.AllowAdjustmentsFromChildren(EntityId, varyByScenarioTypeId, varyByTimeId)

Results determine if Journal postings from children are allowed for this Member.

Member

Name

Function

Description

Base Members

HasChildren = false, e.g. A#Root.Children(HasChildren=False)

Determines if the Member is a base Member.

Get Member ID

api.Members.GetMember(dimTypeId, MemberName).MemberPk.MemberID
or

api.POV.Dimension.Memberpk.Memberid

Retrieves the name for the selected Member.

Get Member Name

api.Members.GetMember(dimTypeId, MemberName).Name

Retrieves the name for the selected Member.

Member Lists

See section of documentation on Creating Member Lists

Retrieves the Members from a named list stored in a Business Rule.

Member Name

api.POV.AccountDim, api.POV.EntityDim, etc

Retrieves the Member name.

Member Name or ID

Get Member name from Member ID:
Public Function GetMember(dimTypeId As Integer, MemberId As Integer) As Member

myMember = api.Members.GetMember(dimTypeId, MemberId)

Get Member ID from Member name:
Public Function GetMember(dimTypeId As Integer, MemberName As String) As Member

myMember = api.Members.GetMember(dimTypeId, MemberName)

Retrieves the Member for the specified ID number or name.

Top Member

api.Account.GetTopMemberForDimType(AccountMemberId, dimTypeForTopMember)

Retrieves the top Member of the selected Dimension.

Is Base

Public Function IsBase(dimPk As DimPk, ancestorMemberId As Integer, baseMemberId As Integer, Optional dimDisplayOptions As DimDisplayOptions) As Boolean

 

myBoolean = api.Members.IsBase(dimPk, ancestorMemberId, baseMemberId, dimDisplayOptions)

Determines whether the POV Member is the Base of a defined Member.

Get First Common Parent

Public Function GetFirstCommonParent(dimPk As DimPk, topMostMemberId As Integer, MemberIdA As Integer, MemberIdB As Integer, Optional dimDisplayOptions As DimDisplayOptions) As Member

 

myMember = api.Members.GetFirstCommonParent(dimPk, topMostMemberId, MemberIdA, MemberIdB, dimDisplayOptions)

Returns the first common Parent between multiple Members. 

Scenario

Name

Function

Description

Scenario Consolidation View

Public Function GetConsolidationView(Optional ScenarioId As Integer) As ViewMember

myViewMember = api.Scenario.GetConsolidationView(ScenarioId)

Determines if the Scenario's Consolidation View is set to YTD or Periodic.

Scenario Default View

Public Function GetDefaultView(Optional ScenarioId As Integer) As ViewMember

myViewMember = api.Scenario.GetDefaultView(ScenarioId)

Retrieves the Scenario's Default View.

Scenario Input Frequency

Public Function GetInputFrequency(Optional ScenarioId As Integer) As Frequency

myFrequency = api.Scenario.GetInputFrequency(ScenarioId)

Retrieves the Scenario's Input Frequency.

Status

Name

Function

Description

Calc Status

Public Function GetCalcStatus(Optional CubeId As Integer, Optional EntityId As Integer, Optional ParentId As Integer, Optional consId As Integer, Optional ScenarioId As Integer, Optional timeId As Integer) As CalcStatus

myCalcStatus = api.CalcStatus.GetCalcStatus(CubeId, EntityId, ParentId, consId, ScenarioId, timeId)

Retrieves the calculation status for the data intersection.

Impact Calc Status

Public Sub ImpactCalcStatus(Optional CubeId As Integer, Optional EntityId As Integer, Optional ParentId As Integer, Optional consId As Integer, Optional ScenarioId As Integer, Optional timeId As Integer)

api.CalcStatus.ImpactCalcStatus(CubeId, EntityId, ParentId, consId, ScenarioId, timeId)

Changes the status of the specified Data Unit to impact.

Time

Name

Function

Description

MemberId for the Month to which a Week belongs

Dim timeIdForMonth As Integer = BRApi.Finance.Time.ConvertIdToClosestIdUsingAnotherFrequency(si, timeIdForWeek, Frequency.Monthly)

Weekly applications only: This determines to which month a specific week belongs.  Used within a Finance Business Rule.

Functions

Name

Function

Description

Days Sales Outstanding

Dim cell As DataCell = api.Functions.GetDSODataCell(acctsReceivableMember, salesMember)

Calculates Days Sales Outstanding (see below).

Dynamic Simulation of Consolidation

api.Functions.GetEntityAggregationDataCell(memberScript as string, Optional useStoredAmountsWhereCalcStatusIsOK as Boolean, Optional fxRateTypeRevenueExpOverride as String, Optional fxRuleTypeRevenueExpOverride as String, Optional fxRateTypeAssetLiabOverride as String, Optional fxRuleTypeAssetLiabOverride as String)

Pseudo/approximate consolidation of a data cell (see Dynamic Simulation of Consolidation)

GetStage or GetCustom

Download the OneStream API Overview Guide from MarketPlace for examples:

GetStageBlendTextUsingCurrentPOV

GetStageBlendText

GetStageBlendNumberUsingCurrentPOV

GetStageBlendNumber

GetStageBlendDataTableUsingCurrentPOV

GetStageBlendDataTable

GetCustomBlendDataTableUsingCurrentPOV

GetCustomBlendDataTable

Several functions that retrieve text and calculate values by reading values from the OneStream Stage or a custom relational table.