Colour For datetime field - SQL Server Forums

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

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Colour For datetime field

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-04-23 : 03:29:56

Hi I'm trying to add some colours to a datetime field
if its today = Red
If its less than today = Blue
If its greater than today = Green

I'm getting an end of statement error with this?
I'm adding this to the Font - Colour expression.

=Switch(Fields!Agreed_Solved_Date___Time.Value = Today()),"Red",
Fields!Agreed_Solved_Date___Time.Value < Today()),"Blue",
(Fields!Agreed_Solved_Date___Time.Value > Today()),"Green")

SZ1
Learning and development is the driving force in the universe...!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-23 : 08:33:01

Looks like you have a mismatch on the brackets. See if this will work:

=Switch(Fields!Agreed_Solved_Date___Time.Value = Today(),"Red",
	Fields!Agreed_Solved_Date___Time.Value < Today(),"Blue",
	Fields!Agreed_Solved_Date___Time.Value > Today(),"Green")

If Agreed_Solved_Date___Time has time portion , you would need to remove the time portion before comparing against "Today()"

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-04-23 : 09:51:05

This could work if I can include and AND clause for the Within_Flag for breached tickets. The Agreed_Solved_Date_Time does have time too?? how do I remove the time portion the RED = Today is returning Green not RED.

Will this work?

=Switch(Fields!Agreed_Solved_Date___Time.Value = Today()),"Red",
Fields!Agreed_Solved_Date___Time.Value < Today()), And(Fields!Solved_Within_SLA_Flag.Value ="0")"Blue",
(Fields!Agreed_Solved_Date___Time.Value > Today()),"Green")

Thanks

SZ1
Learning and development is the driving force in the universe...!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-23 : 11:00:06

YOu can use the following to remove the time portion (i.e., replace Fields!Agreed_Solved_Date___Time.Value with the fragment below):

dateadd("d",datediff("d",0,Fields!Agreed_Solved_Date___Time.Value),0)

Didn't quite follow what you meant by Within_Flag. You can add another condition to the the tests - for example

dateadd("d",datediff("d",0,Fields!Agreed_Solved_Date___Time.Value),0) = Today() 
AND Fields!Within_Flag.Value = "1" ,"Red"

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-04-24 : 05:53:44

Yea I get only black with this even though the Agreed_Solved_Date_Time is today for some records and where the Within SLA is 1, would expect to see red colour for records today and a 1 for the Within SAL flag.

=IIF(dateadd("d",datediff("d",0,Fields!Agreed_Solved_Date___Time.Value),0) = Today()
AND Fields!Solved_Within_SLA_Flag.Value = "1" ,"Red","Black")

SZ1
Learning and development is the driving force in the universe...!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-24 : 08:31:11

Are you applying the logic to the correct field/property? If you replaced the "Black" also with "Red", do you see Red always?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-24 : 08:47:10

Check if format of date returned by Today is same as what the date expression interprets

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-04-24 : 09:22:02

Yes Text Box Properties - Font - Color Expression (fx), Even if I change both to red its still black so must be a format thing?

When I do a return on =Today() I get the format 4/24/2013 12:00:00 AM
On the field Agreed_Ddate_Time return I get 24/04/2013 12:00:00.

This is beacuse I changed the language in the field to en-GB, so do I need to make the whole report en-GB so that all the formulas return this date format? if so how do I do this? I already changed the =Today() to en-GB and its now the same as the field but how do I tell the expression to do thte same

Thanks

SZ1
Learning and development is the driving force in the universe...!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-24 : 11:56:36

Is Agreed_Ddate_Time of type datetime as returned by the database? If it is character format, that could be the problem.

Test it like this:
If you change the color to red (without using an expression), does it change to red?
If it does, make the expression simply ="Red" and see if that works.
If that works, change the expression to =IIF(Today()=Today(),"Red","Black") and see if that works
If all of the above works, then the format is the problem. You should get it as a datetime column from the database.

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-04-24 : 12:09:23

Yea its defo datetime format from the db although the format is different, its still datetime though. You mentioned if=Today but it needs to refer to the Agreed_Date_Time field to see if it matches...

SZ1
Learning and development is the driving force in the universe...!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-24 : 12:30:12

quote:
Originally posted by sz1

Yea its defo datetime format from the db although the format is different, its still datetime though. You mentioned if=Today but it needs to refer to the Agreed_Date_Time field to see if it matches...

SZ1
Learning and development is the driving force in the universe...!

use Format and specifically change the format of date before you do the comparison with Today() to make them same

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-04-30 : 04:50:38

so =format(...
ok thanks

SZ1
Learning and development is the driving force in the universe...!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-30 : 04:54:47

yep

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources