How to use expressions for cell colours in SSRS

I have a report on SSRS that produces a range of dates (activity_date) of a particular field in a SQL DB. What I want to do is create an expression so that the cell appears red/yellow/green based on the value of another date (valid_to) of the same field if the activity_date is greater than 2 weeks, less than two weeks or late.

Is this possible and if so how can I do this?
MSSC_supportAsked:
Who is Participating?

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

x
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.

Vikas GargAssociate Principal EngineerCommented:
Hi,

You can have that using the cell properties.

Then you can give condition to the fill property and as per the expression the cell color can be set dynamically as shown in the Image below.

SSRS Cell Color
MSSC_supportAuthor Commented:
I see, but how can I use the date from valid_to rather than the activity_date which is showing on the cell? Can I reference using the expression the table and the field where this date comes from?
Vikas GargAssociate Principal EngineerCommented:
Yes,

In the expression you can find all the fields of the dataset and use them accordingly.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

MSSC_supportAuthor Commented:
Can you please show me how?
ValentinoVBI ConsultantCommented:
Is the valid_to located in the same dataset as activity_date?  If yes you can just use it as Vikas mentioned in his screenshot. If not then it's a bit more complicated and the answer depends on your particular situation.  So if that applies to you then let me know and I'll elaborate.
MSSC_supportAuthor Commented:
Yes it is in the same dataset.
Vikas GargAssociate Principal EngineerCommented:
Hello,

then it is as simple as I explained in the screenshot.

=IIF(activity_date='condition',"red",Yellow) and for multiple condition use multiple iif loop.
MSSC_supportAuthor Commented:
Hi Vikas,

Sorry if I am not being clear. Activity_date is what is showing in the cell.

Valid_to date is the reference that I want to use it against for the conditional formatting.

Or please let me know if I am not understanding it correctly.

Thanks.
Vikas GargAssociate Principal EngineerCommented:
HI,

You can write this expression in the cell Properties => Fill and click on Expression as shown in the screenshot.

The expression is as follows

=IIF(Datediff("d",Fields!Activity_date.Value,Fields!Valid_to.Value)=14,"red",IIF(Datediff("d",Fields!Activity_date.Value,Fields!Valid_to.Value)>14,"yellow","green"))

You can change color as per your required logic.
MSSC_supportAuthor Commented:
OK that seems to work but everything that doesn't have an entry is showing as yellow. Can we also add in this expression that if the valid_to date has elapsed from today it remains red unless there is an activity_date. How can add this to compound with the previous?
MSSC_supportAuthor Commented:
Ok to make it easier for the report I have done the following:


RED                       Valid to is prior to today AND Activity date is null
ORANGE               Valid to is prior to today AND Activity date is NOT null
YELLOW         Valid to is between today and 14 days ahead of today and activity date is null
BLUE               Valid to is in the future and activity date is NOT NULL
GREEN               Valid to is 15 or more days ahead of today and activity date is NOT NULL

Are you able to help me build the IF statement based on this?
ValentinoVBI ConsultantCommented:
MSSC_support: it would help if you would state your requirements straight away in the question...

You can use the Switch function for this:

= Switch(Fields!validto.Value < Now() and IsNothing(Fields!activitydate.Value), "Red",
  Fields!validto.Value < Now() and Not IsNothing(Fields!activitydate.Value), "orange",
  Fields!validto.Value >= Now() and Fields!validto.Value < DateAdd(DAY, 14, Fields!validto.Value) and IsNothing(Fields!activitydate.Value), "yellow",
  Fields!validto.Value >= DateAdd(DAY, 14, Fields!validto.Value) and IsNothing(Fields!activitydate.Value), "blue",
  true, "white")

It's not clear to me what the difference is between "more than 15 days" and "in the future" so I left that out.

That last condition using true/white is the "else" part that catches all unhandled situations.

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
MSSC_supportAuthor Commented:
Apologies  as our requirements changed after posting this question. I understand it now however I have a slight issue in that due to the way the report is configured now and due to other requirements the value of the cell that is returned is either text or either a date depending on the date.

cell_value=
				CASE 
					WHEN cc.activity = av.activity AND cc.activity_value = av.activity_value AND cc.activity IN ('EDEXPG', 'EDTGGD') THEN cc.activity_value 
					WHEN cc.activity = av.activity AND cc.activity_value = av.activity_value AND cc.activity NOT IN ('EDEXPG', 'EDTGGD') THEN CONVERT(VARCHAR(10),cc.activity_date,126)
					ELSE NULL

Open in new window


The expression you have suggested above gives me the error:

Warning      1      [rsRuntimeErrorInExpression] The BackgroundColor expression for the text box ‘cell_value’ contains an error: Argument 'Date1' cannot be converted to type 'Date'.      

How do I get around this in the expression for the dates?
ValentinoVBI ConsultantCommented:
SSRS has an IsDate() function which returns true if the argument is a valid date, false otherwise.  So use that to prevent conversion to date when the value is not a date.
MSSC_supportAuthor Commented:
Thank you. I think I have got it. Trouble is that my query is causing me other issues so I have had to raise another question to resolve that!

Thanks again.
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
SSRS

From novice to tech pro — start learning today.