SSRS report expression issue

Question

I have text as follow (some question don't have number which I'll ignore):

1. Question 1
2. Question 2

and I'm trying to extract the question numbers to sort them A-Z, if there is a question number or assign 0 if there is no number. For that I came up with this expression: =IIf(IsNothing(InStr(Fields!questiontext.Value,".")), 0, CInt(Left(Fields!questiontext.Value,InStr(Fields!questiontext.Value,"."))))

Now, when I run my report I get following: Converting from string "" to type integer is not valid.

I was expecting that the expression return 0 for the questions without number and return the number of the question if there is any.

Any help is appreciated, thanks.

Answer 1

Haven’t had to work with SSRS in a long time, but testing this out I recalled one annoying bit about it: iif() always evaluate both sides. So regardless of your check, if CInt() can’t convert the substring to int you’ll get an #Error.

Try using Val(). It defaults to 0 if it can’t convert, so you don’t even need the iif with all the duplication:

=Val(Left(Fields!questiontext.Value, InStr(Fields!questiontext.Value, ".")))

Technically this includes the period in the substring, but Val() can handle it and decrementing the length parameter will result in #Error in cases where it goes negative.

The results will look like this: