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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jason YousefSr. 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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jason YousefSr. 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.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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 YousefSr. 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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.