Recent Discussions
Public Overloads Function GetCalculatedFxRate... is obsolete
Is someone able to assist me with an alternative method for GetCalculatedFxRate on line 14 of the code below? We just upgraded to 8.5.1 and this is no longer being supported. In 8.2.3 it was still working. This is a parser business rule that is being applied to a data source and calculation happening when data is being imported. '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 'Purpose: Get Translation rate for To/From currencies on current record. ' Store translation rate in global variable to use with subsequent records for performance improvements. '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Dim time As String = api.CurrentDataClusterKey.TimeName 'Get current time. Dim currencyRate As Decimal = globals.GetDecimalValue(fromCurrency & "-" & toCurrency & "-" & time) 'Check Global variable for current translation rate. If Not currencyRate = Nothing Then 'If Globals is populated, return rate from global variable. Return currencyRate Else 'Else, get translation rate from Database and store in Global variable. Dim rateType As FxRateType = BRApi.Finance.Scenario.GetFxRateTypeForRevenueExpense(si, api.CurrentDataClusterKey.ScenarioID) 'Get Revenue rate type for current scenario Dim defaultCurrencyId As Integer = BRApi.Finance.Cubes.GetDefaultCurrencyId(si, 0) 'Get Cube's default currency ID Dim objActFxRate As New FxRatePkUsingNames(rateType.ToString, time, fromCurrency, toCurrency) 'Get rate information for current translation. Dim rate As Decimal = BRApi.Finance.Data.GetCalculatedFxRate(si, defaultCurrencyID, objActFxRate) 'Get translation Amount Globals.SetDecimalValue(fromCurrency & "-" & toCurrency & "-" & time, rate) 'Store current translation amount in global variable. Return rate 'Return Translation Rate. End If Thank you, Denisedenisefockler25 minutes agoNew Contributor III1View0likes0CommentsAverage 12 months Account formulas
Hi everyone, I suspect that this is fairly simple, but I haven't been able to find a solution as yet. I need to add formulas for Debtor Days, Creditor Days, and Inventory Days, all of which use 12-month averages in the calculation. The also all use the number of days in the month. Does anyone have an example of the syntax needed to include both of these? ThanksSolvedPhilClement19 hours agoNew Contributor44Views0likes2CommentsAccount Reconcillations (RCM) Excluding an Import from Actuals
Hi All, I am currently setting up account reconcillations manager (RCM) and import both YTD GL data and Periodic Flow data in the actual scenario. I want to bring in just the YTD GL data into RCM, but it's bringing in both data sets. How can I exclude the Import Flow Data? I know that I can filter on the workflow profile "Import Flow Data" in the Account Rec inventory and delete them, but I'd rather the flow data just not come into RCM in the first place. Thanks for the help!SolvedCI_RWise5 days agoNew Contributor26Views0likes3CommentsCombining/Joining DataSets
Hi Everyone. I am unable to join datasets and suing below code. Purpose: is to combine the outcome of 2 method queries WF Status and CertificationStatus to get a single table. Kindly help with your comments or guidance: 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.DashboardDataSet.GetWFStatusandCertification Public Class MainClass Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardDataSetArgs) As Object Try Select Case args.FunctionType Case Is = DashboardDataSetFunctionType.GetDataSetNames Dim DSnames As New List(Of String)() DSnames.Add("GetWFStatusandCertificationF") Return DSnames Case Is = DashboardDataSetFunctionType.GetDataSet If args.DataSetName.XFEqualsIgnoreCase("GetWFStatusandCertificationF") Then Dim WFTable As DataTable = New DataTable("WFSTATUS") Dim CertTable As DataTable = New DataTable("WFCertSTATUS") Dim CertTable1 As DataTable = New DataTable("WFCertSTATUS1") Dim CertTable2 As DataTable = New DataTable("WFCertSTATUS2") Dim WFTime As String = "2023M12" 'args.NameValuePairs("2023M12") Dim WFScenario As String = "Actuals" 'args.NameValuePairs("Actuals") Dim WFName As String = "P1000 Group" 'args.NameValuePairs("P1000 Prudential Group") Dim methodTypeId As String Dim methodQuery As String Dim resultDataTableName As String Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si) 'Create Tables in Memory WFTable.Columns.Add("ProfileKey") WFTable.Columns.Add("ProfileName") WFTable.Columns.Add("ScenarioName") WFTable.Columns.Add("TimeName") WFTable.Columns.Add("StatusText") WFTable.Columns.Add("LastExecutedStepStatus") WFTable.Columns.Add("LastExecutedStepTimeUTC") WFTable.Columns.Add("LastExecutedStepTimeEST") methodTypeId = XFCommandMethodTypeId.WorkflowStatus methodQuery = "{" & WFName &"}{" & WFScenario & "}{" & WFTime & "}{AllProfiles}{Descendants}{}" resultDataTableName = "WFSTATUS" Dim customSubVars As New Dictionary(Of String, String) Dim objDataSet As DataSet = BRApi.Database.ExecuteMethodCommand(dbConnApp,methodTypeId,methodQuery, resultDataTableName,customSubVars) For Each Row As DataRow In objDataSet.Tables("WFSTATUS").Rows Dim easternZone = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time") Dim EST = TimeZoneInfo.ConvertTimeFromUtc(Row.Item("LastExecutedStepTime"), easternZone) WFTable.Rows.Add(Row.Item("ProfileKey"),Row.Item("ProfileName"),Row.Item("ScenarioName"),Row.Item("TimeName"),Row.Item("StatusText"),Row.Item("LastExecutedStepStatus"),Row.Item("LastExecutedStepTime"),EST) Next 'Return WFTable End Using Using DBConAppForCert1 As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si) CertTable1.Columns.Add("ProfileName") CertTable1.Columns.Add("ProfileKey") CertTable1.Columns.Add("ScenarioKey") CertTable1.Columns.Add("ScenarioName") CertTable1.Columns.Add("TimeKey") CertTable1.Columns.Add("TimeName") methodTypeId = XFCommandMethodTypeId.CertificationForWorkflowUnit MethodQuery = "{" & WFName & "}" & "{" & WFScenario & "}" & "{" & WFTime & "}" &"{True}" &"{}" resultDataTableName = "WFCertSTATUS1" Dim CustomSubVars As New Dictionary(Of String, String) Dim ObjDataSet As DataSet = BRApi.Database.ExecuteMethodCommand(DBConAppForCert1,methodtypeid,methodQuery,resultDataTableName,CustomSubVars) For Each Row As DataRow In ObjDataSet.Tables("WFCertSTATUS1").Rows CertTable1.Rows.Add(Row.Item("ProfileName"),Row.Item("ProfileKey"),Row.Item("ScenarioKey"),Row.Item("ScenarioName"),Row.Item("TimeKey"),Row.Item("TimeName")) Next ' Return CertTable1 End Using Using DBConAppForCert2 As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si) CertTable2.Columns.Add("ProfileKey") CertTable2.Columns.Add("ScenarioKey") CertTable2.Columns.Add("TimeKey") CertTable2.Columns.Add("SignOffState") CertTable2.Columns.Add("UserName") CertTable2.Columns.Add("TimeStamp") methodTypeId = XFCommandMethodTypeId.CertificationForWorkflowUnit MethodQuery = "{" & WFName & "}" & "{" & WFScenario & "}" & "{" & WFTime & "}" &"{True}" &"{}" resultDataTableName = "WFCertSTATUS2" Dim CustomSubVars As New Dictionary(Of String, String) Dim ObjDataSet As DataSet = BRApi.Database.ExecuteMethodCommand(DBConAppForCert2,methodTypeId,MethodQuery,resultDataTableName,CustomSubVars) For Each Row As DataRow In ObjDataSet.Tables("WFCertSTATUS2_SignOffGroups").Rows CertTable2.Rows.Add(Row.Item("ProfileKey"),Row.Item("ScenarioKey"),Row.Item("TimeKey"),Row.Item("SignOffState"),Row.Item("UserName"),Row.Item("TimeStamp")) Next ' Return CertTable2 End Using ' ' Return WFTable Dim DS_CertStatus As New DataSet("DS_CertificationStatus") DS_CertStatus.Tables.Add(WFTable) ' DS_CertStatus.Tables.Add(CertTable1) DS_CertStatus.Tables.Add(CertTable2) DS_CertStatus.Relations.Add("ProfileKeyRelation",DS_CertStatus.Tables(0).Columns("ProfileKey"),DS_CertStatus.Tables(1).Columns("ProfileKey"),False) Return DS_CertStatus 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 NamespaceSolvedSKMundra7 days agoNew Contributor III115Views0likes12CommentsGetBaseMembers: How can I perform a getmembers on an entity as if it were in a cube view?
Hi Everyone. I wanted to see if there is a way to obtain the entities that are under another one but with a filter, like the following: E#|!PerformPnL_Entity!|.TreeDescendantsInclusive.Where(Text1 StartsWith M_ and (HasChildren = True)) I want to do this in a BR, but I am not sure exactly how to do it or if it is possible, since GetBaseMembers brings all of them and I would like to filter it first so that there are not too many entities to verify. I would appreciate it if you could help me with this question.Marco8 days agoContributor II24Views0likes1CommentJournal Name Creation on Event Handler
Hi Has anyone created an event handler around the journal name creation? We have a requirement around enforcing the journal name to meet a specific criteria + be within a certain number of characters in length ThanksABott738 days agoNew Contributor II25Views0likes2CommentsSyntax Starter
Hey - I seem to recall something/somewhere in the application I could reference to find sample syntax such as common financial logic and other starters. My memory is fuzzy, but I thought it was almost like a dialog box similar to the member filter builder, but inside the application. I can't seem to locate it in the application nor find reference material. Am I losing my mind?SolvedCAIGuySeanF11 days agoContributor24Views0likes2CommentsHelp with Table Views
Hello, I'm attempting to create my first table view using the syntax below. The SQL is a very basic record grab from DataEntryAuditSource (initial POC). Compiles fine and seems to be okay relative to the user guide, but I keep getting an error message when I attempt to run. Does anyone have any thoughts on what I could be doing wrong? Thanks, Sean ----- 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.Spreadsheet.UTM_TaskList Public Class MainClass Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As SpreadsheetArgs) As Object Try Select Case args.FunctionType ' Case Is = SpreadsheetFunctionType.Unknown ' Case Is = SpreadsheetFunctionType.GetCustomSubstVarsInUse Case Is = SpreadsheetFunctionType.GetTableView If args.TableViewName.Equals("SeansFirstTableView") Return GetUTMTaskListExport(si) End If ' Case Is = SpreadsheetFunctionType.SaveTableView End Select Return Nothing Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function #Region "Get Table Views" Private Function GetUTMTaskListExport(ByVal si As SessionInfo) As TableView Try Dim SQL_TaskListExport_L As New Text.StringBuilder SQL_TaskListExport_L.AppendLine(" SELECT DataEntryAuditSource.UserID as ID, DataEntryAuditSource.TimeStamp as TimeStamp, DataEntryAuditSource.UniqueID as RecordID, DataEntryAuditSource.CubeVieworFileName as Format, DataEntryAuditSource.DataEntryType as Type, FROM DataEntryAuditSource ") 'Create and fill the DataTable Dim DT_TaskListExport_L As DataTable = Nothing Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si) dt_TaskListExport_L = BRApi.Database.ExecuteSql(dbConnApp, sql_TaskListExport_L.ToString, False) If Not dt_TaskListExport_L Is Nothing Then dt_TaskListExport_L.TableName = "NoData" End Using 'Create and Populate Table View Dim tv_TaskListExport As New TableView() tv_TaskListExport.PopulateFromDataTable(dt_TaskListExport_L, True, True) 'Table View Settings and Formatting tv_TaskListExport.CanModifyData = False tv_TaskListExport.HeaderFormat.BackgroundColor = XFColors.XFDarkBlueBackground tv_TaskListExport.HeaderFormat.TextColor = XFColors.White tv_TaskListExport.HeaderFormat.IsBold = True tv_TaskListExport.Columns.Item(1).ColumnFormat.ColumnWidth = 15 Return tv_TaskListExport Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function 'GetUTMTaskListExport #End Region End Class End NamespaceCAIGuySeanF12 days agoContributor47Views0likes3Comments