Recent Discussions
Entity Relationship Properties update via business rules
Hi Team, I would like to fetch and update the Entity Relationship Properties for the Entity Dimension members via Business Rules. The below peace of code works fine for only when I pass the Actual and Budget but when i pass the other scenario Type like Scenariotype 1 to 8 I get the error Object is not set to an instance of an object. Dim Scenario As String = args.CustSubstVarsAlreadyResolved("Param_SecnarioType_EO_24") 'The above value i get it from the combo box as delimited list Dim Scenarioid As Integer = BRApi.Finance.Members.GetMemberId(si,dimtype.Scenario.Id,Scenario) Dim MyScenarioTypeid As ScenarioType = BRApi.Finance.Scenario.GetScenarioType(si,Scenarioid) Dim Timefilter As String = args.CustSubstVarsAlreadyResolved("Param_Time_24") Dim Timeid As Integer = BRApi.Finance.Members.GetMemberId(si,dimtype.Time.Id,Timefilter) Can you please help me with the correct way to get the scenario Type.
Where does Stored Calculations store Data?
Today, a customer asked me:Where do stored calculations store data? api.Data.Calculate If Statements In stored calculations, If Statements are helpful to specify that a formula is only required to execute on specific Data Units, as shown in the image below. If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyForEntity()))contains two logical operators (AndandNot). Due to theANDoperator, the statement api.Data.Calculate("S#Forecast = S#Actual") betweenThenandEnd Ifwill only run if both of the following are true: The Entity is a Base-level Entity Member, i.e.: doesNOThave Children (Not api.Entity.HasChildren()) The Consolidation Member is Local indicating the Entity Member's local currency (api.Cons.IsLocalCurrencyForEntity()) The statementapi.Data.Calculate("S#Forecast = S#Actual")will copy the S#Actual data buffer to the S#Forecast data buffer. TheIf...End Ifis limiting the calculation statement to execute only for specific Data Unit Dimension Members. Other consideration to take into account when creating store calculation rules.💥 api.Data.Calculate("A#CashCalc=RemoveZeros(A#10000)")uses the RemoveZeros function to remove cells with No Data or 0.00 cells in the A#10000 data buffer. The results are stored in the A#CashCalc data buffer. The RemoveZeros function is to help with performance if there are large amounts of No Data or 0.00 cells. END IF😂😀😃
How to sum three times in a single getdatacell
Hi Everyone. What I want to achieve is to obtain the amount of three times in a single getdatacell. What I have is the following: Dim first As Decimal = api.Data.GetDataCell("S#[" & strScenario & "]:" & timesTest(0) & ":E#30801_30800:A#FINVOLUME:F#None:O#BeforeAdj:U1#D0000:U2#P0100:U3#LOCAL_DATA:U4#PJ00000").CellAmount The issue is that I need to use three different times, but I don’t want to create three variables to sum those values, and I also don’t want to do something like the following: 'Example Dim tot as Decimal = api.Data.GetDataCell("#Time1" + "#Time2" + "#Time3") So, I wanted to see if there is a way to put all the times together in one so that the sum is done internally, or if it can only be done as shown above. I hope you can explain the best method to achieve this.
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
Exporting OneStream Data to Snowflake Database Parallel
Hello Team I am currently using the "FDXExportDataUnit" function to load data into a DataTable using the following code: Dim dt As DataTable = BRApi.Import.Data.FdxExecuteDataUnit(si,"FPA_CM","E#Entity_top.base","USD",scenarioTID,Scenario,timefilter,"MTD",True,"Amount <> 0 And Flow='EB'",16,False) he resulting DataTable contains 678,123 records. I plan to insert these records into a Snowflake database. To speed up the inserts, I am dividing the DataTable into multiple DataTables of 150,000 records each. However, the inserts are currently happening sequentially, and I want to run them in parallel. I am considering the following options: Pass the split DataTable as a parameter to a Data Management sequence: This would allow me to call the sequence using "QueueDataManagementSequence" so the process runs on different servers. I need assistance on how to pass the DataTable as a parameter to the sequence. Run bulk insert SQL statements in parallel using threading tasks: I need help constructing the Parallel.ForEach loop. My current idea is as follows which is not working parallel.Foreach(splitTable.AsEnumerable(),sf_load.PrepareSqlInsertQueryWithoutColumns(si ,"dev_fin360_core.fpa_integration.CM_CUBE", splitTable, "SFDEVW","")) Any input or suggestions would be greatly appreciated. Best Regards Karun
SFTP Connection with PPK file
Hi Everyone, Has anyone worked on establishing an SFTP connection in extensibility rules using a PPK file? I am currently facing an issue where, despite setting theSshPrivateKeyPathproperty in the session options, I receive the error: "No supported authentication methods available (server sent: publickey)." Any insights or solutions would be greatly appreciated. Thank you, MithunPDF file "damaged" on Extract
Hello, I have created an extensibility rule to extract values from the FileBytes column in the XFW_UTM_SupportDoc table, then convert the FileBytes to a file and save in the file share. See below for code snippet: 'Execute query and save attachment to file share folder Using dt As DataTable = BRAPi.Database.ExecuteSql(dbConnApp, fileBytesQuery, True) If Not dt Is Nothing Then For Each dr As DataRow In dt.Rows 'Process rows Dim fileBytesValue As Byte() = dr("FileBytes") Dim fileNameValue = dr("FileName") Dim taskNameValue = dr("TaskName") Dim fullFileName As String = fileNameValue 'Create file in file share, and store folder name Dim fileInfo As XFFileInfo = New XFFileInfo(FileSystemLocation.FileShare, fullFileName.toString) fileInfo.FolderFullName = "Applications/" & applicationName & "/Batch/Harvest/TaskManager_Exports" 'Convert fileBytes to file, and save in folder path Dim fileFile As XFFile = New XFFile(fileInfo, "", fileBytesValue) BRApi.FileSystem.InsertOrUpdateFile(si, fileFile) Next End If End Using This code runs successfully for .xlsx, .csv, and .docx files. It also executes without error for .pdf files, but I am met with this error after I download the .pdf file and open: Any thoughts on why this is happing specifically for .pdf files?derivative rule syntax
Hello, Im tryin to derive few rows like this: E#[*]=HQ_MANTUGR:A#[R69204ALLOC]=R79204ALLOC:U7#[MREA01]=AREA01:U8#[EUR]=None is there any otpion i can use to tell the system to replace only the "6" in the account nameR69204ALLOC by 7 to getR79204ALLOC and the "M" in the UD7 Name by A to getAREA01 ? if this requires a business rule and you have one exemple please share it with me 🙂 thanks for you help
