Extender: Auto Create Member
This snippet will create a new Account member, including setting some properties that can vary by Scenario Type and/or Time. Note: SaveMemberInfo does not create entries in Audit tables, which means the Audit Metadata report will not contain anything related to this operation. For this reason, we do not recommend to use this snippet outside of implementation activities or in production environments. 'Create a new MemberInfo object with its child objects. Dim objMemberPk As New MemberPk(DimType.Account.Id, DimConstants.Unknown) 'Update Dim Name accordingly Dim objDim As OneStream.Shared.Wcf.Dim = BRApi.Finance.Dim.GetDim(si, "<Dimension Name>") 'Create New Member Dim objMember As New Member(objMemberPk, _ "<New Member Name>", "<Member Description>", objDim.DimPk.DimId) 'Create VaryingMemberProperties object Dim objProperties As New VaryingMemberProperties( _ objMemberPk.DimTypeId, objMemberPk.MemberId, DimConstants.Unknown) 'Create new member info object for new member Dim objMemberInfo As New MemberInfo( _ objMember, objProperties, Nothing, objDim, DimConstants.Unknown) 'Modify some member properties. Account dimension, in this example. Dim accountProperties As AccountVMProperties = objMemberInfo.GetAccountProperties() accountProperties.AccountType.SetStoredValue(AccountType.Revenue.Id) accountProperties.Text1.SetStoredValue( _ ScenarioType.Unknown.Id, DimConstants.Unknown, "MyNewText1Value") 'Save the member and its properties. Dim isNew As TriStateBool = TriStateBool.TrueValue BRApi.Finance.MemberAdmin.SaveMemberInfo(si, objMemberInfo, True, True, False, isNew)2KViews3likes0CommentsFinance Dynamic Calc: Annotations Consolidations
This DynamicCalc formula consolidates Annotations for entities in a hierarchy. 'Cube View definition 'Row Definition: E#[Total GolfStream].tree 'Col1 Definition: V#Annotation:Name("Comment") 'Col2 Definition: V#Annotation:UD8#DynamicTextCons:Name("Consolidated") 'UD8 Member setup 'Name: DynamicTextCons <-- If this is changed it must be updated in the CV Col2 definition above. 'Formula Type: DynamicCalc 'Allow Input: True 'Is Consolidated: False 'In Use: True 'Assign the formula below to the UD8 member to show the consolidated text Dim iEntityID As Integer = api.pov.Entity.MemberId Dim iETestID As Integer Dim strETestName As String = string.empty Dim eTest As Member Dim sSave As String = string.empty Dim sSource As String = string.empty If api.View.IsAnnotationType Then If api.Entity.HasChildren() Then For Each etest In api.Members.GetDescendents( _ api.Dimensions.GetBaseDim(dimtypeid.Entity).DimPk, iEntityID) iETestID = etest.MemberId sSource = api.Data.GetDataCellEx("U8#None:E#[" & etest.Name & "]:C#" & _ api.Entity.GetLocalCurrency(iETestID).Name).DataCellAnnotation If Not sSource.Equals(String.Empty) Then sSave = sSave.Trim & "; " & eTest.Name & ": " & sSource End If Next Else sSource = api.Data.GetDataCellEx($"U8#None:E#[{api.pov.entity.Name}]").DataCellAnnotation If Not sSource.Equals(String.Empty) Then sSave = ";" & sSource End If End If End If If Not sSave.Equals(String.Empty) Then Return right(sSave, len(sSave)-1).Trim Else Return Nothing End If Source: Snippet Editor: Finance - Common Account - DynamicCalc2KViews4likes0CommentsExtender: Auto Update Member Property
This snippet will modify a Member property that can vary by Scenario Type and/or Time. Just pass the relevant ScenarioType ID or Time member ID to set it in a more specific way; it will then appear as a "Stored Item" in the interface. Note: SaveMemberInfo does not create entries in Audit tables, which means the Audit Metadata report will not contain anything related to this operation. For this reason, we do not recommend to use this snippet outside of implementation activities or in production environments. 'Get the MemberInfo object for the member you want to update, in this example an Account. Dim objMemberInfo As MemberInfo = BRApi.Finance.Members.GetMemberInfo( _ si, DimType.Account.Id, "<Member Name>", True) ' Retrieve member properties so we can modify them. Dim accountProperties As AccountVMProperties = objMemberInfo.GetAccountProperties() ' change the Account Type accountProperties.AccountType.SetStoredValue(AccountType.Revenue.Id) ' change default Text1 value ' if you want to set it for a specific ScenarioType and/or time, ' use the relevant values in the first 2 parameters accountProperties.Text1.SetStoredValue( _ ScenarioType.Unknown.Id, DimConstants.Unknown, "<UpdatedValue>") 'Save the member and its properties. Dim isNew As TriStateBool = TriStateBool.TrueValue BRApi.Finance.MemberAdmin.SaveMemberInfo(si, objMemberInfo, False, True, False, isNew)2KViews8likes0CommentsAggregating a tagged set of accounts (e.g. Balance Sheet only)
This is an example to obtain values for a Cash Flow formula for all accounts tagged with a certain Text1 field. For every Account tagged CF_AR (Cash Flow - Accounts Receivable) and evaluating the Flow member Activity Calc, if the Activity Calc and the Account Tag in UD1 match, the number is added to the data buffer. Values are then sign-flipped by multiplying them by -1. There are two main approaches, depending on the version you're working with. For releases 6.0 and later, we can leverage GetDataBufferUsingFormula to produce performant code that is easy to read. For older releases, we have to use a more complex approach via the Eval function. GetDataBufferUsingFormula - v6.0 and above ' retrieve the account ID once, outside the loop, to avoid multiple lookups Dim cfArId as Integer = api.Members.GetMemberId(DimType.Account.Id, "CF_AR") ' create the result buffer Dim targetBuf As New DataBuffer() ' retrieve source data, limited to tagged accounts with a complex filter Dim sourceBuf As DataBuffer = api.Data.GetDataBufferUsingFormula( _ "RemoveNoData(FilterMembers(F#ActivityCalc, [A#BalanceSheet.Base.Where(Text1 = CF_AR)]))") ' loop through each cell For Each cell As DataBufferCell In sourceBuf.DataBufferCells.Values ' copy cell into new one, so we can safely tweak it Dim newCell As New DataBufferCell(cell) ' change the account newCell.DataBufferCellPk.AccountId = cfArId ' Stuff cell into target buffer. ' The last parameter ensures multiple values are summed up, ' rather than replacing each other targetBuf.SetCell(si, newCell, True) Next ' assign target buffer to a variable we can reference in Calculate api.Data.FormulaVariables.SetDataBufferVariable("myBuf", targetBuf, False) ' actually save the buffer into the database, against the target destination, while flipping signs. api.Data.Calculate("A#CF_AR:F#CF_Activity = $myBuf * -1") Note: this sample aims to strike a balance between code complexity, flexibility, and performance. More performant approaches are possible: you could modify the Flow ID also, and flip sign, inside the loop; then just saving results with api.Data.SetDataBuffer, rather than going through a Calculate call. That would be less flexible to integrate with other requirements though (e.g. performing other operations before saving, which can be easily done in Calculate but not with buffer objects). Eval - v.5.x and below Formula The Calculate call is straightforward. Notice the second parameter; that must be the name of our Helper Function. api.Data.Calculate("A#CF_AR:F#CF_Activity = EVAL(F#ActivityCalc) * -1", AddressOf onEvalDataBuffer) Helper Function This can be reused over and over, so it might be better placed in a shared Business Rule for ease of maintenance. Private Sub OnEvalDataBuffer(ByVal api As FinanceRulesApi, ByVal evalName As String, ByVal eventArgs As EvalDataBufferEventArgs) Dim cfAccountId As Integer = api.Members.GetMemberId(DimType.Account.Id, "CF_AR") Dim cfFlowId As Integer = api.Members.GetMemberId(DimType.Flow.Id, "CF_Activity") 'Retrieve the target accounts, and keep their IDs in a Dictionary Dim ms As String = "A#BalanceSheet.Base.Where(Text1 = CF_AR)" Dim members As List(Of MemberInfo) = _ api.Members.GetMembersUsingFilter( _ api.Pov.AccountDim.DimPk, ms, Nothing) Dim memberlookup As New Dictionary(Of Integer, Object) If Not members Is Nothing Then For Each memInfo As MemberInfo In members memberlookup.Add(memInfo.Member.MemberId, Nothing) Next End If 'Loop over cells that match our "TEXT1" account filter Dim resultCells As New Dictionary(Of DataBufferCellPk, DataBufferCell) For Each sourceCell As DataBufferCell In eventArgs.DataBuffer1.DataBufferCells.Values ' Ignore NoData cells and where the account is not tagged If(Not sourceCell.CellStatus.IsNoData) And _ memberLookup.ContainsKey(sourceCell.DataBufferCellPk.AccountId) Then ' We don't assign the original cell object to output buffer, ' but an edited copy Dim cashflowCell As New DataBufferCell(sourceCell) cashflowCell.DataBufferCellPk.AccountId = cfAccountId cashflowCell.DataBufferCellPk.FlowId = cfFlowId Dim existingCell As DataBufferCell = Nothing If resultCells.TryGetValue(cashflowCell.DataBufferCellPk, existingCell) Then 'Since there is already a cell in the dictionary, ' replace it with the aggregated amount. existingCell.CellAmount = existingCell.CellAmount + sourceCell.CellAmount Else 'Add this dataCell to the new dictionary. resultCells.Add(cashflowCell.DataBufferCellPk, cashflowCell) End If End If Next 'Assign the new list of DataCells to the result. eventArgs.DataBufferResult.DataBufferCells = resultCells End Sub1.5KViews2likes0CommentsExtender: Rename a dimension
This snippet will rename a dimension. Note: RenameDim does not create entries in Audit tables, which means the Audit Metadata report will not contain anything related to this operation. For this reason, we do not recommend to use this snippet outside of implementation activities or in production environments. Note: renaming a dimension might break references in exported artefacts. Dim originalDim As String = "Original Dimension Name" '<-- Dimension name to be changed Dim updatedDim As String = "New Dimension Name" '<-- Updated Dimension name Dim objDimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, originalDim) BRApi.Finance.Dim.RenameDim(si, objDimPk, originalDim, updatedDim)1.2KViews0likes0CommentsFinance: Return on Equity
Efficiency Measure: Return on equity measures a corporation's profitability by revealing how much profit a company generates with the money shareholders have invested. Formula: ROE= Net Inocme/ Average Shareholders Equity 'Calculate Return on Equity 'Formula: ROE= Net Income/ Average Shareholders Equity Return api.Data.GetDataCell("Divide(A#[YourNetIncomeAccount], A#[YourShareholdersEquityAccount]:V#YearAvgToDate)") Source: Snippet Editor: Finance - Common Accounts - Efficiency Measures1.1KViews1like0CommentsFilter IC Dimension by Entity Property
A common requirement for reporting is to be able to filter the IC dimension by some property that exists only on the original Entity members. This can be achieved with a custom Member List defined in a Finance business Rule. Select Case api.FunctionType ' MemberListHeaders support is optional but good practice Case Is = FinanceFunctionType.MemberListHeaders Dim mListHeaders As New List(Of MemberListHeader) ' add the name of your list: mListHeaders.Add(New MemberListHeader("withText1")) Return mListHeaders ' Here we do the real work Case Is = FinanceFunctionType.MemberList If args.MemberListArgs.MemberListName.XFEqualsIgnoreCase("withText1") Then ' this list of members will be populated later Dim ICs As New List(Of Member) ' amend parameters as necessary here Dim dimensionName as String = "CorpEntities" Dim memberFilter as String = "E#Root.Base.Where(Text1 <> '')" ' filter the Entity dimension by some criteria Dim entities As List(Of MemberInfo) = brapi.Finance.Members.GetMembersUsingFilter(si, _ brapi.Finance.Dim.GetDimPk(si, dimensionName), _ memberFilter, _ True) ' retrieve IC members corresponding to the selected Entity members ' and push them into output list For Each entityMInfo As MemberInfo In entities if entityMInfo.getEntityProperties().isIC then ICs.Add(brapi.Finance.Members.GetMember(si, dimtypeId.IC, entityMInfo.Member.Name)) end if Next ' wrap with the MemberList object and return Return New MemberList(New MemberListHeader("withText1"), ICs) This can then be referenced in CubeViews and elsewhere like this:1.1KViews7likes0CommentsFinance: Annualized Revenue
This Dynamic Calc formula calculates Annualized Revenue, by taking 3 months of Total Revenue, dividing it down to a monthly amount, then multiplying it by 12. ' "Prior Scenario" must be stored as Text1 property in current Scenario Dim priorScenario As String = api.Scenario.Text(1) Dim objTimeMemberSubComponents As TimeMemberSubComponents = BRApi.Finance.Time.GetSubComponentsFromName( _ si, api.Pov.Time.Name) Dim periodNum As String = "M" & objTimeMemberSubComponents.Month.ToString Select Case periodNum Case "M1" Return api.Data.GetDataCell( _ "((A#TOTREV:U1#Top:V#Periodic:I#Top" & _ $" + A#TOTREV:U1#Top:V#Periodic:I#Top:S#{priorScenario}:T#PovPrior1" & _ $" + A#TOTREV:U1#Top:V#Periodic:I#Top:S#{priorScenario}:T#PovPrior2)" & _ " / 3) * 12") Case "M2" Return api.Data.GetDataCell( _ "((A#TOTREV:U1#Top:V#Periodic:I#Top" & _ " + A#TOTREV:U1#Top:V#Periodic:I#Top:T#PovPrior1" & _ $" + A#TOTREV:U1#Top:V#Periodic:I#Top:S#{priorScenario}:T#PovPrior2)" & _ " / 3) * 12") Case Else Return api.Data.GetDataCell( _ "((A#TOTREV:U1#Top:V#Trailing3MonthTotal:I#Top / 3) * 12)") End Select1KViews0likes0CommentsFinance: Daily Revenue
An account that calculates Daily Revenue by taking three months of Total Revenue and dividing it down to a daily amount 'Calculate Daily Revenue 'Get Text1 priority from current Scenario to learn which Scenario to copy prior data from Dim priorScenario As String = api.Scenario.Text(api.Pov.Scenario.MemberPK.MemberId, 1) Dim srcPov As String = "A#YourTotalRevenueAccount:F#Top:I#Top:V#Periodic" Dim srcInputs As String = String.Empty Select Case Right(api.Pov.Time.Name, 2) Case "M1" srcInputs = srcPov & ":S#" & priorScenario & ":T#PovPriorYearM11 +" & _ srcPov & ":S#" & priorScenario & ":T#PovPriorYearM12 +" & _ srcPov Case "M2" srcInputs = srcPov & ":S#" & priorScenario & ":T#PovPriorYearM12 +" & _ srcPov & ":T#PovPrior1 +" & _ srcPov Case Else srcInputs = srcPov & ":T#PovPrior2 +" & _ srcPov & ":T#PovPrior1 +" & _ srcPov End Select api.Data.Calculate("A#YourDailyRevenueAccount:F#None:I#None:V#YTD = (((" & srcInputs & ")/3)*12/365)")994Views0likes0CommentsFinance: Return on Assets
Efficiency Measure: An indicator of how profitable a company is relative to its total assets. It gives an idea as to how efficient management is at using its assets to generate earnings. Formula: ROA = Net Income / Average Total Assets 'Calculate Return on Assets 'Formula: ROA = Net Income / Average Total Assets Return api.Data.GetDataCell("Divide(A#[YourNetIncomeAccount], A#[YourTotalAssetsAccount]:V#YearAvgToDate)") Source: Snippet Editor: Finance - Common Accounts - Efficiency Measures880Views1like0Comments
Filter IC Dimension by Entity Property
A common requirement for reporting is to be able to filter the IC dimension by some property that exists only on the original Entity members. This can be achieved with a custom Member List defined in a Finance business Rule. Select Case api.FunctionType ' MemberListHeaders support is optional but good practice Case Is = FinanceFunctionType.MemberListHeaders Dim mListHeaders As New List(Of MemberListHeader) ' add the name of your list: mListHeaders.Add(New MemberListHeader("withText1")) Return mListHeaders ' Here we do the real work Case Is = FinanceFunctionType.MemberList If args.MemberListArgs.MemberListName.XFEqualsIgnoreCase("withText1") Then ' this list of members will be populated later Dim ICs As New List(Of Member) ' amend parameters as necessary here Dim dimensionName as String = "CorpEntities" Dim memberFilter as String = "E#Root.Base.Where(Text1 <> '')" ' filter the Entity dimension by some criteria Dim entities As List(Of MemberInfo) = brapi.Finance.Members.GetMembersUsingFilter(si, _ brapi.Finance.Dim.GetDimPk(si, dimensionName), _ memberFilter, _ True) ' retrieve IC members corresponding to the selected Entity members ' and push them into output list For Each entityMInfo As MemberInfo In entities if entityMInfo.getEntityProperties().isIC then ICs.Add(brapi.Finance.Members.GetMember(si, dimtypeId.IC, entityMInfo.Member.Name)) end if Next ' wrap with the MemberList object and return Return New MemberList(New MemberListHeader("withText1"), ICs) This can then be referenced in CubeViews and elsewhere like this:
