The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.
Forum Discussion
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
5 Replies
From the look of it it is a parser rule and you can only use that inside a data source. If you are doing this on a multi culture environment and a finance rule there are xfstring that you can use for a cellamount method
Good point on the multi-culture element from the XFxxxx equivalents of Replace etc. in VB.NET.
In this case I didn't need it, but I'll add in to V2.0!
- JackLacava
OneStream Employee
Thanks Steve!
Where should we place this code?
It's indeed a Parser Rule, so you can use it as a Complex Expression for the Amount field in a Data Source - or you could put it in a Business Rule for re-use if you have multiple Data Sources that need to do the same thing.
