SSRS Format a multi-value field

Question

I have a number field in my ssrs report that has multiple values separated by a comma. I want to convert the number to currency but I get an error when the field has more than on value. I'm converting the text box to currency and added the (*) operator to multiply by .01 so my numbers are not inflated. I'm sure the comma is what causing the error as BIDS is expecting a number, but I'm not sure how to write the expression so that it only modifies the numbers in the string.

=Fields!field_A.Value * .01

Field
Number1 = converts to currency($Number1)
Number1, Number2 = #Error
number1, number2, number3 = #error

Check out this post - if you need to extract just the Number1 section from the field, you can use the answer's code (modified to extract based on a comma, not a blank space). http://stackoverflow.com/questions/5819335/remove-all-characters-after-first-white-space-in-ssrs-2005 — AHiggins, Jul 30 '14 at 18:27
I don't want to extract, I want the additiona number values to also have the currency value. so if Number1, Number2 = $number1, $number2 and the same if there is a third value — D-ONE, Jul 30 '14 at 18:38
need decimals. i'm getting the number from ssms so if the value is 1000, i need it to be $10.00. that why I'm multiplying by .01 — D-ONE, Jul 30 '14 at 18:46
Is there a known limit to the number of numbers in a single field? — AHiggins, Jul 30 '14 at 18:48
the limit is undetermined. it can be one number to a set of 10 possibly more. — D-ONE, Jul 30 '14 at 19:03
If there was a limit, I'd say to use this (http://stackoverflow.com/questions/6928888/ssrs-expression-to-split-string-possible) ... but with no limit, I'd tell you to go back to your data source and use some sort of function to split, format, and rearrange your data. — AHiggins, Jul 30 '14 at 19:10
I created a proc and I'm already splitting the string. I want the report to look the way it does, I just need to make sure each number value has a decimal place and the currency symbol — D-ONE, Jul 30 '14 at 19:12
@AHiggins I don't want to split the values into a row each, I need the values to stay in one row. the solution you suggested would over complicate what I need — D-ONE, Jul 30 '14 at 19:15
Can you change your proc to do the formatting / multiplication as part of the csv concatenation? — AHiggins, Jul 30 '14 at 19:16
I can but the formatting should be done on the front end. that would hinder performance and I'm already pulling a large amount of data. hopefully someone has an answer for me — D-ONE, Jul 30 '14 at 19:29
and formatting in the proc would still leave the problem where I need every value to have a currency symbol — D-ONE, Jul 30 '14 at 19:49

score 1 · Accepted Answer · answered Jul 31 '14 at 07:02

You can use embedded code in the report to do the processing of the string. This allows you to use the full VB.NET language. From the report menu in SSRS select Report Properties and select the Code section on the left. (This is in VS2010, I'm not sure what version of SSRS you're building in.)

Then you can use a VB.NET function like the one below to do the actual string processing. I'm not sure exactly how you want the output so you'll want to read the MSDN page on the FormatNumber() VB.NET function.

Function ConvertCashString(orig As String)
    Dim newString As String = ""
    For Each s As String In orig.Split(",")
        Dim parsedString As Double
        If (Double.TryParse(s, parsedString)) Then
            newString += "$" + FormatNumber(parsedString * 0.01, 2, TriState.True, TriState.UseDefault, TriState.False) + ", "
        End If
    Next

    Return newString
End Function

Then in the report itself you can call the function like so:

 =Code.ConvertCashString(Fields!dbString.Value)

You should also read the MSDN page: Add Code to a Report (SSRS)

SSRS Format a multi-value field

1 Answers1