locked
SSRS IF statement when calculating workdays between two dates RRS feed

  • Question

  • Good day

    I am having trouble with an If statement:
    =IIf(IsNothing(Fields!Procurement_Final_Approval_Date.Value)
    ,

    If Fields!Procurement_Final_Approval_Date is Blank - (it displays Error in the report field):
    (DateDiff(DateInterval.day, Fields!Created.Value, Today())+1)
     - (DateDiff(DateInterval.WeekOfYear,Fields!Created.Value,Today())*2)
     - IIF(Weekday(Fields!Created.Value,1) = 1,1,0)
     - IIF(Weekday(Fields!Created.Value,1) = 7,1,0)
     - IIF(Weekday(Today(),1) = 1,1,0)
     - IIF(Weekday(Today(),1) = 7,1,0)
    ,
    Else - (This section is working and displays a value)
    (DateDiff(DateInterval.day, Fields!Created.Value, Fields!Procurement_Final_Approval_Date.Value)+1)
     - (DateDiff(DateInterval.WeekOfYear,Fields!Created.Value,Fields!Procurement_Final_Approval_Date.Value)*2)
     - IIF(Weekday(Fields!Created.Value,1) = 1,1,0)
     - IIF(Weekday(Fields!Created.Value,1) = 7,1,0)
     - IIF(Weekday(Fields!Procurement_Final_Approval_Date.Value,1) = 1,1,0)
     - IIF(Weekday(Fields!Procurement_Final_Approval_Date.Value,1) = 7,1,0)
    )

    Full expression:

    =IIf(IsNothing(Fields!Procurement_Final_Approval_Date.Value)
    , 
    (DateDiff(DateInterval.day, Fields!Created.Value, Today())+1)
     - (DateDiff(DateInterval.WeekOfYear,Fields!Created.Value,Today())*2)
     - IIF(Weekday(Fields!Created.Value,1) = 1,1,0)
     - IIF(Weekday(Fields!Created.Value,1) = 7,1,0)
     - IIF(Weekday(Today(),1) = 1,1,0) 
     - IIF(Weekday(Today(),1) = 7,1,0)
    ,
    (DateDiff(DateInterval.day, Fields!Created.Value, Fields!Procurement_Final_Approval_Date.Value)+1)
     - (DateDiff(DateInterval.WeekOfYear,Fields!Created.Value,Fields!Procurement_Final_Approval_Date.Value)*2)
     - IIF(Weekday(Fields!Created.Value,1) = 1,1,0)
     - IIF(Weekday(Fields!Created.Value,1) = 7,1,0)
     - IIF(Weekday(Fields!Procurement_Final_Approval_Date.Value,1) = 1,1,0)
     - IIF(Weekday(Fields!Procurement_Final_Approval_Date.Value,1) = 7,1,0)
    )
    Thanks in advance for your assistance.
    Regards

    Tuesday, August 23, 2016 8:58 AM

Answers

  • Hi ,

    I am suspecting one of your values is null . Have a look at the next link, similar case to yours :

    https://www.experts-exchange.com/questions/28665695/SSRS-DateDiff-Expression-Getting-Error-if-one-of-the-dates-is-NULL-even-with-IIF-trying-to-catch-NULL-dates.html


    Regards, David .

    • Marked as answer by Lylecwk1 Wednesday, August 31, 2016 6:43 AM
    Tuesday, August 23, 2016 10:06 AM
  • Hi Lylecwk1,

    In Reporting Services, it will evaluate each part of the function before the report is executed. So, in your scenario, as for the second part in IIF expression, if Fields!Procurement_Final_Approval_Date is blank, when SSRS evaluate the Weekday(Fields!Procurement_Final_Approval_Date.Value,1) will fail and show #Error in filed cell.

    I have searched a valid solution to calculate workdays between two dates by using custom code. I have tested this solution in my local environment, it works fine. Please refer to calculate business day between two dates.

    Below is the result in my test:

    If you have any question, please feel free to ask.

    Best regards,
    Yuliana Gu

    • Marked as answer by Lylecwk1 Wednesday, August 31, 2016 6:43 AM
    Wednesday, August 24, 2016 11:03 AM

All replies

  • Hi ,

    I am suspecting one of your values is null . Have a look at the next link, similar case to yours :

    https://www.experts-exchange.com/questions/28665695/SSRS-DateDiff-Expression-Getting-Error-if-one-of-the-dates-is-NULL-even-with-IIF-trying-to-catch-NULL-dates.html


    Regards, David .

    • Marked as answer by Lylecwk1 Wednesday, August 31, 2016 6:43 AM
    Tuesday, August 23, 2016 10:06 AM
  • Hi Lylecwk1,

    In Reporting Services, it will evaluate each part of the function before the report is executed. So, in your scenario, as for the second part in IIF expression, if Fields!Procurement_Final_Approval_Date is blank, when SSRS evaluate the Weekday(Fields!Procurement_Final_Approval_Date.Value,1) will fail and show #Error in filed cell.

    I have searched a valid solution to calculate workdays between two dates by using custom code. I have tested this solution in my local environment, it works fine. Please refer to calculate business day between two dates.

    Below is the result in my test:

    If you have any question, please feel free to ask.

    Best regards,
    Yuliana Gu

    • Marked as answer by Lylecwk1 Wednesday, August 31, 2016 6:43 AM
    Wednesday, August 24, 2016 11:03 AM
  • Thanks for the reply’s.

    Before i saw your suggestions i slit the IF statements into two calculated fields so that it first calculates the date difference then created the If statement using the two calculated fields.<o:p></o:p>

    Regards 


    • Edited by Lylecwk1 Wednesday, August 31, 2016 6:53 AM
    Wednesday, August 31, 2016 6:53 AM