Forum Discussion

Krishna's avatar
Krishna
Valued Contributor
3 years ago

Best way to this calculation

Hi - I know the below code is not a good practice but is there is an alternative way to set the data =0 because I want to clear all Origin Base member and UD2 base. Any help would be appreciated ?  ...
Finance
Krishna's avatar
Krishna
Valued Contributor
3 years ago

I have modified as below and let me know if this good. All I am doing is to if there is a data in USD Account then I am clearing the other account at USD and copying the data.

If Not api.Entity.HasChildren Then

	Dim destAcct As String = "A#OCI_PensionOPEB"
	Dim destDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(A#OCI_PensionOPEB:F#EndBalInput:C#USD)")

	'USD ACCOUNT
	Dim srcAcct As String = "A#USDOCI_PensionOPEB"
	Dim srcDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(A#USDOCI_PensionOPEB:F#EndBalInput)")

	If Not srcDataBuffer Is Nothing Then

		For Each srccell As DataBufferCell In srcDataBuffer.DataBufferCells.Values
			If srccell.CellAmount <> 0 Then
				Dim ud1Member As String = srccell.GetUD1Name(api)
				Dim ud3Member As String = srccell.GetUD3Name(api)
				Dim icp As String = srccell.GetICName(api)

				If Not destDataBuffer Is Nothing Then
					For Each sourcecell As DataBufferCell In destDataBuffer.DataBufferCells.Values
						If Not sourcecell.CellStatus.IsNoData Then
							api.Data.ClearCalculatedData(True, True, True, destAcct,,, "I#" & icp, "U1#" & ud1Member,, "U3#" & ud3Member,,,,,)
						End If
					Next
				End If
			End If
		Next
		api.Data.Calculate("A#OCI_PensionOPEB:F#EndBalInput=A#USDOCI_PensionOPEB:F#EndBalInput")
	End If
End If
Henning's avatar
Henning
Icon for OneStream Employee rankOneStream Employee
3 years ago

Hi Kris,

I am still not 100% sure what you are trying to achieve here. When you want to copy data from account A to B, account B is a calculated account, I assume? That is why I asked if the data you wish to clear is calculated. 

With regards to your script, you are looping within a loop, which I would not recommend as this will have an impact on performance.

I will assume that there is calculated data on the target account that is being calculated during each calculation. Ideally, data is not copied to an account where it has to be deleted later on. If that cannot be avoided one may clear it afterwards, of course. In that case, (something like) these two rows may be all you need.

api.Data.ClearCalculatedData(True,True,True,"A#USDOCI_PensionOPEB","F#EndBalInput",,"I#ICEntities.base","U1#Top.base",,"U3#Top.base")
api.Data.Calculate("A#OCI_PensionOPEB:F#EndBalInput=A#USDOCI_PensionOPEB:F#EndBalInput")