Use api.data.calculate to calculate data from one entity to another by account
Hello - I'm looking for a formula to calculate data from one entity to another for two specific accounts. I would just use a standard data management job for this since the amounts are not changing, however I want the sign to change, so the amounts will net to zero when the entities are consolidated in the same structure. Can I use api.data.calculate for this? And if so, would the formula be stored on the two accounts?
In the following example, data from E#BU100 is copied to entity E#BU_110. Keep in mind that this example follows your description above, i.e. using a DM job for this. There are plenty of other possible ways to go about that. Which one is the best, depends on your exact requirements and process.
As you can see, BU_100 is the source, and BU_110 the target entity. Data is copied o the accounts A#40000 and A#40010 and multiplied with -1.
For this I created the following simple Finance Business Rule:
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
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
Namespace OneStream.BusinessRule.Finance.CopyAccount
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object
Try
Select Case api.FunctionType
Case Is = FinanceFunctionType.CustomCalculate
If args.CustomCalculateArgs.FunctionName.XFEqualsIgnoreCase("MyFunctionName") Then
Api.Data.Calculate("O#Forms = -O#Top:E#BU_100","A#40000, A#40010",,,,,,,,,,,,,, False)
End If
End Select
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
And then the DM step:
When this step is triggered, the data is copied as per first screenshot.
Please note that the point of view and the chosen members in the rule are only exemplary. You need to adjust those to the point of view you need to copy.
Also, I set the IsDurableCalculatedData boolean to False. I.e. when BU_110 is calculated or consolidated, this data will be cleared. If you wish this data to be durable (not cleared), set this to True as per the below code.
As for your last question "would the formula be stored on the two accounts?"
The answer is no, if you follow the above example. You can use member formulas and store the formula on a both accounts, but then a DM job is (possible but) not needed to trigger the calculation, as it runs naturally during each consolidation and calculation. Maybe member formulas make more sense in your case, but - again - this very much depends on your requirements.
