11-10-2022 12:33 PM - last edited on 05-02-2023 10:40 AM by JackLacava
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 ?
api.Data.Calculate("A#ACCT:F#EndBalInput:UD1#" & ud1Member & ":UD3#" & ud3Member & ":I#" & icpMember & ":C#USD:O#All:UD2#All:UD4#None:UD5#None:UD6#None:UD7#None:UD8#None = 0")
11-10-2022 02:04 PM
Kris,
I would use a data buffer and then clear out/reset each data buffer cell. Here's some sample code. I would pull all ud1, ud3 and ICP members in the data buffer (i.e. exclude the dimensions in the member filter script) and then search for specific members within the For Each cell loop.
11-10-2022 02:29 PM
Thanks Cosimo.
I already know the ICP, UD1 & UD3 but I want to clear the data for all the Origin & UD2 member. IN that case Can I just use the data buffer cell to clear all Origin & UD2 base members. Will this work ?
11-11-2022 03:02 AM
In case you use the data buffer way, make sure to set the isNoData boolean for the cell's status to True:
Still, please be aware of possible consequences and consider alternative approaches.
11-10-2022 03:26 PM
Yes.. the data buffer will include all cells for any Origin / UD2 combination that has data (real or derived). Just loop through each cell and reset the cell back to NoData.
Since you mention the ICP dimension, are you trying to clear out intercompany and elimination data from your script? If yes, you will need to run the same calculation for C#Elimination data unit as this is linked to O#Elimination.
11-11-2022 02:51 AM
Hi Kris, thank you very much for asking this question as you already point out that this is not recommended.
What is the reason that you need to clear that data? Is this data calculated so that you can use a api.data.clearcalculateddata? Is it loaded, then it would be best to reload that data with an adjusted dataset prior to loading? Etc.?
The thing is with a Business Rule, you remove all of that data (and really, please do not set to zero!), also in periods that have been closed and locked already if they get reconsolidated. So you are tapping into serious audit issues here in my view.
11-11-2022 12:31 PM - last edited on 04-28-2023 09:11 AM by JackLacava
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
11-14-2022 05:33 AM
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")
11-19-2022 04:22 PM
Hi Henning - Thanks and Here you go.
A - Normal Account
B - USD Override Account
The above 2 accounts come through the file
1. When Account B has data then Copy the Data from B to A USD and A Local should not be cleared.
2. If the B has no data, then A should translate naturally.
11-21-2022 05:37 AM
Hi Kris, so you are attempting a standard translation override. Typically one uses the flow dimension for this, but I assume using an account to hold the override data is the result of the design session. There are - as always - several ways to go about it. Here is a simple example:
If (Not api.Entity.HasChildren() And api.Cons.IsForeignCurrencyForEntity) Then 'Execute on local entity and C#Ttranslated
If not api.Data.GetDataCell("A#B").CellStatus.IsNoData Then 'If B has data, clear A on Translated and copy from B
11-11-2022 10:20 AM
Hi Kris,
You can do it with a little trick: First, change the number status to "IsCalculated", then clear calculated data.
Example
Dim script As String = "U2#Allo"
api.Data.Calculate($"{script} = 0 * {script}")
api.Data.ClearCalculatedData(script,True,True,True)
Regards,
Tobias
11-11-2022 12:32 PM
Thanks