none
help with Report Builder code

    질문

  • I have a piece of code in Rpeort Builder that looks at a date field and if the date is within the next 60 days, the field is red and otherwise it has no colour. I'd like to also add in a bit where if the date is in the past the field is also red. Please help.

    =iif(Fields!First_Aid__EFAW__Exp.Value > Today() and Fields!First_Aid__EFAW__Exp.Value<=Today().AddDays(60),"Red","nothing")

    2012년 6월 12일 화요일 오후 12:17

답변

  • I would think you need to remove the check for >Today() and you will then have the formatting applied for anything less than 60 days from today.  Like so:

    =iif(Fields!First_Aid__EFAW__Exp.Value<=Today().AddDays(60),"Red","nothing")

    • 답변으로 표시됨 Adam Raison 2012년 6월 12일 화요일 오후 2:04
    2012년 6월 12일 화요일 오후 2:00

모든 응답

  • Try:

    =iif((Fields!First_Aid__EFAW__Exp.Value<=DateAdd(DAY,60,Now())) and (Fields!First_Aid__EFAW__Exp.Value>Now()), "Red", "Black")


    2012년 6월 12일 화요일 오후 12:58
  • Your code seems to be giving me this error:

    System.Web.Services.Protocols.SoapException: The BackgroundColor expression for the text box ‘First_Aid_Exp’ contains an error: [BC30455] Argument not specified for parameter 'DateValue' of 'Public Function Day(DateValue As Date) As Integer'.
       at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.SetReportDefinition(String Report, Byte[] Definition, Warning[]& Warnings)
       at Microsoft.ReportingServices.WebServer.ReportingService2010Impl.SetItemDefinition(String ItemPath, Byte[] Definition, Property[] Properties, Warning[]& Warnings)
       at Microsoft.ReportingServices.WebServer.ReportingService2010.SetItemDefinition(String ItemPath, Byte[] Definition, Property[] Properties, Warning[]& Warnings)

    2012년 6월 12일 화요일 오후 1:06
  • =iif((Fields!First_Aid__EFAW__Exp.Value<=DateAdd("d",60,Now())) and (Fields!First_Aid__EFAW__Exp.Value>Now()), "Red", "Black")
    Sorry, this will work.
    2012년 6월 12일 화요일 오후 1:49
  • Unfortunately, that just makes all date fields black no matter if they're in the past or future.
    2012년 6월 12일 화요일 오후 1:56
  • I would think you need to remove the check for >Today() and you will then have the formatting applied for anything less than 60 days from today.  Like so:

    =iif(Fields!First_Aid__EFAW__Exp.Value<=Today().AddDays(60),"Red","nothing")

    • 답변으로 표시됨 Adam Raison 2012년 6월 12일 화요일 오후 2:04
    2012년 6월 12일 화요일 오후 2:00
  • That's fantastic, it works a treat, but is there a way to make empty fields have a Clear background? At the moment all fields are red except those dates more than 60 days in the future.

    Sorry to be a pain!

    2012년 6월 12일 화요일 오후 2:04
  • You could try nesting the iif statements to first check for the empty case, and if false run the second iif, like so:

    =iif(Fields!First_Aid__EFAW__Exp.Value = "", "nothing",
       iif(Fields!First_Aid__EFAW__Exp.Value<=Today().AddDays(60),"Red","nothing")
    )
    2012년 6월 12일 화요일 오후 2:14
  • Unfortunately that made all the fields have a clear background fill, whether they were empty, had a date in, or if the date was in the future or the past.
    2012년 6월 12일 화요일 오후 2:17
  • Ok.  You'll have to play with the parameters, like Null instead of "", and so forth.  Try testing each iif separately and then combine them to get the effect you want.  That approach will work, but the the pieces have to work first before putting them together.
    2012년 6월 12일 화요일 오후 2:19
  • That's awesome, thank so much for yuor help!
    2012년 6월 12일 화요일 오후 2:23
  • I think it's because I was using this value as an expression in the field:

    =IIf(FormatDateTime(Fields!First_Aid__EFAW__Exp.Value,2)=CDate("1/1/0001"),"",FormatDateTime(Fields!First_Aid__EFAW__Exp.Value,2))

    Because instead of returning an empty field when compiling the report, it was returning a result of "1/1/0001" so I reckon that your code is working from the 0/0/0001 date, turning all the empty fields red.
    2012년 6월 12일 화요일 오후 2:39
  • Yes, your statement sounds accurate.  I don't recall what an empty date would look like here, my apologies for that bit of misdirection.

    2012년 6월 12일 화요일 오후 3:32