Use api.data.calculate to calculate data from one entity to another by account

[benmac360]

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? 

[Henning]

Hi, a good entry point is the documentation e.g. here ...

Writing Stored Calculations (onestream.com) (Design and Reference Guide)

...or here:

Writing Stored Calculations (onestream.com) (API Guide)

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.

Api.Data.Calculate("O#Forms = -O#Top:E#BU_100","A#40000, A#40010",,,,,,,,,,,,,, TRUE)

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.