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)
 Formatting on days
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

Posted - 02/26/2013 :  11:44:27  Show Profile  Reply with Quote
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...!

Edited by - sz1 on 02/26/2013 12:00:21

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/26/2013 :  22:55:37  Show Profile  Reply with Quote
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/

Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/27/2013 :  05:37:24  Show Profile  Reply with Quote
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/

Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

Posted - 02/27/2013 :  06:07:12  Show Profile  Reply with Quote
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...!
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/27/2013 :  23:17:00  Show Profile  Reply with Quote
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/

Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/04/2013 :  06:01:10  Show Profile  Reply with Quote
then its problem with the date values your fields are having

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

Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
355 Posts

Posted - 03/04/2013 :  06:25:11  Show Profile  Reply with Quote
ok mate thanks I will look further, thanks for the code :)

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 - 03/04/2013 :  23:44:56  Show Profile  Reply with Quote
no problem

you're wc

------------------------------------------------------------------------------------------------------
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.07 seconds. Powered By: Snitz Forums 2000