Reputation: 2439
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
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:
A simple table with the above expression looks as required:
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