Use api.Data.Calculate to move data between 2 cubes
Is it possible to copy a value from one cube to another mutually exclusive cube with a Custom Calculate business rule? I am trying to copy a value from the Cb#Financial cube to the Cb#CASH cube. However, using the “api.Data.Calculate” code below I get the error “Invalid destination data unit in script” even though the Data Unit defined in my Data Management step matches the target cube Data Unit. Dim sTops As String = ":C#Local:S#AOP_Final:V#Periodic:A#BGMOPEX:F#EndBal_Input:O#BeforeAdj:I#None:U1#TopUD1:U2#TopUD2:U3#SALARY:U4#TopUD4:U5#TopUD5:U6#TopUD6:U7#None:U8#GC_USD" Dim sNones As String = ":Cb#CASH:E#US01:S#CashForecast_Forecast:T#2024M10:C#Local:V#Periodic:F#EndBal_Input:O#Forms:I#None:U1#None:U2#None:U3#NoVendor:U4#None:U5#None:U6#CashForecast_Plan:U7#None:U8#GC_USD" Dim dPayroll As Decimal = api.Data.GetDataCell("Cb#Financial:E#US01" & sTops & ")").CellAmount api.Data.Calculate("A#PAYUSA" & sNones & " = RemoveZeros(" & dPayroll & ")",True) In the past I have used the “BRApi.Finance.Data.SetDataCellsUsingMemberScript(si, objMemberScriptValues)” method and the “DM Data Export / Import” method to move data between cubes, but I was hoping that I could use a simple api.Data.Calculate utilizing the “Cb#” filter. Is this possible? Thanks.
Calculation Error "Error processing script: 'A#All = 0 * A#All"
I was running into issues clearing durable data prior to Clearing it so I calculated the data prior to the Clear statement so the data was "calculated", then ran the ClearCalculatedData stmt. It works fine for me, but I have admin access and it works fine for a select few users. But for the majority of users they receive the error"Error processing script: 'A#All = 0 * A#All". I can not easily test in Prod as my hands are tied as far as making security changes. In prod I have other users with the same set of security groups (just pointing to other entity WFs) and they do not receive the error. In our Dev environment I can set up a native user with the exact same security groups as a prod user that fails and I do not receive an error. As for it being an issue with data, I have an entity where 1 user can run it and another can not. And they have the same set of security groups. Does anyone have any thoughts on what is going on? I also thought that the ClearCalculatedData should have been able to clear it (durable data) with the Boolean set to true, but it doesn't. Does anyone have any thought on why the clear does not work w/o calculating the data prior to clearing it? I did see a note about the ClearCalculatedData that the final argument must be true to clkear any data with a storage type Durable. But I'm not exactly sure what this means - can someone clarify that statement for me? This is the code: This is the error:
Recurring journal with Auto Post
So im trying to create a rule that pulls selected journals forward than posts them automaticaly. I have my test journal pulling forward as expected but when im trying to post the journal in rule below its causing the rule to error. The line in question is, BRApi.Journals.Process.ExecutePost(si, journalObjectHeader.UniqueID) What am i missing it seems pretty straight forward? Imports System Imports System.Data Imports System.Data.Common Imports System.IO Imports System.Collections.Generic Imports System.Globalization Imports System.Linq Imports Microsoft.VisualBasic Imports System.Windows.Forms Imports OneStream.Shared.Common Imports OneStream.Shared.Wcf Imports OneStream.Shared.Engine Imports OneStream.Shared.Database Imports OneStream.Stage.Engine Imports OneStream.Stage.Database Imports OneStream.Finance.Engine Imports OneStream.Finance.Database Imports System.IO.Compression Namespace OneStream.BusinessRule.Extender.CA_JournalRecurring2 Public Class MainClass Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object Try Select Case args.FunctionType Case Is = ExtenderFunctionType.Unknown CopyJournal(si) Case Is = ExtenderFunctionType.ExecuteDataMgmtBusinessRuleStep Case Is = ExtenderFunctionType.ExecuteExternalDimensionSource 'Add External Members Dim externalMembers As New List(Of NameValuePair) externalMembers.Add(New NameValuePair("YourMember1Name","YourMember1Value")) externalMembers.Add(New NameValuePair("YourMember2Name","YourMember2Value")) Return externalMembers End Select Return Nothing Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function Private Sub CopyJournal(ByVal si As SessionInfo) Try 'retrieve the names of the workflow parameters and concatenate them together Dim profilePostfix As String = brapi.Workflow.Metadata.GetProfile(si,si.WorkflowClusterPk.ProfileKey).Name Dim scenarioPostfix As String = brapi.Finance.Members.GetMemberName(si,dimtype.Scenario.Id, si.WorkflowClusterPk.ScenarioKey) Dim timePostfix As String = brapi.Finance.Members.GetMemberName(si, dimtype.Time.Id, si.WorkflowClusterPk.TimeKey) Dim strPostfix As String = $"{profilePostfix}_{scenarioPostfix}_{timePostfix}" 'The name of the journal to copy Dim journalName As String ="Tax Accruals_Houston.journals_Actual_2011M2" Dim oldJournalObject As JournalEx = BRApi.Journals.Metadata.GetJournalOrTemplate(si, journalName) brapi.ErrorLog.LogMessage(si, journalName) 'Remove the postfix from the old journal name to get the name Dim strOldName As String = oldJournalObject.Header.Header.Name Dim oldProfileName As String = brapi.Workflow.Metadata.GetProfile(si,oldJournalObject.Header.Header.WorkflowProfileID).Name strOldName = strOldName.Remove(strOldName.IndexOf(oldProfileName)) brapi.ErrorLog.LogMessage(si, strOldName) 'create a copy of the journal header Dim journalObjectHeader As New JournalHeader(oldJournalObject.Header.Header) 'Update the required parameters journalObjectHeader.Name = $"RF_{strOldName}{strPostfix}" journalObjectHeader.Description = $"Roll Forward from {oldJournalObject.Header.Header.name}: {oldJournalObject.Header.Header.Description}" journalObjectHeader.UniqueID = Guid.NewGuid() journalObjectHeader.MemberIds.Scenario = si.WorkflowClusterPk.ScenarioKey journalObjectHeader.MemberIds.Time = si.WorkflowClusterPk.TimeKey journalObjectHeader.WorkflowProfileID = si.WorkflowClusterPk.ProfileKey ' Log the unique ID Of the journal before posting brapi.ErrorLog.LogMessage(si, $"Journal UniqueID: {journalObjectHeader.UniqueID}") 'Journal status as working journalObjectHeader.JournalStatus = JournalStatus.Working 'Create a copy of the journal line item using linq Dim journalObjectLineItems As list(Of JournalLineItem) = oldJournalObject.LineItems.Select(Function(x) New JournalLineItem(x.LineItem)).tolist 'Create a new journal object Dim journalObject As New Journal(journalObjectHeader, journalObjectLineItems) 'Save it BRApi.Journals.Metadata.SaveJournalOrTemplateUsingIds(si, journalObject, False, True) ' Post the journal using ExecutePost BRApi.Journals.Process.ExecutePost(si, journalObjectHeader.UniqueID) brapi.ErrorLog.LogMessage(si, $"Journal '{journalObjectHeader.Name}' posted successfully.") Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Sub
Workspace Assembly - Call functions from Finance or Connector business rules
Hi Everyone, has anyone already tried to reference a Workspace Assembly business rule from a Finance or a Connector business rule? I have a business rule that is packaged to an Assembly in a Workspace: It has a set of public functions returning dictionaries and other variables. And I would like these functions being called from a Connector. I can successfully reference it from another Assembly workspace. But when I am using the same syntax in a connector, that is not working: I am assuming the referenced Business rule must be declared in the Properties, but I am not sure of the syntax. Or even sure this is accepted. Many thanks to anyone who can helps!
Error in Business Rule for Data Mgmt Process
Data Mgmt sequence and steps work great when utilizing a 'manual' initiation of this process and business rule. However, the DM process automatically initiates twice daily at specific times using the same code and rules. When this occurs, it fails on occasion (not always), and this error message is received: Summary: Error processing Data Management Step 'ProcessRecons_RCM'. Unable to execute Business Rule 'RCM_DataMgmtProcess'. One or more errors occurred. () Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. This is the business rule in use... asking if anyone can spot an obvious issue I have overlooked or might know the cause of the failed task on occasion. Business Rule: Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object Try Select Case args.FunctionType Case Is = ExtenderFunctionType.ExecuteDataMgmtBusinessRuleStep 'Prepare Parameters Dim wfProfile As String = args.NameValuePairs.XFGetValue("WFProfile", String.Empty) Dim wfTime As String = args.NameValuePairs.XFGetValue("WFTime", String.Empty) 'Load Substitution Variables To Parse WFProfile and WFTime Using dbConnFW As DbConnInfo = BRApi.Database.CreateFrameworkDbConnInfo(si) Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si) Dim subVarInfo As SubstVarSourceInfo = SubstitutionVariablesHelper.CreateSubstVarSourceInfo(dbConnFW, dbConnApp, False) wfProfile = SubstitutionVariableParser.ConvertString(si, subVarInfo, Nothing, wfProfile) wfTime = SubstitutionVariableParser.ConvertString(si, subVarInfo, Nothing, wfTime) End Using End Using 'Users Can Supply Either WFProfileName or WFProfileID. If Name Is Supplied Resolve To ID Dim wfProfileID As Guid = Guid.Empty Dim wfProfileInfo As WorkflowProfileInfo = Nothing If Not Guid.TryParse(wfProfile, wfProfileID) Then 'Profile Name Was Supplied wfProfileInfo = BRApi.Workflow.Metadata.GetProfile(si, wfProfile) Else 'ProfileID Was Supplied wfProfileInfo = BRApi.Workflow.Metadata.GetProfile(si, wfProfileID) End If 'Validate WFProfile Supplied Is A Valid Profile If wfProfileInfo Is Nothing Then Throw New XFUserMsgException(si, Nothing, Nothing, $"Error: Invalid workflow profile [{wfProfile}]") Else wfProfileID = wfProfileInfo.UniqueID End If 'Users Can Supply Either WFTimeName or WFTimeID. If Name Is Supplied Resolve To ID Dim wfTimeID As Integer = SharedConstants.Unknown If Not Int32.TryParse(wfTime, wfTimeID) Then 'Time Name Was Supplied wfTimeID = BRApi.Finance.Time.GetIdFromName(si, wfTime) End If 'Validate WFTime Supplied Is A Valid Time If wfTimeID = SharedConstants.Unknown Then Throw New XFUserMsgException(si, Nothing, Nothing, $"Error: Invalid workflow time [{wfTime}]") End If 'Set Task Description Dim rScenarioID As Integer = GeneralHelpers.GetStoredSettingAsInteger(si, RCM_SettingsHelpers.StoredSettingName_ReconScenario) Dim wfClusterPk As New WorkflowUnitClusterPk(wfProfileID, rScenarioID, wfTimeID) Dim wfDesc As String = BRApi.Workflow.General.GetWorkflowUnitClusterPkDescription(si, wfClusterPk) Dim fields As List(Of String) = StringHelper.SplitString(wfDesc, ":", StageConstants.ParserDefaults.DefaultQuoteCharacter) Dim wfName As String = If(fields.Any(), fields(0), "WP#Missing") Dim taskInformation As String = $"{wfName}:S#{ScenarioDimHelper.GetNameFromId(si, rScenarioID)}:T#{BRApi.Finance.Time.GetNameFromId(si, wfTimeID)} - {OFC_SharedConsts.DataMgmtTaskPrefixReconProcess}." DataMgmtHelpers.SetTaskDescription(si, args.TaskActivityID, taskInformation) 'Make sure this DM job can run at this time (unless this was called from a UI action and we checked it already) Dim canDataMgmtJobRunChecked As Boolean = args.NameValuePairs.MPGetValueToBoolean(DataMgmtProcessHelper.CanDataMgmtJobRunCheckedKey, False) If Not canDataMgmtJobRunChecked Then Dim dataMgmtHelper As New DataMgmtProcessHelper(si) Dim params As New Dictionary(Of String, String) From { {"WFProfile", wfProfileID.ToString}, {"WFTime", wfTimeID.ToString}} Dim canDataMgmtJobRun As BoolAndTwoStrings = dataMgmtHelper.CanDataMgmtJobRun(OFC_SharedConsts.DataMgmtSeqReconProcess, params, args.TaskActivityID) If Not canDataMgmtJobRun.Bool1 Then Throw New XFUserMsgException(si, Nothing, Nothing, canDataMgmtJobRun.String1) End If End If 'Execute Process Recons Dim errorMessage As String = String.Empty Dim sScenarioID As Integer = GeneralHelpers.GetStoredSettingAsInteger(si, RCM_SettingsHelpers.StoredSettingName_SourceScenario) Dim processHelper As New ProcessReconsHelper(si, wfProfileID, sScenarioID, rScenarioID, wfTimeID, args.TaskActivityID) processHelper.ExecuteProcessRecons(errorMessage) 'Update Task Information Based On Results If Not String.IsNullOrEmpty(errorMessage) Then taskInformation &= errorMessage DataMgmtHelpers.SetTaskDescription(si, args.TaskActivityID, taskInformation, True) End If End Select Return Nothing Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function End Class End Namespace
Issue with Dynamic Calculation for "Translated" Consolidation in a UD1 Grouping
Hello everyone, I am experiencing an issue with a dynamic calculation rule in OneStream for a user-defined dimension (UD1) grouping entities. The rule works correctly for all Consolidation dimensions except for "Translated," where the data is not calculated or displayed at the UD1 level, even though the base entities have values for that member. Context: I have a dynamic calculation rule that sums the values of base entities grouped under a UD1. The rule’s logic is the following: Dim entity As String = api.Pov.Entity.Name Dim text5 As String = api.Entity.Text(5) Dim ud1 As String = api.Pov.UD1.Name Dim ud1Man As String = ud1.Substring(1,3) Dim parent As String = "P" & ud1Man Dim result As Decimal = 0.00 'Filter the entities by Text5 Dim entityPk As DimPk = api.Dimensions.GetDim("Entities").DimPk Dim entityList As List(Of MemberInfo) = api.Members.GetMembersUsingFilter(entityPk, "E#PL00.Base.Where(Text5 Contains '" & ud1 &"')", Nothing) 'If we deploy the entities with any of the parent entities we will see the totals If entity.Equals("PL00") Or entity.Equals("P000") Then For Each entityMember As MemberInfo In entityList Dim entityLooped As String = entityMember.Member.Name result = result + api.Data.GetDataCell("E#" & entityLooped & ":P#" & parent & ":U1#None").CellAmount Next Return result End If The rule works perfectly for Consolidations like "Local" and "OwnerPreAdj," but not for "Translated." I have manually confirmed that the base entities have values in the "Translated" Consolidation via Cube Views. Example: For the period 2024M8: UD1 S043 groups the entities E043 and E146. Both entities (E043 and E146) have values in the "Translated" Consolidation. However, the total for S043 in "Translated" appears blank. Questions: Is there anything specific to consider when handling Consolidations like "Translated" in dynamic calculations? Could this be related to how data is configured or consolidated for "Translated" in OneStream? Has anyone faced a similar issue or have recommendations to resolve this? I would greatly appreciate any insights or suggestions! Best regards, Claudia
