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
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Colour For datetime field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

Posted - 04/23/2013 :  03:29:56  Show Profile  Reply with Quote
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...!

Edited by - sz1 on 04/23/2013 03:31:54

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 04/23/2013 :  08:33:01  Show Profile  Reply with Quote
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()"
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

Posted - 04/23/2013 :  09:51:05  Show Profile  Reply with Quote
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...!

Edited by - sz1 on 04/23/2013 09:55:53
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 04/23/2013 :  11:00:06  Show Profile  Reply with Quote
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"
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

Posted - 04/24/2013 :  05:53:44  Show Profile  Reply with Quote
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...!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 04/24/2013 :  08:31:11  Show Profile  Reply with Quote
Are you applying the logic to the correct field/property? If you replaced the "Black" also with "Red", do you see Red always?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/24/2013 :  08:47:10  Show Profile  Reply with Quote
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
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

Posted - 04/24/2013 :  09:22:02  Show Profile  Reply with Quote
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...!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 04/24/2013 :  11:56:36  Show Profile  Reply with Quote
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.
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

Posted - 04/24/2013 :  12:09:23  Show Profile  Reply with Quote
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...!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/24/2013 :  12:30:12  Show Profile  Reply with Quote
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
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

Posted - 04/30/2013 :  04:50:38  Show Profile  Reply with Quote
so =format(...
ok thanks

SZ1
Learning and development is the driving force in the universe...!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/30/2013 :  04:54:47  Show Profile  Reply with Quote
yep

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.08 seconds. Powered By: Snitz Forums 2000