Skip to main content
A
AndreaF
November 8, 2023
Solved

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

  • November 8, 2023
  • 5 replies
  • 0 views

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.

 

Best answer by JackLacava

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, ...).

5 replies

A
AndreaFAuthor
November 8, 2023
 

 

 

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.

A
AndreaFAuthor
November 8, 2023

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


AndreaF_0-1699448835370.png

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


AndreaF_1-1699448922905.png

 

A
AndreaFAuthor
November 8, 2023

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


AndreaF_2-1699449155714.png

I get the same data combinations as before


AndreaF_3-1699449197223.png

 

A
AndreaFAuthor
November 8, 2023

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


AndreaF_4-1699449399230.png

I get no data


AndreaF_5-1699449426851.png

What am I doing wrong? Thank you

J
JackLacavaAnswer
November 8, 2023

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, ...).

A
AndreaFAuthor
November 8, 2023

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

Terms & ConditionsAccessibility statement