Forum Discussion

vasantharaidu's avatar
vasantharaidu
New Contributor III
21 days ago

Need help on Clear Specific member data

Hi Team,

I need your support regarding an issue I am facing with the dashboard-driven data clearing logic.

Requirement:
When specific Dimension/Members are selected in the dashboard, the attached code is expected to execute in Preview and Clear modes to remove the corresponding data.

Issue Observed:

  • The code runs successfully during execution.
  • I can see the log message indicating that the data is “cleared.”
  • However, the actual data is not getting cleared from the system.

This suggests that while the process is triggering correctly, the clearing operation is not impacting the underlying data as expected.

Could you please review and provide your suggestions on what might be causing this issue or any potential gaps in the approach?

Let me know if you need additional details or logs from my end.

Thanks in advance for your help.

api.Data.SetDataCell(memberScript, amount, isNoData, isDurableCalculatedData)

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
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.EPM_Clear_Specific_Scenarios

    Public Class MainClass

        Public Function Main(si As SessionInfo, globals As BRGlobals, api As FinanceRulesApi, args As FinanceRulesArgs) As Object
            Try

                '========================================
                ' VALID FUNCTION TYPE
                '========================================
                If api.FunctionType <> FinanceFunctionType.CustomCalculate Then
                    Return Nothing
                End If

                '========================================
                ' PARAMETERS
                '========================================
                Dim entityName As String = GetParam(args, "Entity")
                Dim scenarioName As String = GetParam(args, "ParamSScenario")
                Dim timeName As String = GetParam(args, "Time")
				Dim con as String="Local"
                Dim accountName As String = GetParam(args, "Account")
                Dim flowName As String = GetParam(args, "Flow")
                Dim originName As String = GetParam(args, "Origin")
                Dim icName As String = GetParam(args, "IC")

                Dim ud1 As String = GetParam(args, "UD1")
                Dim ud2 As String = GetParam(args, "UD2")
                Dim ud3 As String = GetParam(args, "UD3")
                Dim ud4 As String = GetParam(args, "UD4")
                Dim ud5 As String = GetParam(args, "UD5")
                Dim ud6 As String = GetParam(args, "UD6")
                Dim ud7 As String = GetParam(args, "UD7")
                Dim ud8 As String = GetParam(args, "UD8")

                Dim mode As String = GetParam(args, "Mode", "EXECUTE").ToUpper()
                Dim confirmDelete As String = GetParam(args, "ConfirmDelete", "NO").ToUpper()

                api.LogMessage("=== DATABUFFER CLEAR START ===")
                api.LogMessage("Mode=" & mode & " | ConfirmDelete=" & confirmDelete)

                '========================================
                ' VALIDATION
                '========================================
                If String.IsNullOrWhiteSpace(accountName) Then
                    Throw New Exception("Account parameter is required.")
                End If

                '========================================
                ' BUILD FILTER
                '========================================
                Dim filter As String =
                    "E#" & entityName &
                    ":S#" & scenarioName &
                    ":T#" & timeName &
                    ":A#" & accountName

                If Not String.IsNullOrWhiteSpace(flowName) Then filter &= ":F#" & flowName
                If Not String.IsNullOrWhiteSpace(originName) Then filter &= ":O#" & originName
                If Not String.IsNullOrWhiteSpace(icName) Then filter &= ":I#" & icName
				If Not String.IsNullOrWhiteSpace(Con) Then filter &= ":C#" & "Local"
                If Not String.IsNullOrWhiteSpace(ud1) Then filter &= ":U1#" & ud1
                If Not String.IsNullOrWhiteSpace(ud2) Then filter &= ":U2#" & ud2
                If Not String.IsNullOrWhiteSpace(ud3) Then filter &= ":U3#" & ud3
                If Not String.IsNullOrWhiteSpace(ud4) Then filter &= ":U4#" & ud4
                If Not String.IsNullOrWhiteSpace(ud5) Then filter &= ":U5#" & ud5
                If Not String.IsNullOrWhiteSpace(ud6) Then filter &= ":U6#" & ud6
                If Not String.IsNullOrWhiteSpace(ud7) Then filter &= ":U7#" & ud7
                If Not String.IsNullOrWhiteSpace(ud8) Then filter &= ":U8#" & ud8

                api.LogMessage("Filter used: " & filter)

                '========================================
                ' GET DATABUFFER
                '========================================
                Dim dataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula(filter)

                If dataBuffer Is Nothing _
                    OrElse dataBuffer.DataBufferCells Is Nothing _
                    OrElse dataBuffer.DataBufferCells.Count = 0 Then

                    api.LogMessage("No data found for given filter.")
                    Return Nothing
                End If

                Dim totalCells As Integer = dataBuffer.DataBufferCells.Count
                api.LogMessage("Cells found: " & totalCells)
				

                '========================================
                ' SAFETY LIMIT
                '========================================
                If totalCells > 500000 Then
                    Throw New Exception("Too many records selected (" & totalCells & "). Reduce filter.")
                End If

                '========================================
                ' PREVIEW MODE
                '========================================
                If mode = "PREVIEW" OrElse confirmDelete <> "YES" Then

                    Dim previewCount As Integer = 0

                    For Each kvp As KeyValuePair(Of DataBufferCellPk, DataBufferCell) In dataBuffer.DataBufferCells

                        api.LogMessage("PREVIEW: " & kvp.Value.CellAmount)

                        previewCount += 1
                    Next
						Dim preview As New StringBuilder()
						
						                    preview.AppendLine("DATA SELECTED FOR CLEAR")
						                    preview.AppendLine("====================================")
						                    preview.AppendLine("Filter :")
						                    preview.AppendLine(filter.ToString())
						                    preview.AppendLine("")
						                    preview.AppendLine("Total Cells : " & totalCells.ToString("N0"))
						                    'preview.AppendLine("Total Amount: " & Kvp.value.cellamount.ToString("F2"))
						
                    api.LogMessage("Total PREVIEW cells: " & previewCount)
					BRApi.Dashboards.Parameters.SetLiteralParameterValue(
					                        si,
					                        True,
					                        "ETPreviewData",
					                        preview.ToString())

                    Return Nothing
                End If

                '========================================
                ' CLEAR MODE
                '========================================
                Dim clearedCount As Integer = 0
                Dim skippedCount As Integer = 0

                For Each kvp As KeyValuePair(Of DataBufferCellPk, DataBufferCell) In dataBuffer.DataBufferCells

                    Try
                        Dim cellPk As DataBufferCellPk = kvp.Key
                        Dim memberScriptBuilder As New MemberScriptBuilder()
                        api.Data.ApplyDataBufferCellPkToMemberScriptBuilder(memberScriptBuilder, cellPk)
                        Dim writeScript As String = memberScriptBuilder.GetMemberScript.ToString
