Report Server - unable to display correct input parameter via Iif() or Switch()

Question

I have a nullable boolean input parameter with the following expression in my textbox:

=iif(Parameters!Sorted.Value="","All",iif(Parameters!Sorted.Value="True","Sorted","Unsorted"))

and I am trying to display this based on the value of Sorted input parameter

Null = "All"

True = "Sorted"

False = "Unsorted"

I have also tried the following switch statement without any luck:

=Switch(Parameters!Sorted.Value="","All",Parameters!Sorted.Value="True","Sorted",Parameters!Sorted.Value="False","Unsorted")

Each time when the report renders I receive the following error:

The value expression for the textbox ‘textbox7’ contains an error: Input string was not in a correct format.

I am using VS2003 and SSR Designer v 8.0

Edit #1: Per request

<ReportParameter Name="Sorted">
  <DataType>Boolean</DataType>
  <Nullable>true</Nullable>
  <Prompt>Sorted</Prompt>
</ReportParameter>

Is this the code you were requesting?

Answer 1

Okay, I think your problem may be that your DataType is Boolean, and you are specifying a blank value. You cannot do that.

Instead, try to specify the keyword Nothing:

=iif(Parameters!Sorted.Value=nothing,"All",iif(Parameters!Sorted.Value="True","Sorted","Unsorted"))

Answer 2

If it is a Boolean type parameter it's not semantically correct to check it as if it were a string.

You can use IsNothing(Parameters!Sorted.Value) to check for a null and then Parameters!Sorted.Value = True (without the quotes) for the second case.

I'll admit I didn't fire up Visual Studio to check if it normally is okay with treating Boolean parameters as if they were a string, but the error you're getting sounds as such. That error is usually thrown by Parse methods, like if you do Int32.Parse("34.32"), or so I seem to recall. I'm guessing RS is doing an automatic parse so that the datatypes match and the equality operator can do its magic.

Answer 3

Why not change the logic to test explicitly for True or False and then drop through to "All"? And without worrying about string, True vs "true" etc

=iif(Parameters!Sorted.Value,"Sorted",iif(NOT(Parameters!Sorted.Value), "Unsorted","All"))

(Can't test and hope NOT is correct :-)