Smart Integration Connector
Smart Integration Connector was launched as a feature of 7.3 and we have a new self-paced eLearning course available for you on Navigator to support this: OneStream Architecture: Smart Integration Connector (SIC) - Welcome to the OneStream Smart Integration Connector | Employee (thoughtindustries.com) Free to everyone who can access Navigator.How to deal with an Amount Field that has space as a thousands separator AND a DR/CR indicator!
If you encounter an Amount field that has spaces as the thousands separator AND includes a DR/CR indicator (e.g. 12 345 678 D) then you'll need this to parse the value: Dim strRawAmount As String = api.Parser.DelimitedParsedValues(4) ' Zero-based and adjust for your columns! '----------------------------------------------------------------------------- ' If not empty then remove space separator and interpret D/C indicators If Not String.IsNullOrEmpty(strRawAmount) Then Dim strAmount As String = strRawAmount.Replace(" ", "") ' 12 345 678 D becomes 12345678D Dim strDRCR As String = Right(strAmount, 1) ' D or C on end of string? Dim dAmount As Decimal = Left(strAmount, len(strAmount) - 1).XFConvertToDecimal ' Strip D or C and convert to Decimal '----------------------------------------------------------------------------- ' Did conversion to Decimal work? If IsNumeric(dAmount) Then ' This is critical '----------------------------------------------------------------------------- api.ValueIsnumeric = True 'This is critical too!!! Select Case strDRCR Case "C" ' Credit Return (dAmount * -1).ToString Case "D" ' Debit Return (dAmount).ToString '----------------------------------------------------------------------------- End Select End If Else '----------------------------------------------------------------------------- ' Empty so bypass line globals.Bypass = True '----------------------------------------------------------------------------- End If Steve
Derivative rules
Hi experts, I have a group of GL accounts that I need to map to either an expense / revenue based on the sum of the balances. I thought this would be easy enough with a derivative rule; first create an interim rule to aggregate the accounts and then create a final rule to check if the balance is greater / less than 0. Finally, I have used the final derivative rule in my target account mapping. But, this is not quite working as expected. It seems to just add the negative balances of the individual GL accounts under the "_N" rule and the positive balances under "_P" rule, therefore both my expense and revenue account in OS ends with a balance. Could you please suggest what I am doing wrong? Regards, S
Unable to get value from another column in Parser BR
OneStream V7.0.1 We have a Data Source that takes data file that has 12 data columns Oct ... Sep. The year for the data in the 12 data columns is a field "Fiscal Year", the first column of the data file. We have a Parser BR that is called on each of the 12 data columns that looks at the first field in each record to get the year and concatenates that with the M<number> month for each month column to give the correct Time dimension member in which to store the data. e.g. 2023M1 The problem is we're getting the row header "Fiscal Year" instead of the year value in the record, so the string being returned looks like Fiscal YearM1 How do we get the year data from the first column instead of the column header? Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As ParserDimension, ByVal args As ParserArgs) As Object Dim fiscalYear As String() = {"Oct","Nov","Dec""Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep"} Dim YearColPosition As Integer = 0 Dim year As String = String.Empty Dim month As String = String.Empty Try 'Both of these keep giving us the column header "Fiscal Year" 'instead Of the value In the currently parsed record year = api.Parser.DelimitedParsedValues(YearColPosition) 'year = args.Line.Split(",")(YearColPosition) 'this marries the index of the month name in the months array 'with M to give us the M1 notation for period. month = $"M{Array.IndexOf(fiscalYear, args.Value)+1}" BRApi.ErrorLog.LogMessage(si, $"Time: {year}{month}") Return $"{year}{month}" Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function
