ms SSRS 2008 R2: Display Computed Values #Error

Question

The Report will display Two Columns Foo and Bar

Some Rows of Foo are Empty Some have a numerical Value:

 Foo:
+----+
|    |
+----+
|10.0|
+----+

then There is the Bar Column, this column will take the Values from Foo and add 10 to them, the report should yield Results like this:

 Foo: Bar:
+----+----+
|    |    |
+----+----+
|10.0|20.0|
+----+----+

Thats the Expression i use to determine whether Foo is numeric inside Bar:

=IsNumeric(ReportItems!FooBox.Value)

And this is the Result that Expression will Yield:

 Foo: Bar:
+----+-----+
|    |False|
+----+-----+
|10.0|True |
+----+-----+

Okay, thats exactly the way i want it so far, thus i write My Expression that way:

=IIf(IsNumeric(ReportItems!FooBox.Value),
            ReportItems!FooBox.Value +10,
            "")

This will Yield the Following Results:

Foo: Bar:
+----+------+
|    |#Error|
+----+------+
|10.0|20.0  |
+----+------+

And most Bizare, when i remove the little addition in the Truepart of the IIf, it will execute:

=IIf(IsNumeric(ReportItems!FooBox.Value),
                ReportItems!FooBox.Value,
                "")

Foo:   Bar:
+----+------+
|    |      |
+----+------+
|10.0|10.0  |
+----+------+

It's almost as if the "wrong" part of the Ternary operator gets executed Anyway, thus generating this castError or whatever it might be.

How can i convert the Values properly in order to Show the Results as Explained before?

possible duplicate of [SSRS hide #Error displayed in cell](http://stackoverflow.com/questions/9144312/ssrs-hide-error-displayed-in-cell) I voted to close: your question is essentially a duplicate of several others asked before. — Jamie F, Sep 24 '13 at 15:50
Another answer: http://stackoverflow.com/questions/1204179/does-the-iif-function-compute-both-paths-in-ssrs-or-is-it-short-circuited — Jamie F, Sep 24 '13 at 16:14

score 1 · Accepted Answer · answered Sep 24 '13 at 16:16

Indeed - using IIf will cause both statements (true and false) to be evaulated and that leads to the error you are getting.

being in your situation, I would create my own function to handle this - you can place it in Code field of your Report (click outside of Page area to access Report object)

your function might look like this:

Public Function MyAdd(val As Object) As Nullable(Of Integer)
        If TypeOf(val) Is Integer Then
             Return val + 10
        Else
             Return Nothing
        End If
End Function

you might need to play with types used (Object and Nullable Of Integer are maybe not what will work for you)

then, of course, use MyAdd in your expression and pass ReportItems!FooBox.Value as parameter

ms SSRS 2008 R2: Display Computed Values #Error

1 Answers1