Change background color if Date is within 30 days

[tipofapin]

Using SSRS 2005 with SQL 2005. I am working on a report and I want to change the background color of a date field if that date is not null and is within 30 days of the current date. I have used every variation that I can think of in the background properties expression, and I keep getting error messages.

This is the code that I have right now (which doesn't work of course.)

=Switch (Fields!CrisisPlanningDueDate.Value <>"" and Fields!CrisisPlanningDueDate.Value < ((Today)+30),"LightCoral")

Can someone help me out?

 

[SgtJarrow]

Below is NOT tested...but I think it is close if not correct.

I see two problems with your statement. First, SSRS code is VB-based and switch is not used in the manner you are trying to use it. In this instance, with only two options, I would recommend an in-line if statement. Second, you say you want to change the color if the date is "within 30 days of the current date". Does that imply you want to change the color if the report is due in the next 30 days? You then need to check for the date to be between today and today+30. But what about those reports that have dates before today?

Finally, in this code, you need to specify the color the report should be when the condition is true (light coral) and when the condition is false (white).

If these statements are correct, try this:

=IIf(DueDate.ToString.Length > 0 And DueDate > DateTime.Today And DueDate < DateTime.Today.AddDays(30), "LightCoral", "White")

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[tipofapin]

OK.. This is the code that I entered per your help.

=IIf(Fields!DueDate.Value.ToString.Length > 0 And Fields!DueDate.Value > DateTime.Today And Fields!DueDate.Value< DateTime.Today.AddDays(30), "LightCoral", "White")

It does what it is supposed to.. It highlights the background of this field correctly.

But I get this warning message:

[rsRuntimeErrorInExpression] The BackgroundColor expression for the textbox ‘DueDate’ contains an error: Object reference not set to an instance of an object.

So even though it performs the correct task, I really don't want a warning message to be popping up. Any other ideas?

 

[SgtJarrow]

Due you have any DueDates that are truly NULL or empty????

If so, then the error is coming from that. An IIF statement always evaluates each part, even if the first one is false...So if you have a NULL or empty date, the checking of the date on those records are the problem...

In a case like this, I would modify my DataSet field that contains the DueDate to return me an arbitrary number if the date is NULL or empty...

Your SQL might look like this:

SELECT
    DueDate
FROM SomeTable

I would change it to:

SELECT
    COALESCE(DueDate, '1/1/1800') 'DueDate'
FROM SomeTable

The I would change the IIF to be:

=IIf(Fields!DueDate.Value <> #1/1/1800# And Fields!DueDate.Value > DateTime.Today And Fields!DueDate.Value< DateTime.Today.AddDays(30), "LightCoral", "White")

Finally, I would add some code to the visible property to hide the '1/1/1800' values...

=(DueDate <> #1/1/1800#)

Or something similar to these instructions

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer