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 fieldif its today = RedIf its less than today = BlueIf its greater than today = GreenI'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")SZ1Learning 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")ThanksSZ1Learning 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")SZ1Learning 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 MVPhttp://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 AMOn 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 sameThanksSZ1Learning 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 worksIf 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...SZ1Learning 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...SZ1Learning 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2013-04-30 : 04:50:38
|
so =format(...ok thanksSZ1Learning 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|