Formatting on days - 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)

Formatting on days

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-02-26 : 11:44:27

Hi

I'm trying to colour the rows with the following criteria but I get an error...have I missed something somewhere tried adding a )?
can someone advise please?
Thanks

=iif((Fields!Agreed_Solved_Date___Time.Value <= DateAdd("s",-1,Fields!Agreed_Solved_Date___Time.Value), "Blue",(iif

(Fields!Agreed_Solved_Date___Time.Value = DateAdd("d" ,-1, Fields!Agreed_Solved_Date___Time.Value),"Red", iif(Fields!

Agreed_Solved_Date___Time.Value <= DateAdd("d",-2, Fields!Agreed_Solved_Date___Time.Value),"Green", ""))))

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-26 : 22:55:37

try like

=iif(Fields!Agreed_Solved_Date___Time.Value <= DateAdd("s",-1,Fields!Agreed_Solved_Date___Time.Value), "Blue",iif

(Fields!Agreed_Solved_Date___Time.Value = DateAdd("d" ,-1, Fields!Agreed_Solved_Date___Time.Value),"Red", iif(Fields!

Agreed_Solved_Date___Time.Value <= DateAdd("d",-2, Fields!Agreed_Solved_Date___Time.Value),"Green", "")))

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

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-02-27 : 05:22:10

Thanks that shows no erros, have I got the correct...I want to use the Fields!Agreed_Solved_Date___Time.Value field to highlight in colours when the date is approaching, so if the Fields!Agreed_Solved_Date___Time.Value date = today then Red, if Fields!Agreed_Solved_Date___Time.Value < 1 day Orange, Fields!Agreed_Solved_Date___Time.Value < 2 or more days Green,
Fields!Agreed_Solved_Date___Time.Value < currentdate Blue

Can you advise?
Thanks

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-27 : 05:37:24

Nope your current expression is comparing between same field value which is wrong. i think you need to compare it current date returned by Now() function like

=iif(Fields!Agreed_Solved_Date___Time.Value <= DateAdd("s",-1,Now()), "Blue",iif

(Fields!Agreed_Solved_Date___Time.Value = DateValue(Now()),"Red", iif(Fields!

Agreed_Solved_Date___Time.Value <= DateAdd("d",-2, Now()),"Green", "")))

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

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-02-27 : 06:07:12

Agh I see that now...

You formula shows more blue than I would have thought...what I really could do with is this? Are you able to help with this?
Thanks again

so, if Agreed_Solved_Date_Time = today "Red" --going out of date today

else if Agreed_Solved_Date_Time > Today +1 "Orange"--going out of date tomorrow

else if Agreed_Solved_Date_Time > Today +2 "Green"---going out of date in 2 days or more

else if Agreed_Solved_Date_Time < Today "Blue"--out of date

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

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-02-27 : 06:36:22

I've had a stab at this but getting false part error?
What I need is:

Red = going out of date today
Orange = going out of date tomorrow
Green = going out of date in 2 days or more
Blue = out of date

=iif(Fields!Agreed_Solved_Date___Time.Value = Today()), "Red",iif

(Fields!Agreed_Solved_Date___Time.Value = DateAdd("d",+1,Today()),"Orange" iif(Fields!

Agreed_Solved_Date___Time.Value >= DateAdd("d",+2, Today()),"Green", iif(Fields!

Agreed_Solved_Date___Time.Value < DateAdd("d",0, Today()),"Blue", "")))

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-27 : 23:17:00

better option would be to use switch in this case

Switch(Fields!Agreed_Solved_Date___Time.Value = Today(),"Red",
Fields!Agreed_Solved_Date___Time.Value = DateAdd("d",+1,Today()),"Orange",
Fields!Agreed_Solved_Date___Time.Value >= DateAdd("d",+2, Today()),"Green",
Fields!Agreed_Solved_Date___Time.Value < DateAdd("d",0, Today()),"Blue",
Len(Fields!Agreed_Solved_Date___Time.Value) = 0,"")

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

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-03-04 : 05:49:15

mmm interesting everything returns blue, and today and tomorrow is black not red and orange?
Thanks

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-04 : 06:01:10

then its problem with the date values your fields are having

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

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-03-04 : 06:25:11

ok mate thanks I will look further, thanks for the code :)

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

no problem

you're wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources