SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 Set background color based on datetime values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 09/14/2012 :  10:49:07  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 09/14/2012 :  11:00:42  Show Profile  Reply with Quote
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/


Edited by - visakh16 on 09/14/2012 11:02:05
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 09/14/2012 :  14:31:03  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/14/2012 :  16:48:43  Show Profile  Reply with Quote
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/

Go to Top of Page

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 09/17/2012 :  03:34:16  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/17/2012 :  11:29:44  Show Profile  Reply with Quote
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/

Go to Top of Page

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 09/17/2012 :  14:51:57  Show Profile  Reply with Quote
Ah ok, now is more clear.
Thank you very much Visakh.

Luigi
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/17/2012 :  14:57:54  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000