Forum Discussion

AndreaF's avatar
AndreaF
Contributor III
2 years ago

GetDataBufferUsingFormula not getting data when refering to a different scenario and time

Hi,

I have a Custom Calc rule called by a data management step and I am experiencing a weird behaviour. I know there is data in Actual 2023M6, and I can get that data when running the data management step from either Actual 2023M12 or MF7 (name of another scenario) 2023M6, changing the GetDataBufferUsingFormula appropriately, but not from MF7 2023M12. I'll post the details below.

 

  • The problem is not GetDataBufferUsingFormula, but FilterMembers. The first argument to FilterMembers is creating a buffer starting from the DataUnit currently being evaluated; the arguments after that are just filtering that buffer. So as it sees T#2023M6, FilterMembers builds you a buffer from DataUnit Cb#Main:E#RU0452:C#Local:S#M7:T#2023M6; by the time your clause to filter on S#Actual is considered, you already have a buffer *containing only records from S#M7*, so you filter them all out and you get an empty buffer.

    If you want to override multiple DataUnit members, you have to do it in the first clause of FilterMembers, e.g. FilterMembers(T#2023M6:S#Actual, ...).

  • AndreaF's avatar
    AndreaF
    Contributor III
     

     

     

    Imports System
    Imports System.Collections.Generic
    Imports System.Data
    Imports System.Data.Common
    Imports System.Globalization
    Imports System.IO
    Imports System.Linq
    Imports System.Windows.Forms
    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.MF_Copy
    	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
    	
    				Dim bLogEnabled As Boolean = True					'Change to true only in development for logging values
    				Dim strLogName As String = Me.GetType.Namespace		'Uniquely identifies where the log is coming from, no change required. Get the name from the current namespace for identification of log entry
    				Dim sbLogMessage As New System.Text.StringBuilder	'Holds the log - use appendline to add to the logstring
    				sbLogMessage.appendline ("*** Log ***")				'Header for easy finding log entries
    				
    				Select Case api.FunctionType
    					
    					Case Is = FinanceFunctionType.CustomCalculate
    
    						If args.CustomCalculateArgs.FunctionName.XFEqualsIgnoreCase("CopyETC_Actual_Test") Then
    						'-------------------------------------------------------------------------
    						'---CopyETC_Actual_Test---Start
    						'-------------------------------------------------------------------------
    							sbLogMessage.appendline ("CopyETC_Actual_Test")
    							sbLogMessage.appendline ("POV:" & api.Pov.Entity.Name & "_" & api.Pov.Scenario.Name & "_" & api.Pov.Time.Name)
    
    							'Run for base Entities and local currency only
    							If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyforEntity())) Then
    																
    								'Copy data
    								sbLogMessage.AppendLine("CopyData")
    								'Dim databuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(T#2023M6,S#Actual,A#AC890000.Base,F#None,U1#PR999998.Base,U1#None,U2#EQ0599.Base,U2#None,U3#None,U4#None,U5#Tot_Reporting.Base,U6#None,U7#None,U8#Actual)",,False)
    								'Dim databuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(S#Actual,A#AC890000.Base,F#None,U1#PR999998.Base,U1#None,U2#EQ0599.Base,U2#None,U3#None,U4#None,U5#Tot_Reporting.Base,U6#None,U7#None,U8#Actual)",,False)
    								Dim databuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(T#2023M6,A#AC890000.Base,F#None,U1#PR999998.Base,U1#None,U2#EQ0599.Base,U2#None,U3#None,U4#None,U5#Tot_Reporting.Base,U6#None,U7#None,U8#Actual)",,False)
    								
    								If Not databuffer Is Nothing Then
    									For Each SourceCell As databuffercell In databuffer.DataBufferCells.Values
    										If Not SourceCell Is Nothing And SourceCell.CellAmount <> 0 Then
    											
    											Dim sSourceMemberscript As String = sourcecell.DataBufferCellPk.GetMemberScript(api)
    											sbLogMessage.AppendLine("CalculatedCell: " & sSourceMemberscript)
    																						
    										End If
    									Next
    								End If
    
    							End If
    						'-------------------------------------------------------------------------
    						'---CopyETC_Actual_Test---End
    						'-------------------------------------------------------------------------
    						End If
    						
    				End Select
    				
    				If bLogEnabled Then brapi.ErrorLog.LogMessage(si,strLogName,sbLogMessage.ToString)
    				
    				Return Nothing
    				
    			Catch ex As Exception
    				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
    			End Try
    		End Function
    	End Class
    End Namespace

     

     

    This is the finance rule with the test custom calc. The code is used for testing and it prints out in the error log the combinations found by the getdatabufferusingformula.

  • AndreaF's avatar
    AndreaF
    Contributor III

    Now, I have a data management step, here run from Actual 2023M12.

    The getdatabufferusingformula, which is pointing to T#2023M6, can get some data.

     

  • AndreaF's avatar
    AndreaF
    Contributor III

    I then change the getdatabufferusingformula to point to S#Actual

     

    Dim databuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(S#Actual,A#AC890000.Base,F#None,U1#PR999998.Base,U1#None,U2#EQ0599.Base,U2#None,U3#None,U4#None,U5#Tot_Reporting.Base,U6#None,U7#None,U8#Actual)",,False)

     

    and run the data management step from MF7 2023M6

    I get the same data combinations as before

     

  • AndreaF's avatar
    AndreaF
    Contributor III

    However, if I put the 2 things together and change the getdatabufferusingformula to point to S#Actual T#2023M6

     

     

    Dim databuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(T#2023M6,S#Actual,A#AC890000.Base,F#None,U1#PR999998.Base,U1#None,U2#EQ0599.Base,U2#None,U3#None,U4#None,U5#Tot_Reporting.Base,U6#None,U7#None,U8#Actual)",,False)

     

    and run the data management step from MF7 2023M12

    I get no data

    What am I doing wrong? Thank you

    • JackLacava's avatar
      JackLacava
      Honored Contributor

      The problem is not GetDataBufferUsingFormula, but FilterMembers. The first argument to FilterMembers is creating a buffer starting from the DataUnit currently being evaluated; the arguments after that are just filtering that buffer. So as it sees T#2023M6, FilterMembers builds you a buffer from DataUnit Cb#Main:E#RU0452:C#Local:S#M7:T#2023M6; by the time your clause to filter on S#Actual is considered, you already have a buffer *containing only records from S#M7*, so you filter them all out and you get an empty buffer.

      If you want to override multiple DataUnit members, you have to do it in the first clause of FilterMembers, e.g. FilterMembers(T#2023M6:S#Actual, ...).

  • AndreaF's avatar
    AndreaF
    Contributor III

    Thank you, I have tested it and this solves my issue. I really appreciate you help.