1
votes

I'm am having some trouble with conditional formatting in SSRS. I have tried Switch & IIF Statements (below). The report runs but is not returning colors as it I'm hoping it would. I'm trying to highlight dates which are <= today in red, and everything else will be black. Does it matter if this field is a date field? I've seeen other questions on here with the same issues but no resolutions. Was hoping today would be my lucky day to find an answer. Here is what I have tried and thank you in advance for any input.

        =Switch( Fields!Decision_Must_Be_Made.Value <= today(), "Red",  
         Fields!Decision_Must_Be_Made.Value > today(),  "Black")

        =IIF( Fields!Decision_Must_Be_Made.Value <=today(), "Red",  "Black")
2
@selectallfrom- If you found your own solution to the problem, feel free to post it as an answer and accept it. That way the answer is more obvious to future readers.Leigh
Sort of related...the trick with the Switch function is to use True = True as the last item as a kind of "catch all" mechanism.Brian Gideon
Need More Information on What does the Fields!Decision_Must_Be_Made.Value contain as values and its type in the database. Is this is single value you are getting from the database. By default it take the first value and compares. Any Error you are getting after viewing the report I am not facing any issues I am getting values and able to compare with no errors.M.C.Rohith
@Leigh, I thought I did post my asnwer but I do not see it now. When I tried to answer my own question, I receive a notification saying I couldn't answer my own question until 6 hours laters (since I'm still fairly new to the community).selectallfrom
@selectallfrom - (Edit) Yeah, I do not know all the rules for new members. I originally commented because I saw a "pending edit" (not sure if it was approved). In this case, posting a separate "answer" is probably better than updating the original question. So it is easier to find. When you are allowed post an "answer" that is .. ;) Btw, welcome to stackoverflow!Leigh

2 Answers

1
votes

Yes, it definitely matters if the field is a Date Time field. If it's a string, then you need to convert it to datetime first. How you do that will depend on the format of the string. But it will be much better if you can stick with a datetime field from the database. (I've seen where some will format a date to a string in the select of the sql query. Don't do that. Format as late as possible: in SSRS, at the text box level.)

If it is a dateTime, break up your formula to find out what's not working as expected and make it more visible, if only for debugging. Put this in the expression of a cell, for example:

=IIF( Fields!Decision_Must_Be_Made.Value <=today(), "Old",  "New")

Edited to add information on where the color formula should be added:

Sounds like you don't have the IIF specifying the color in the right place. There are a few different places you could specify this: it needs to be in the properties of either the textbox or the placeholder. The value for these things should simply be your date field (=Fields.Decision_Must_Be_Made.Value) but the font color needs to be specified separately. One place to do this is in the Text Box Properties dialog. In the font pane, you need to specify the font color. The Fx symbol indicates that you can specify a formula. Click this button for a place to enter your '=iif...' formula.

Text Box Properties font pane

0
votes

Admittedly this does not answer your scenario but may help someone else. I had an issue where a stand-alone textbox using a scenario where I wanted to display an error message when there was either no record or duplicate records. My formula "=IIf(IsNothing(First(Fields!MyField.Value)) Or First(Fields!MyField.Value) <> Last(Fields!MyField.Value), "Red", "SomeOtherColorButNotBlack") which did not render the correct fore-color (came out "Black") however, doing a similar expression that equates to True or False on a Tablix or Matrix does work fine. Another one for MS to solve. I found my own workaround by setting the color to always be red and then the expression of the Text to be blank when no error.