01-04-2022 11:59 AM - last edited on 05-02-2023 10:27 AM by JackLacava
Hello,
I am trying to set up a business rule that seeds the forecast from actuals to the forecast. This works easily when I am not using all entities to seed. However, I need to somehow take the actual data from each base entity and copy it all into 1 entity for the forecast. The client only needs to forecast at 1 entity but wants to include the actuals from every base. Is this possible to do in 1 rule? I know you can't have the entity on the left side of the calculation so I'm not sure how to solve.
Thanks!
Solved! Go to Solution.
01-05-2022 10:24 AM
I'm thinking you can achieve your result using databuffers.
1) You can only write into your pov entity, so:
Dim strTargetEntity = "TargetEntity"
If api.pov.entity.name = strTargetEntity Then
2) Set the target databuffer something like:
Dim targetBuffer As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("E#" & strTargetEntity)
Dim sourceDataBuffer As Databuffer = api.data.GetDataBufferUsingFormula("FilterMembers(E#TopEntity.Base)",DataApiSctiptmethodType.Calculate,False,targetBuffer)
3) Save to the target buffer:
api.data.SetDataBuffer(sourceDataBuffer,targetBuffer)
Please note, this is not the exact syntax, so corrections on your side are expected.
01-06-2022 08:46 PM
Hello,
As others in this thread have mentioned, you could do this in many different ways. Possibly the easiest way would be to design a custom calculate finance rule that is executed by a custom calc DM job that cycles through the base entities to copy, fills a result data buffer with data, and writes the results to a given target entity. Further below is an example script you can study to see one possible approach you could take.
To avoid a rule that loops through an entity member list, you could also consider taking the reverse approach by designing a DM sequence that has two custom calc DM steps. The first custom calc DM step would use an entity filter such as E#TopEntity.base that calls a custom calc business rule for each base entity in the DM step filter. Within the business rule called by the first DM step, you could then simply retrieve the Actuals data for each entity and store it in a global data buffer object that would gradually build up with data after iterating through each base entity. The second DM step would then be designed to run for the target base entity to seed the data to, and in this DM step you would call a business rule that retrieves the global data buffer object from memory (all the previously collected base entity data) and sets the data to the target destination for the forecast. The idea is to leverage and build up a global data buffer object that will persist in memory for the user throughout the completion of the entire DM sequence (both steps). The reason you might explore this approach is it could be potentially faster and help you avoid the need to loop through a member list to retrieve the data to seed.
Select Case api.FunctionType
Case Is = FinanceFunctionType.CustomCalculate
If args.CustomCalculateArgs.FunctionName.XFEqualsIgnoreCase("SeedActualsToForecast") Then
'only run the rule for a specific target entity (which should be defined in the custom calc DM entity filter) and local currency
If api.Pov.Entity.Name.XFEqualsIgnoreCase("SomeTargetEntity") AndAlso api.Cons.IsLocalCurrencyForEntity() Then
'declare the result buffer and empty destination
Dim resultBuffer As New DataBuffer()
Dim destinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("")
'declare a list of base entities to pull data from
Dim baseEntityList As List(Of MemberInfo) = api.Members.GetMembersUsingFilter(api.Pov.EntityDim.DimPk, "E#Houston.base", Nothing)
If (Not baseEntityList Is Nothing) Then
'loop through the base entities to copy and retrieve the data from the actual scenario
For Each baseEntity As MemberInfo In baseEntityList
Dim baseBuffer As DataBuffer = api.Data.GetDataBuffer(DataApiScriptMethodType.Calculate, "S#Actual:E#[" & baseEntity.Member.Name & "]", destinationInfo)
'fill the result buffer with data
resultBuffer = resultBuffer + baseBuffer
Next
'save the result buffer to the target destination and set IsDurableData to True as this is a custom calc.
api.Data.SetDataBuffer(resultBuffer, destinationInfo,,,,,,,,,,,,,True)
End If
End If
End If
End Select
Nick Kroppe
OneStream Software
01-04-2022 12:20 PM
There are a few ways this could be accomplished, but in simple terms, when processing the destination entity in forecast, you would cycle through each of the source base level entities from Actual, while aggregating the results to output data buffer. I presume all base level entities share the same currency of the destination entity, else you have a bit more work to do.
01-05-2022 04:19 AM
Loading via the workflow might be the best option in this case. Here are a couple of options
01-05-2022 07:51 AM
Maybe I'm misunderstanding but wouldn't you use the "If (api.Cons.IsLocalCurrencyForEntity() And Not api.Entity.HasChildren()) Then" beginning to narrow down all base entities then have your calculate line as normal?
01-05-2022 08:25 AM
Something along those lines might be possible if you could "push" the data from the source entities to the destination entity. However, I don't believe you can write data outside the Data Unit currently being processed. As such, you have to "pull" the data from the source entities to the destination entity. The other problem with "pushing" the data is that you would have to come up with a way of aggregating the data in the destination entity, whereas "pulling" makes it easy to reliably aggregate the data.
01-05-2022 08:29 AM
Apologies - I read it wrong. I didn't see that it was adding together multiple base entities into one entity. Ignore my comment 🙂
01-05-2022 10:24 AM
I'm thinking you can achieve your result using databuffers.
1) You can only write into your pov entity, so:
Dim strTargetEntity = "TargetEntity"
If api.pov.entity.name = strTargetEntity Then
2) Set the target databuffer something like:
Dim targetBuffer As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("E#" & strTargetEntity)
Dim sourceDataBuffer As Databuffer = api.data.GetDataBufferUsingFormula("FilterMembers(E#TopEntity.Base)",DataApiSctiptmethodType.Calculate,False,targetBuffer)
3) Save to the target buffer:
api.data.SetDataBuffer(sourceDataBuffer,targetBuffer)
Please note, this is not the exact syntax, so corrections on your side are expected.
01-04-2024 04:35 PM
Hi Koemets, I have tried adding a separate entity in the ExpressionDestinationInfo property as you implied, but I am getting an "Invalid destination data unit in script" error. This is the error I am actually trying to avoid by using this method as opposed to an api.Data.Calculate, so is there a way to resolve this issue?
01-06-2022 08:46 PM
Hello,
As others in this thread have mentioned, you could do this in many different ways. Possibly the easiest way would be to design a custom calculate finance rule that is executed by a custom calc DM job that cycles through the base entities to copy, fills a result data buffer with data, and writes the results to a given target entity. Further below is an example script you can study to see one possible approach you could take.
To avoid a rule that loops through an entity member list, you could also consider taking the reverse approach by designing a DM sequence that has two custom calc DM steps. The first custom calc DM step would use an entity filter such as E#TopEntity.base that calls a custom calc business rule for each base entity in the DM step filter. Within the business rule called by the first DM step, you could then simply retrieve the Actuals data for each entity and store it in a global data buffer object that would gradually build up with data after iterating through each base entity. The second DM step would then be designed to run for the target base entity to seed the data to, and in this DM step you would call a business rule that retrieves the global data buffer object from memory (all the previously collected base entity data) and sets the data to the target destination for the forecast. The idea is to leverage and build up a global data buffer object that will persist in memory for the user throughout the completion of the entire DM sequence (both steps). The reason you might explore this approach is it could be potentially faster and help you avoid the need to loop through a member list to retrieve the data to seed.
Select Case api.FunctionType
Case Is = FinanceFunctionType.CustomCalculate
If args.CustomCalculateArgs.FunctionName.XFEqualsIgnoreCase("SeedActualsToForecast") Then
'only run the rule for a specific target entity (which should be defined in the custom calc DM entity filter) and local currency
If api.Pov.Entity.Name.XFEqualsIgnoreCase("SomeTargetEntity") AndAlso api.Cons.IsLocalCurrencyForEntity() Then
'declare the result buffer and empty destination
Dim resultBuffer As New DataBuffer()
Dim destinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("")
'declare a list of base entities to pull data from
Dim baseEntityList As List(Of MemberInfo) = api.Members.GetMembersUsingFilter(api.Pov.EntityDim.DimPk, "E#Houston.base", Nothing)
If (Not baseEntityList Is Nothing) Then
'loop through the base entities to copy and retrieve the data from the actual scenario
For Each baseEntity As MemberInfo In baseEntityList
Dim baseBuffer As DataBuffer = api.Data.GetDataBuffer(DataApiScriptMethodType.Calculate, "S#Actual:E#[" & baseEntity.Member.Name & "]", destinationInfo)
'fill the result buffer with data
resultBuffer = resultBuffer + baseBuffer
Next
'save the result buffer to the target destination and set IsDurableData to True as this is a custom calc.
api.Data.SetDataBuffer(resultBuffer, destinationInfo,,,,,,,,,,,,,True)
End If
End If
End If
End Select
Nick Kroppe
OneStream Software
01-07-2022 04:33 PM
Thank you all, this is very helpful!