Moiz
Moiz

Reputation: 2439

How to check If value is numeric In SSRS

I am creating a report in SSRS. My dataset query returns me multiple output and I show them in the report in every new line new record.

Here is the query. Now I want to check if the value return is numeric or not. Here is my expression.

=Join(LookupSet(Fields!QuestionId.Value, Fields!QuestionID.Value, 
Fields!ScalarValue.Value, "myResponse"), Constants.VbCrLf)

I Tried working on IIF(isnumeric ... ) but as it returns me multiple result, It does not allow me to format. I want to format that two 2 decimal if the output is numeric.

Upvotes: 2

Views: 10766

Answers (1)

Ian Preston
Ian Preston

Reputation: 39566

IsNumeric is useful here, but the problem with IIf is that is doesn't short circuit, so you might run into errors if you're casting data.

For example, if you use CDec to allow numeric formatting to be applied to a string, it will be applied in all cases and throw an error when the string is not numeric.

A safe way to do this is to create a simple Custom Code function to handle the formatting and use this in the LookupSet expression.

So add some Custom Code to the report like:

Public Function FormattedString(ByVal st as String) as String

    If IsNumeric(st)

      Return Format(CDec(st), "N2")

    End If

  Return st

End Function

And change your expression to use this new FormattedString function:

=Join(LookupSet(Fields!QuestionId.Value, Fields!QuestionID.Value, 
Code.FormattedString(Fields!ScalarValue.Value), "myResponse"), Constants.VbCrLf)

I tested against the following data:

enter image description here

A simple table with the above expression looks as required:

enter image description here

So it's working for me against a mix of data - you can change the formatting logic in the Custom Code as required.

Upvotes: 6

Related Questions