I have text as follow (some question don't have number which I'll ignore):
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.
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: