Set background color based on datetime values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Set background color based on datetime values

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-09-14 : 10:49:07

Hello all,
I have a cell where I change the background color with this expression:

=IIF(Fields!DataEnd.Value Is Nothing, "Transparent", "Yellow")

but I have also to check that if the date is present, the row has to be yellow also if is < today.

How can I change this expression?

Thanks a lot.

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 11:00:42

sorry from what i understand current condition was that it will still show it as yellow if you've value for DateEnd
so for today's data also you will get row as yellow

isnt that what you're asking for?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-09-14 : 14:31:03

For example, the query that populate my report's dataset gives me 2 datetime field like these:

PlantID - DateStartProduction - DateStopProduction

1 - 19960101 - NULL
2 - 19980201 - NULL
3 - 20000101 - 20101231
4 - 20040101 - 20991231
5 - 20100101 - NULL
....

and in my report I should show rows with yellow backgroud for the plants that are no more active.

In the list above, only the 3rd plant is no more active, becaus his DateStop is passed (20101231).
If a plant has a date in the future (for example, Plant=4 it will stop on 2099)
or has NULL, it means that is active at the moment.

The date to compare is the Getdate() of the report visualization.

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 16:48:43

just use like

=IIF((Cdate(Now()) >= Fields!DateStartProduction.Value And Cdate(Now()) <=Fields!DateStopProduction.Value) Or (Cdate(Now()) >= Fields!DateStartProduction.Value And Fields!DateStopProduction.Value Is Nothing), "Transparent", "Yellow")

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-09-17 : 03:34:16

Perfect, it works correctly, thank you very much Visakh.
Just a question: why you use the Cdate function for the Now()?
I cannot simply use Now() <= ....?

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 11:29:44

just to make sure its converted to date and not regarded as a string value as i need to do date comparison here using >=,<= and not string (alphabetic) based comparison

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-09-17 : 14:51:57

Ah ok, now is more clear.
Thank you very much Visakh.

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 14:57:54

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources