We help IT Professionals succeed at work.

SSRS: color cell complex query

Hi,

I'd need a sql query to color code a date:
Items = status NOT equal to 'Status01', 'Status02'
For all items which have date less then a week => green
older then a week => orange
older then 2 weeks => red
Items which have Status Status03
and have date
older then 2 weeks orange
older then a month red

Thanks for helping with the approach!

J
Comment
Watch Question

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
I believe that can be done via Data Driven Colored Text  as explained here https://www.mssqltips.com/sqlservertip/2612/data-driven-colored-text-for-reporting-services-reports/ and all needed is to focus on the
=SWITCH(Fields!Pay.Value <= 10, "Red", Fields!Pay.Value >= 20, "Green")

Open in new window

where you need to place your actual date column criteria.

Author

Commented:
Thanks, but that s nowhere near to what my query should look like :-(
Can you help building the query syntax using dates?

Something like
=SWITCH(Fields!date.Value <= today-14, "Red", Fields!date.Value <= today-7, "Green")
Then if Fields.status stayus03 and date <=
Database Analyst
CERTIFIED EXPERT
Commented:
I would say lets try something like below:

=SWITCH(
Fields!date.Value > getdate()-7, "Green",
Fields!date.Value between getdate()-14 and getdate()-7, "Orange",
Fields!date.Value <= getdate()-14, "Red", 
Fields!.status = 'Status03' and Fields!date.Value between getdate()-30 and getdate()-14 , "Orange",
Fields!.status = 'Status03' and Fields!date.Value <= getdate()-30, "Red"
)

Open in new window