Here is the main Finance Business Rule for the Dynamic Calc which is referenced from the Member Formula:
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
Imports System.Diagnostics
Imports System.Text.RegularExpressions
Imports System.Threading
Imports System.Threading.Tasks
Imports System.Collections.Concurrent
Namespace OneStream.BusinessRule.Finance.XF_DynamicExtensibilityReporting
Public Class MainClass
#Region "Not in use"
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object
Try
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
#End Region
Public Function DynamicExtensibilityReporting (ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi) As Decimal
Try
If api.Entity.HasChildren AndAlso api.Pov.Cube.CubeReferences.Values.Count > 0 'Dynamic calc is run at Parent Entity Level for the TOP Consolidation Cube
Dim ParentEntity As String = api.Pov.Entity.Name
Dim PovTime As String = api.Pov.Time.Name
'Retrieve Current UD8 Text Properties:
Dim U8Text1 As String = api.UD8.Text(api.Pov.UD8.MemberId,1,api.Pov.ScenarioTypeId,api.Pov.Time.MemberId)
Dim DimTag As String = ""
Dim FixedSourcePOV As String = ""
If U8Text1.StartsWith("DimTag=",stringcomparison.CurrentCultureIgnoreCase) Then
DimTag = StringHelper.SplitString(U8Text1,"=")(1)
DimTag = StringHelper.RemoveEnclosingSquareBrackets(DimTag)
End If
Dim DynamicPOV As New Dictionary (Of String, String) From {{"A#",$"{api.pov.Account.Name}"}, {"F#",$"{api.pov.Flow.Name}"}, {"O#",$"{api.pov.Origin.Name}"}, {"I#",$"{api.pov.IC.Name}"}, {"U1#",$"{api.pov.UD1.Name}"}, {"U2#",$"{api.pov.UD2.Name}"}, {"U3#",$"{api.pov.UD3.Name}"}, {"U4#",$"{api.pov.UD4.Name}"}, {"U5#",$"{api.pov.UD5.Name}"}, {"U6#",$"{api.pov.UD6.Name}"}, {"U7#",$"{api.pov.UD7.Name}"}, {"U8#","None"}}
'Gets the Source Data Buffer POV
Dim DimSourcePov As String = Me.GetSourceDataBufferPOV(si,globals,api,DimTag,DynamicPOV)
'Creates data buffer where we store the Aggregated data for all base Entities from the given Parent Entity:
Dim AggEntityData As New DataBUffer()
'Data is pulled from all base Entities by Extended Dim as defined in U8 Text1 (DimTag=<>)
If Globals.GetObject($"{ParentEntity}{PovTime}{DimSourcePov}") Is Nothing
Dim BaseEntityList As List(Of Member) = api.Members.GetBaseMembers(api.Pov.EntityDim.DimPk,api.Pov.Entity.MemberId)
AggEntityData = Me.AggregateEntityDataBuffer(si,api,BaseEntityList,DimSourcePov)
Globals.SetObject($"{ParentEntity}{PovTime}{DimSourcePov}",AggEntityData)
Else
AggEntityData = globals.GetObject($"{ParentEntity}{PovTime}{DimSourcePov}")
End If
'Defines Target CellPK to pull from the Aggregated Data Buffer - Pull cell by Extended Dim Member:
Dim GetTargetCellPK As DataBufferCellPk = Me.GetTargetDataBufferCellPk(si,globals,api,DimTag)
If Not AggEntityData.GetCell(si,GetTargetCellPK) Is Nothing 'If Target Cell Exists within AggDataBuffer we pick it and return the Cell Amount:
Return AggEntityData.GetCell(si,GetTargetCellPK).CellAmount
Else 'If Cell Does not exist within the Data Buffer, aggregate values through Extended Dim:
Dim DoesBufferExist As Boolean = False
api.Data.FormulaVariables.GetDataBuffer($"{ParentEntity}{PovTime}AggEntityData",DoesBufferExist)
If Not DoesBufferExist Then
api.Data.FormulaVariables.SetDataBufferVariable($"{ParentEntity}{PovTime}AggEntityData",AggEntityData,True)
End If
Dim FilteredAggEntityData As DataBuffer = api.Data.GetDataBufferUsingFormula($"FilterMembers(${ParentEntity}{PovTime}AggEntityData, [{DimTag}{DynamicPOV(DimTag)}.base])") 'ie: DataBufferFilter = [U1#api.pov.UD1.name.base]
Dim TotalCellAmount As Decimal = Decimal.Zero
TotalCellAmount += Aggregate DriverCell In FilteredAggEntityData.DataBufferCells.Values Into Sum(DriverCell.CellAmount)
Return TotalCellAmount
End If
Else 'If its base Entity, return base product cell:
Return api.Data.GetDataCell("U8#None").CellAmount
End If
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Public Function AggregateEntityDataBuffer(ByVal si As SessionInfo, ByVal api As FinanceRulesApi, ByVal DataUnitList As List(Of Member), ByVal SourceFormulaString As String, ByVal Optional DataBufferFilters As String = "" ) As DataBUffer
' Aggregates All Data Units (Entities) based in a DataUnitList and provided Filters
'
' Version Date Author Comment
' -------------------------------------------------------------------------------------------------------------
' V1.0 25-04-2024 A.Formenti Initial Version
'
' Changes:
'
'______________________________________________________________________________________________________________
Try
Dim ResultBuffer As New DataBuffer()
Dim TargetCurrency As Currency = api.Entity.GetLocalCurrency(api.Pov.Entity.MemberId)
For Each CalcEntity As Member In DataUnitList
'Get the FX Rate based on the Entity and Account POV:
Dim SourceCurrency As Currency = api.Entity.GetLocalCurrency(CalcEntity.MemberId)
Dim rateFx As Decimal = 1
Dim rateType As New FxRateType
Dim ruleType As New FxRuleType
If sourceCurrency.Id <> targetCurrency.Id Then
Dim accountType As String = api.Account.GetAccountType(api.Pov.Account.MemberId).Name
If accountType.XFEqualsIgnoreCase("Asset") OrElse accountType.XFEqualsIgnoreCase("Liability")
rateType = api.FxRates.GetFxRateTypeForAssetLiability(DimConstants.DefaultPOV, DimConstants.DefaultPOV)
ruleType = api.FxRates.GetFxRuleTypeForAssetLiability(DimConstants.DefaultPOV, DimConstants.DefaultPOV)
Else If accountType.XFEqualsIgnoreCase("Revenue") OrElse accountType.XFEqualsIgnoreCase("Expense")
rateType = api.FxRates.GetFxRateTypeForRevenueExp(DimConstants.DefaultPOV, DimConstants.DefaultPOV)
ruleType = api.FxRates.GetFxRuleTypeForRevenueExp(DimConstants.DefaultPOV, DimConstants.DefaultPOV)
End If
rateFx = api.FxRates.GetCalculatedFxRate(rateType, DimConstants.DefaultPOV, DimConstants.DefaultPOV, sourceCurrency.Id, targetCurrency.Id)
End If
api.Data.FormulaVariables.SetDecimalVariable("FXRate",rateFx)
Dim SourceDataBuffer As New DataBuffer
Dim AggDataBuffer As New DataBuffer
If api.Pov.View.Name.XFEqualsIgnoreCase("YTD") AndAlso ruleType.Name.XFEqualsIgnoreCase("Periodic") 'If View is YTD and Rule Type Periodic - Apply Periodic Translation
If sourceCurrency.Id <> targetCurrency.Id Then 'If needs translation, translate on the fly with Periodic Method:
Dim TimeList As List (Of MemberInfo) = api.Members.GetMembersUsingFilter(api.Pov.TimeDim.DimPk, $"T#{api.pov.time.name}.AllPriorInYearInclusive")
For Each sTime As MemberInfo In TimeList
rateFx = api.FxRates.GetCalculatedFxRate(rateType, DimConstants.DefaultPOV, sTime.Member.MemberId, sourceCurrency.Id, targetCurrency.Id)
api.Data.FormulaVariables.SetDecimalVariable("FXRate",rateFx)
Dim SourceDataBufferByTime As DataBuffer = api.Data.GetDataBufferUsingFormula($"FilterMembers(RemoveZeros(V#Periodic:T#{sTime.Member.Name}:E#{CalcEntity.Name}:P#[{api.Pov.Parent.name}]:C#Local:{SourceFormulaString}) * $FXRate, {DataBufferFilters})",,True)
'Converts Data Buffer to get Aggregated Extended Members:
Dim AggDataBufferByTime As DataBuffer = api.Data.ConvertDataBufferExtendedMembers(api.Pov.Cube.name,CalcEntity.Name,api.Pov.Scenario.Name,SourceDataBufferByTime)
'Accumulates both Base and Extended Members Data Buffers
For Each AggCell As DataBufferCell In AggDataBufferByTime.DataBufferCells.Values
If Not SourceDataBufferByTime.DataBufferCells.ContainsKey(AggCell.DataBufferCellPk)
SourceDataBufferByTime.SetCell(si,AggCell)
End If
Next AggCell
'Loop through the Buffer and accumulates data by Time:
For Each sCell As DataBufferCell In SourceDataBufferByTime.DataBufferCells.Values
SourceDataBuffer.SetCell(si,sCell,True)
Next sCell
Next sTime
End If
Else 'If View Is Periodic or Direct Translation:
SourceDataBuffer = api.Data.GetDataBufferUsingFormula($"FilterMembers(RemoveZeros(E#{CalcEntity.Name}:P#[{api.Pov.Parent.name}]:C#Local:{SourceFormulaString}) * $FXRate, {DataBufferFilters})",,True)
'Converts Data Buffer to get Aggregated Extended Members:
AggDataBuffer = api.Data.ConvertDataBufferExtendedMembers(api.Pov.Cube.name,CalcEntity.Name,api.Pov.Scenario.Name,SourceDataBuffer)
'Accumulates both Base and Extended Members Data Buffers
For Each AggCell As DataBufferCell In AggDataBuffer.DataBufferCells.Values
If Not SourceDataBuffer.DataBufferCells.ContainsKey(AggCell.DataBufferCellPk)
SourceDataBuffer.SetCell(si,AggCell)
End If
Next AggCell
End If
'Loop through the Buffer and accumulates data by Entity:
For Each sCell As DataBufferCell In SourceDataBuffer.DataBufferCells.Values
ResultBuffer.SetCell(si,sCell,True)
Next sCell
Next CalcEntity
Return ResultBuffer
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Private Function GetSourceDataBufferPOV(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal DimTag As String, ByVal DynamicPOV As Dictionary (Of String, String)) As String
Try
' Gets the Source Data Buffer POV
'
' Version Date Author Comment
' -------------------------------------------------------------------------------------------------------------
' V1.0 05-04-2024 A.Formenti Initial Version
'
' Changes:
'
'______________________________________________________________________________________________________________
Dim DimSourcePov As String = ""
'Builds Up the Source POV:
Dim sPovBuilder As New System.Text.StringBuilder
For Each sDimTag As String In DynamicPOV.keys
If Not sDimTag.StartsWith(DimTag, StringComparison.CurrentCultureIgnoreCase) Then 'Add all Dynamic Dims POV except for Extended Dim
sPovBuilder.Append($":{sDimTag}{DynamicPOV(sDimTag)}")
End If
Next sDimTag
'Source POV:
DimSourcePov = sPovBuilder.Remove(0,1).ToString
globals.SetObject($"GetSourcePOV_{api.pov.UD8.Name}",DimSourcePov)
Return DimSourcePov
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Private Function GetTargetDataBufferCellPk(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal DimTag As String) As DataBufferCellPk
Try
' Gets the Target DataBufferCellPK
'
' Version Date Author Comment
' -------------------------------------------------------------------------------------------------------------
' V1.0 05-04-2024 A.Formenti Initial Version
'
' Changes:
'
'______________________________________________________________________________________________________________
Dim TargetBufferCellPk As New DataBufferCellPk
'By Default all members pulled from XFCommon:
TargetBufferCellPk.SetMembers(api,"XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon","XFCommon")
'Extended dim to be pulled from the api.pov:
If DimTag.XFEqualsIgnoreCase("A#") Then
TargetBufferCellPk.AccountId = api.Pov.Account.MemberId
Else If DimTag.XFEqualsIgnoreCase("F#") Then
TargetBufferCellPk.AccountId = api.Pov.Flow.MemberId
Else If DimTag.XFEqualsIgnoreCase("O#") Then
TargetBufferCellPk.AccountId = api.Pov.Origin.MemberId
Else If DimTag.XFEqualsIgnoreCase("I#") Then
TargetBufferCellPk.AccountId = api.Pov.IC.MemberId
Else If DimTag.XFEqualsIgnoreCase("U1#") Then
TargetBufferCellPk.AccountId = api.Pov.UD1.MemberId
Else If DimTag.XFEqualsIgnoreCase("U2#") Then
TargetBufferCellPk.AccountId = api.Pov.UD2.MemberId
Else If DimTag.XFEqualsIgnoreCase("U3#") Then
TargetBufferCellPk.AccountId = api.Pov.UD3.MemberId
Else If DimTag.XFEqualsIgnoreCase("U4#") Then
TargetBufferCellPk.AccountId = api.Pov.UD4.MemberId
Else If DimTag.XFEqualsIgnoreCase("U5#") Then
TargetBufferCellPk.AccountId = api.Pov.UD5.MemberId
Else If DimTag.XFEqualsIgnoreCase("U6#") Then
TargetBufferCellPk.AccountId = api.Pov.UD6.MemberId
Else If DimTag.XFEqualsIgnoreCase("U7#") Then
TargetBufferCellPk.AccountId = api.Pov.UD7.MemberId
End If
Return TargetBufferCellPk
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
This is awesome @aforementi, I was playing around with this today and noticed that base members seem to show data but intermediary parents between cubes show no aggregation or data for them. I'm curious if you know a work around for this.
The concept I am describing is outlined below. I basically would love a way to be able to fill in the red row.
