CODEWITHSUNDEEP
sortingreporting-services
user3353158
user3353158

Reputation: 23

SQL Server Reporting Studio (SSRS) sorting error

I am attempting to allow a dynamic sort on a text box on an SSRS report. The field upon which I am trying to sort will either have an "A" or a decimal number. I am wanting to sort the decimal numbers in descending order. The expression I am using is:

=iif(isnumeric(Fields!CommScore.Value), (cdbl(Fields!CommScore.Value)*-1),6)

For the decimal number will never be larger than 5. The error I get is:

The sortexpression for the text box 'textbox74' contains an error. Input string was not in a correct format. (rsRuntimeErrorInExpression)

I imagine this is something simple. What am I doing wrong?

Upvotes: 1

Views: 2417

Answers (1)

Chris Latta
Chris Latta

Reputation: 20560

The error relates to the CDbl function throwing an exception when trying to convert A to a number. Yes, I know you're checking if it is numeric first but IIF is not a language construct, it is a function and as a function it evaluates all its parameters before passing them to the function. This means that both the true and false parameters get calculated even though one will be discarded.

Try the Val function. It has the benefit of not erroring when it gets passed non-numeric data - it just does the best it can to convert it. 

=IIF(IsNumeric(Fields!CommScore.Value), (Val(Fields!CommScore.Value)*-1), 6)

Upvotes: 1

Related Questions