0

I have been trying variations on this for longer than I care to admit

This formula calculates working days (taken from elsewhere on this site and altered for my fieldnames)

(DateDiff(DateInterval.day,ReportItems!ME06Test.Value,Fields!DATE_VALUE.Value)+1) 
- 
(DateDiff(DateInterval.WeekOfYear,ReportItems!ME06Test.Value,Fields!DATE_VALUE.Value)*2) 
- 
IIF(Weekday(ReportItems!ME06Test.Value,1) = 1,1,0)
- 
IIF(Weekday(ReportItems!ME06Test.Value,1) = 7,1,0)
- 
IIF(Weekday(Fields!DATE_VALUE.Value,1) = 1,1,0)
- 
IIF(Weekday(Fields!DATE_VALUE.Value,1) = 7,1,0)

And it works great unless the field in the matrix is empty:

This:

= IIF( (IsDate(ReportItems!ME06Test.Value)) and (IsDate(Fields!DATE_VALUE.Value)), "YES" , "" )

populates the matrix correctly with blanks when there is a missing date and a "YES" when both date are there.

Now my issue is that if I replace the "YES" with the formula to calculate working days above instead of the "YES" I get the formula result (excellent) but instead of the "" I get #error

Please can someone put me out of my misery and tell me why!!!!?????

jarlh
  • 42,561
  • 8
  • 45
  • 63
Mark
  • 49
  • 11
  • 1
    The answer is most likely a combination of https://stackoverflow.com/questions/21711252/ssrs-expression-giving-error-with-iif-condition and https://stackoverflow.com/questions/7078586/ssrs-expression-throws-error-possible-reasons – lc. May 17 '18 at 08:35
  • Thank you. Having read those links I found the latter helped more as it mentioned that SSRS evaluates the entire expression and does not stop at the matching condition. This means that because the formula will run even on missing (null) dates I get the #error. Why my organisation decided purchasing this piece of junk in 2017 I have no idea but hey..... what can you do :( – Mark May 17 '18 at 14:56

0 Answers0