Forum Discussion

Marco's avatar
Marco
Contributor II
11 days ago

How do I clean a scenario before copying data from another scenario? in the member formula.

Hi Everyone.
I want to copy the information from one scenario to another. Currently, I’m using the member formula of the target scenario to transfer the data. However, I first apply a calculate to set the values to zero, followed by a clecarcalculateddata to properly remove the existing information. The issue is that after this step, the copy process doesn’t execute, and the scenario remains with zero values. My code is as follows:

api.Data.calculate("S#FORECAST_" & monthNumber & "_AT_CY_BUDGET_RATE = 0*(S#FORECAST_" & monthNumber & "_AT_CY_BUDGET_RATE)",True)
api.Data.ClearCalculatedData("S#FORECAST_" & monthNumber & "_AT_CY_BUDGET_RATE",True,True,True,True)							

Dim destinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("S#FORECAST_" & monthNumber & "_AT_CY_BUDGET_RATE")

'' Create new Databuffer for the results
Dim resultDataBuffer As New DataBuffer

'Base Entity at Local
If Not api.Entity.HasChildren And api.Cons.IsLocalCurrencyForEntity() Then
	BRapi.ErrorLog.LogMessage(si,"Validate 1")
	' Get Databuffer from the account and scenario
    Dim sourceDataBuffer As DataBuffer = api.data.GetDataBufferUsingFormula("RemoveZeros(FilterMembers(S#[Forecast " & TimeName & "],[A#Root.Base]))", , False)
    ' Verificar si hay celdas con datos
    If sourceDataBuffer.DataBufferCells.Count > 0 Then
		BRapi.ErrorLog.LogMessage(si,"Validate 2")
        For Each sourceCell As DataBufferCell In sourceDataBuffer.DataBufferCells.Values

            If (Not sourceCell.CellStatus.IsNoData) And (sourceCell.CellAmount <> 0.0) Then
				BRapi.ErrorLog.LogMessage(si,"Validate 3")
                Dim resultCell As New DataBufferCell(sourceCell)

                resultCell.DataBufferCellPk.OriginId = DimConstants.Import

                resultDataBuffer.SetCell(si, resultCell, True)

            End If
        Next

        api.Data.SetDataBuffer(resultDataBuffer, destinationInfo,,,,,,,,,,,,,False)

    End If
End If

I would appreciate your help in understanding why the values are not being copied. The process is executed when I consolidate the information.

Finance
System

5 Replies

  • Henning's avatar
    Henning
    Valued Contributor II
    11 days ago

    Hi, as per default, each data unit gets cleared during a calculation operation (e.g. during a consolidation). This only does not apply if the corresponding scenario setting is set to False. However, in all my years in the OS world, I have never seen it being set to False.

     

    Your calculation really just seems to pull data without a filter one from scenario to another. Just use a standard api.data.calculate("S#destinationScenario = removezeros(S#sourceScenario)"). On local currency and base entities and other things you might want.

    Use a scenario member formula for that to make sure this gets executed first. 

    My high-level guess as to why yours does not work is that the member formula executes during a calculation of the source scenario. Keep in mind, calculations in OneStream are "pull", i.e. you cannot push data from your source scenario to your destination scenario. The calculation has to be executed from the destination scenario in order to pull the data from the source scenario.

    • Marco's avatar
      Marco
      Contributor II
      10 days ago

      So, are you recommending that I use a standard calculate function without all the additional logic? I had disabled the 'Clear Calculated Data during calc' option, but it didn’t help much, as the data was not being cleared when running the formula.

       

      • rhankey's avatar
        rhankey
        Contributor III
        8 days ago

        You script is a little confusing to me.

        What is the purpose of your first Calculate() statement that appears to be zeroing out the data in the target scenario?  Those zeros, and even worse when they were written as durable data, are going to get in the way of writing any subsequent values to those cells.  Other than in a few isolated instances where the odd zero is required to overcome derived data problem, writing zeros to the cube is bad.

        Then why do you subsequently clear what appears to be the same zeroed out data?

        As Henning alludes, the Scenario ClearCalculatedDataDuringCalc should normally be True (I similarly have never seen any builds where this has been set False).

        Then I'd have a two line BR:

        ClearCalculatedData() for all Calculated, Translated, Consolidate and Durable data in current Data Unit

        Calculate() to copy desired source data to current Data Unit.  I usually write the data as durable, but you need to make sure you're filtering out any data that may need to be recalculated in this Scenario. For filtering, you need to add an If test to check what Data Unit is being processed, and/or using Filters on the Calculate statement.

        Run the above two lines for the destination Scenario/Time/Cons/Entities.

        There is nothing I see in your current buffer handler that requires the buffer handler.

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    5 days ago

    You can clear a scenario (completely, all nodata) by running the Reset Scenario data management step, then with another data managment step, copy the data.  Perhaps a bit easier to build and maintain than similar calculations in code. 
    Also note, I wouldn't perform clear then copy in a member formula.  That could cause problems and the next guy will never be able to find it.