The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.
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
I want to Validate that the Data is cleared and validate the calculate
Hi all - We have a Labor Allocation Process that runs manually which we have seen some silent failures occur through our Api Calculate or Api Clear data. I am attempting to go through our business rules and add more complex logging to get a better understanding where/when these things occur. Lets say I had this code block.. Dim sourceDims As String = "U2#None:U3#None:O#Import:I#None:F#None:V#Periodic:C#USD" Dim destinationDims As String = "U2#None:U3#None:O#Import:I#None:F#None:V#Periodic:C#USD" If args.CustomCalculateArgs.FunctionName.XFEqualsIgnoreCase("Copy3rdPartyImportData") Then 'Only run for the Base Entities and local currency If (Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyForEntity()) 'Get the current scenario Name Dim curScenarioName As String = api.Pov.Scenario.Name If curScenarioName = "Rpt_Mar" Then 'Clear previously calculated data api.Data.ClearCalculatedData(True, True, True, True,"A#3PFB_Capital","O#Import","I#None","V#Periodic","C#USD") api.Data.ClearCalculatedData(True, True, True, True,"A#3PFB_Expense","O#Import","I#None","V#Periodic","C#USD") api.Data.ClearCalculatedData(True, True, True, True,"A#Travel_Capital","O#Import","I#None","V#Periodic","C#USD") api.Data.ClearCalculatedData(True, True, True, True,"A#Travel_Expense","O#Import","I#None","V#Periodic","C#USD") 'Seed Current Scenario with previous imported 3rd party data api.Data.Calculate("S#" & curScenarioName & ":A#3PFB_Capital:" & destinationDims & " = RemoveZeros(S#Rpt_Feb:A#3PFB_Capital:" & sourceDims & ")",True) api.Data.Calculate("S#" & curScenarioName & ":A#3PFB_Expense:" & destinationDims & " = RemoveZeros(S#Rpt_Feb:A#3PFB_Expense:" & sourceDims & ")",True) api.Data.Calculate("S#" & curScenarioName & ":A#Travel_Capital:" & destinationDims & " = RemoveZeros(S#Rpt_Feb:A#Travel_Capital:" & sourceDims & ")",True) api.Data.Calculate("S#" & curScenarioName & ":A#Travel_Expense:" & destinationDims & " = RemoveZeros(S#Rpt_Feb:A#Travel_Expense:" & sourceDims & ")",True) I have tried: Dim srcCap As Decimal = api.Data.GetDataCell("S#Rpt_Nov:A#3PFB_Capital:U1#TotUD1:U2#None:U3#None:U4#TotUD4:U5#None:U6#None:U7#None:U8#None:O#Import:I#None:F#None:V#Periodic:C#USD:E#CHN000").CellAmount BRApi.ErrorLog.LogMessage(si, "SOURCE 3PFB_Capital=" & srcCap.ToString("N2")) Which results in 0. api.Data.Calculate(formula1, True) Dim verify1 As Decimal = api.Data.GetDataCell("S#Rpt_Dec:A#3PFB_Capital:U1#TotUD1:U2#None:U3#None:U4#TotUD4:...E#" & api.Pov.Entity.Name).CellAmount BRApi.ErrorLog.LogMessage(si, "DEST read-back=" & verify1.ToString("N2")) same with something like this.
Design Approach for Actual and Budget Consolidation Using FSK
Hi OS Community, I’m an EPM Consultant working mainly on Financial Consolidation, and I would appreciate some guidance on a scenario‑specific consolidation design question. Currently, our Actuals consolidation process is working correctly and uses the standard XFW_FSK_Accounts lookup table to drive account mapping during consolidation. This includes full account‑level detail, which is required for statutory reporting and analysis. Now we need to enable consolidation for the Budget scenario, the rules for Budget should be the same as Actual , but with a different level of granularity: The Budget scenario is loaded at a higher‑level Account hierarchy Main Question What is the recommended way to use a different account mapping lookup table for Budget while keeping the existing XFW_FSK_Accounts logic for Actuals? More specifically: Can we assign or reference a different account lookup table (e.g. XFW_FSK_Accounts_Budget) within the consolidation Business Rules, based on the Scenario? Is it best practice to: Use Scenario‑driven logic inside the consolidation BRs to switch lookup tables? Or create separate consolidation rules for Actual vs Budget? Are there any framework‑supported patterns for handling multi‑scenario account mappings where hierarchies differ? If anyone has implemented a similar approach or can point to best practices / documentation, your insights would be greatly appreciated. Thanks in advance for your help! Best regards, Carolina
Orders calculation formula using Backlog and Sales Accounts
Hello, I'm attempting to create an orders (500055) account, calculated using existing Backlog (500054) and Sales (100020) Accounts. (Current Month Backlog - Prior Month Backlog + Current Month Sales) When I run this calculation in the forecast scenario, February - December work as expected, but there is an issue with January that I can't pinpoint. Any ideas where I've gone wrong? Thank you in advance, Justin Trad If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyForEntity)) And api.Time.GetPeriodNumFromId(api.Pov.Time.MemberID) = 1 Then api.data.calculate("A#500055:T#PovYearM1:O#import:U1#none:U2#none:U5#none:U6#none = (A#500054:T#PovYearM1:O#top:U1#top:U2#top:U5#top:U6#top - A#500054:T#PovPriorYearM12:O#top:U1#top:U2#top:U5#top:U6#top) + A#100020:T#PovYearM1:O#top:U1#top:U2#top:U5#top:U6#top") Else If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyForEntity)) Then api.data.calculate("A#500055:T#Pov:O#import:U1#none:U2#none:U5#none:U6#none = (A#500054:T#Pov:O#top:U1#top:U2#top:U5#top:U6#top - A#500054:T#PovPrior1:O#top:U1#top:U2#top:U5#top:U6#top) + A#100020:T#Pov:O#top:U1#top:U2#top:U5#top:U6#top") End If
How to round the result of a calculation?
Hi everyone, I would like to round the value for a calculation, I try wit this api.Data.Calculate("A#P_EBPPrct:O#Import:U1#D0000:U2#P0000:U3#CORP_ADJ:U4#PJ00000 = Round(Divide(A#433100, A#411000:E#" & strEntTx3 & ":C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#PROMO:U3#CORP_RPT:U4#ALL_PROJECTS))") or this api.Data.Calculate("A#P_EBPPrct:O#Import:U1#D0000:U2#P0000:U3#CORP_ADJ:U4#PJ00000 = Divide(A#433100, A#411000:E#" & strEntTx3 & ":C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#PROMO:U3#CORP_RPT:U4#ALL_PROJECTS)",AddressOf OnEvalDataBuffer) Private Sub OnEvalDataBuffer(ByVal api As FinanceRulesApi, ByVal evalName As String, ByVal eventArgs As EvalDataBufferEventArgs) Dim resultCells As New Dictionary(Of DataBufferCellPk, DataBufferCell) For Each sourceCell As DataBufferCell In eventArgs.DataBuffer1.DataBufferCells.Values sourceCell.CellAmount = math.round(sourceCell.CellAmount) Next eventArgs.DataBufferResult.DataBufferCells = eventArgs.DataBuffer1.DataBufferCells End Sub but didn't work, so, OS have a way to round values in a calculation?
Impact Calc Status
Hi all, I'm using ImpactCalcStatus to impact an entity based on another entity that has the Sibling consolidation pass set to 2. I can see the status changing during processing, but at the end of the consolidation, it remains CA/CN. Only after running it again does the status change to OK. What am I missing here? api.CalcStatus.ImpactCalcStatus(cubeId, Children.MemberId, Parent.MemberId, consId, scenarioId, timeId); Thanks.
How can I retrieve the name of the form template using a finance business rule?
Hello everyone, Thanks to this discussion: https://community.onestreamsoftware.com/discussions/WorkflowDI/forms-by-entity/13417 I can now show or hide the input cube views based on whether or not the group entity has been released, as stored in Entity Text6. However, by doing this, I lose the ability to display cube views only for the quarter, half-year or full year. Currently, I display the cube views every month if the entity is present in the group, so this does not apply to cube views that should be displayed quarterly. I would therefore like to retrieve the name of the form displaying each data entry cube view, so that I can define in the Business Rules whether each form should be displayed monthly, quarterly, half-yearly or only in December. This would allow me to use the settings provided natively by OneStream, which I have previously bypassed. But I am unable to do so. Is this feasible? The ultimate aim is to prevent accidental data entry into workflows that should no longer be populated. I have already removed accountants’ access to the workflow for entities that have left the group, but the central team still has access, and I would like to prevent them from accidentally entering data for periods when the entity has left the group. Thank you for your help
Alternate Entity hierarchies unbalanced
hello Team, good day; we have 4 alternate hierarchies for Entity dimension, the Application is for Consolidation, BS & IC, having the Balance sheet accounts balancing the amounts, the main hierarcy is balanced (means 0 asset, liability & equity), for all the rest aer unbalanced and the reazon could be the elimination accounts because these are not summing properly to 1st and 2nd level, (main parent and ancestor). the consolidation rule is custom and runing, maybe the conso properties are bad. Does anyone knows how to solve the unbalancing for all the Entity hierarchies? thakns in advance and Kind Regards
