VB if statement issue in SQL SSRS

Question

I am running SQL Server 2008 R2 with SSRS.

I have some vb code embedded in a simple SSRS report. The column details in my SSRS report returns a value of either 0,2 or 3. Value 2 also seems to change to green each time...

My vb code is there to just change the cell to red, orange or green depending on the number in the cell. It works for 0 and for 3 but not for number 2 for some arbitrary reason....

My code is as follows:

Public Shared Function GetColor(ByVal value as Int32) As String
    If value = 0 Then
    return “Green"
    ElseIf value = 2 Then
    return "White"
    Else
    return "Red"
    End IF
End Function

I then reference this using an expression for background color of the cell. With the following code:

=Code.GetColor(Fields!status.Value)

Please let me know if additional info is needed.

Is there a reason you have to use custom vb script rather than an iif or switch statement in the expressio? — SFrejofsky
Your code and your question seem to be different as well. In you question you reference red orange and green and in your code you are using green white and red. can you confirm which is correct as this may be your problem as you have number 2 referenced to return white rather than orange. — SFrejofsky
@SFrejofsky can you suggest an iif or switch statement that you would recommend I try? I want number 2 to be orange, i put white in there as I was trying different colours to try and get it to work. — scryptor1

SFrejofsky SFrejofsky · Accepted Answer · 2015-04-21T11:55:57

Here is a switch statement that is much simpler then using custom code. Switch statements are incredibly useful when working with more complex if else logic and allows you to organize your results easier. It also does not leave you searching thru a mess of custom code to find the expression that needs work if something needs to change.

You will need to put this is the "Fill" expression in the textbox properties.

=switch(fields!status.Value = 0, "green",
fields!status.Value = 2, "orange",
fields!status.Value = 3, "red")

I used the following dataset to test this.

SELECT        0 AS Expr1, 'green' AS Expr2
UNION ALL
SELECT        2 AS Expr1, 'orange' AS Expr2
UNION ALL
SELECT        3 AS Expr1, 'red' AS Expr2

Using this switch yielded the following results.

enter image description here

VB if statement issue in SQL SSRS

1 Answers