iif statement and ssrs 2005

I have a 2005 report that uses the following to change the background color of the cells in the report:

=iif(Fields!StatusSort.Value = 1, "Orange",
iif(Fields!StatusSort.Value = 2, "LightGreen",
iif(Fields!StatusSort.Value = 3, "Yellow","")))

This works correctly, however, I need to add more colors for different results, but the StatusSort values for the rest of the results are all "4" in the database (which I have no control over changing), so I wanted to change the field this was using to the following (was StatusSort, now is just Status - statussort is a number value, Status is a string value)

=iif(Fields!status.Value = "Review", "Orange",
iif(Fields!Status.Value = "O/D - Prin", "LightGreen",
iif(Fields!Status.Value = "O/D - Inc", "Yellow",
iif(Fields!status.Value Like "Uninvested","SteelBlue",""))))      

This just brings back rows with no colors for any of the results - no errors.

I also tried this:
=iif(Fields!status.Value.ToString().Equals("Review"), "Orange",
iif(Fields!status.Value.ToString().Equals("O/D - Prin"), "LightGreen",
iif(Fields!Status.Value.ToString().Equals("O/D - Inc"), "Yellow",
This brings back the following error: The backgroundcolor expression for the textbox 'status' refers to the field 'Status'. The report item expressions can only refer to fields within the current data scope or, if inside an aggregate, the specified data set scope. Now I know the field is in the data set since I can pick it in the drop down, but clearly I'm missing something - any thoughts?

Who is Participating?
Jason Yousef, MSSr. BI  DeveloperCommented:
Looks like you've spaces, maybe that's why it doesn't validate.  try that...
USE TRIM (don't remember if it was in 2005) or RTRIM, LTRIM

Also you could use SWITCH for clearer IIF.

=iif(LTRIM(RTRIM(Fields!status.Value)) = "Review", "Orange",
iif(LTRIM(RTRIM(Fields!Status.Value)) = "O/D - Prin", "LightGreen",
iif(LTRIM(RTRIM(Fields!Status.Value)) = "O/D - Inc", "Yellow",
iif(LTRIM(RTRIM(Fields!status.Value)) Like "Uninvested","SteelBlue",""))))

Open in new window

Jason Yousef, MSSr. BI  DeveloperCommented:
I'm sorry, I didn't notice LIKE in your query... use   INStr()
you need a case statement in your code tab (behind code).
that way all you do is pass in the StatusSort valve and use the case statment to select the colors you want then passes back to the textbox background colors.
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

kelleigh2Author Commented:
Huslayer - thanks for the reply. Unfortunately, this still gives me the same error as above.

planocz - I'm not sure what you mean (I'm pretty new at this). I would open up the code tab and put this where? For each cell (this line has about 10 cells, so each one has this background color statement) or somewhere else? Can you give me an example of the statement? Thanks.
Jason Yousef, MSSr. BI  DeveloperCommented:
@planocz is right, that would be the most efficient way is to create a case statement in your query to pass the value and use that in your IIF or even Switch.

would be something like that..
Select *, [Color_Code] = case 

when status like '%Review%' then 'Orange'
when status like '%O/D - Prin%' then 'LightGreen'


from table

Open in new window

Here is an example to place in the code tab area....

Public Function BackColor(ByVal StausSort as String) as String
Select Case StatusSort
     Case "1"
          Return "Orange"
     Case "2"
          Return "LightGreen"
     Case "3"
          Return "Green"
     Case "4"
          Return "Yellow"
     Case "5"
          Return "SteelBlue"
     Case "6"
          Return "SteelBlue"
End Select
Return Nothing
End Function

In the textbox windows properpties background...
add this expression


I am not at my main PC right now so I have not tested this.
When you are getting back no color, it means nothing is matching. Is your query case-sensitive?
You can try going one by one. Try checking for a single value and assign the color.

Also check the field which you are representing. Somewhere you are using status while sometime Status
kelleigh2Author Commented:
Thanks for the help - both the trim and the case-sensitive were the issues that solved this! Happy Holidays!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.