api.LogMessage("WriteScript" & writescript.ToString())
                        api.Data.SetDataCell(writeScript, 0D, True,True)
						

                        clearedCount += 1

                    Catch exInner As Exception

                        skippedCount += 1
                        api.LogMessage("SKIPPED: " & kvp.Value.CellAmount.ToString("F2") & " | Error: " & exInner.Message)

                    End Try

                Next

                '========================================
                ' SUMMARY
                '========================================
				
                api.LogMessage("=== CLEAR SUMMARY ===")
				api.LogMessage("Script      :" & filter )
                api.LogMessage("Total Cells : " & totalCells)
                api.LogMessage("Cleared     : " & clearedCount)
                api.LogMessage("Skipped     : " & skippedCount)

                api.LogMessage("=== DATABUFFER CLEAR END ===")

                Return Nothing

            Catch ex As Exception
                Throw ErrorHandler.LogWrite(si, ex)
            End Try

        End Function

        '========================================
        ' PARAM HELPER
        '========================================
        Private Function GetParam(args As FinanceRulesArgs, name As String, Optional def As String = "") As String
            Try
                Dim v As String = args.CustomCalculateArgs.NameValuePairs.XFGetValue(name)
                If String.IsNullOrWhiteSpace(v) Then Return def
                Return v.Trim()
            Catch
                Return def
            End Try
        End Function

    End Class

End Namespace

 

dashboards
Extenders
Finance

2 Replies

  • BenEppel's avatar
    BenEppel
    Contributor
    21 days ago

    Is the Entity, Time, Scenario and Cons being fed into a data management step which is calling this rule? You can only clear the data within the data unit defined in the DM step. Within your databuffer member filter, you don't need to define the entity, scenario, time, and cons because the rule will assume those members from the POV in the data management step. 

    For example, below the Entity MF has a .base expansion. The custom calculate will run for every base entity and you can only clear the entity that is currently running. To know which enetity is running, you can use api.pov.Entity.Name. To pass a specific entity, you can put E#|!Entity!| in the entity filter, and then below in the parameters put Entity = |!Entity!|. Within the component calling the DM step, define entity with the real param, so Entity = |!param_Entity!|. 

    Below is a more performative way to clear databuffer cells. Instead of setting them all individually, you can collect all the cells you want to set to zero, and then set them all at once. This limits the amount of database interactions. 

    Private Sub ClearAll(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs)

			
				
				Dim destination As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("V#YTD")
				'retrieve the data buffer to clear, loop through the cells, and set the cell amounts to zero and the cell status to NoData
				Dim sourceBufferr As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(FilterMembers(V#YTD))",,False,,,,)
			
					For Each sourceCell As DataBufferCell In sourceBufferr.DataBufferCells.Values
								
								sourceCell.CellAmount = 0
								sourceCell.CellStatus = DataCellStatus.CreateDataCellStatus(True, False)
								resultBufferToClear.SetCell(si, resultCell)
							Next
				
				
					'clear out the cells
					api.Data.SetDataBuffer(sourceBufferr, destination,,,,,,,,,,,,,False)
				
		
			
			
	End Sub

     

  • vasantharaidu's avatar
    vasantharaidu
    New Contributor III
    14 days ago

    Thanks BenEppel. its helped me. also i have found another work around to clear the data without Data Management Step.

                                            Dim msv As New MemberScriptAndValue()
				            msv.Script = BuildClearScriptFromCell(api, baseEntity, scenarioName, timeName, cell)
				            api.LogMessage("CLEAR SCRIPT: " & msv.Script)
				            auditDetails.AppendLine("CLEAR SCRIPT | " & msv.Script)
				            msv.IsNoData = True
							msList.Add(msv)
										
					Dim result As XFResult = BRApi.Finance.Data.SetDataCellsUsingMemberScript(si, msList)
							api.LogMessage("Row 424")