SSRS DateDiff Expression. Getting #Error if one of the dates is NULL, even with IIF trying to catch NULL dates.

Both of these methods give me the number of days between the two dates if both date fields are populated.  If either or both dates are NULL, then I get #Error.  Apparently the whole expression is evaluated even though you'd think the IIF would short circuit it.

How can I make fields where one or more dates are null just print "" (blank) instead of #Error.  The expression works fine.  I just want to suppress the "#Error" when there's a blank date in one of the two date fields of the DateDiff.

=IIF(Fields!ObtainedGADataSheet.Value is nothing OR Fields!ReceivedJobDesc.Value is nothing,"",

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

Open in new window


= IIF (IsDate(Fields!ReceivedJobDesc.Value) AND IsDate(Fields!ReceivedJobDesc.Value), 
  (DateDiff(DateInterval.day,Fields!ObtainedGADataSheet.Value,Fields!ReceivedJobDesc.Value)+1)
- (DateDiff(DateInterval.WeekOfYear,Fields!ObtainedGADataSheet.Value,Fields!ReceivedJobDesc.Value)*2) 
- IIF(Weekday(Fields!ObtainedGADataSheet.Value,1) = 1,1,0)
- IIF(Weekday(Fields!ObtainedGADataSheet.Value,1) = 7,1,0)
- IIF(Weekday(Fields!ReceivedJobDesc.Value,1) = 1,1,0)
- IIF(Weekday(Fields!ReceivedJobDesc.Value,1) = 7,1,0)-1, "")

Open in new window

LVL 1
megninAsked:
Who is Participating?
 
Nico BontenbalCommented:
I think this is because Iif always evaluates the true and the false part of an expression. So this expression:
=iif(true,"??",datediff(dateinterval.WeekOfYear,today(),nothing))

Open in new window

will result in "#Error" where you might expect it to return "??". So you have to make sure there are no nothings returned to the DateDiff function in your expression. So instead of using 'DateDiff(DateInterval.day,Fields!ObtainedGADataSheet.Value,Fields!ReceivedJobDesc.Value)' you should use:
DateDiff(DateInterval.day,Iif(IsNothing(Fields!ObtainedGADataSheet.Value), Today(), Fields!ObtainedGADataSheet.Value),Iif(IsNothing(Fields!ReceivedJobDesc.Value), Today(), Fields!ReceivedJobDesc.Value))

Open in new window

Very annoying, I know. But I don't know any way around this. You could add calculated fields to your dataset probably that do the IsNothing then Today thing on the fields. That would simplify the expression. You could do it on SQL Server, or even write your own custom DateDiff function that handles the Nothing values. But one way or the other, make sure you never pass a Nothing value to the DateDiff function.
0
 
nishant joshiTechnology Development ConsultantCommented:
Hi megnin,

Please try using below code.

=IIF(ISDATE(Fields!ObtainedGADataSheet.Value) AND ISDATE(Fields!ReceivedJobDesc.Value),"",
 CStr((DateDiff(DateInterval.day,Fields!ObtainedGADataSheet.Value,Fields!ReceivedJobDesc.Value)+1)
 - (DateDiff(DateInterval.WeekOfYear,Fields!ObtainedGADataSheet.Value,Fields!ReceivedJobDesc.Value)*2) 
 - IIF(Weekday(Fields!ObtainedGADataSheet.Value,1) = 1,1,0)
 - IIF(Weekday(Fields!ObtainedGADataSheet.Value,1) = 7,1,0) 
 - IIF(Weekday(Fields!ReceivedJobDesc.Value,1) = 1,1,0)
 - ((IIF(Weekday(Fields!ReceivedJobDesc.Value,1) = 7,1,0))-1)
 )
 )

Open in new window


I am not 100% sure if it will work for you or not.if it will not work then check field type in your report xml. field type should be date time.As per my initial check seems to be a conversion issue.
0
 
James0628Commented:
I think Nicobo is correct, and your problem is because the true and false parts are always evaluated.  FWIW, this behavior is described in the "Side Effects" section on the Wiki page:

http://en.wikipedia.org/wiki/IIf#Side_Effects

 The "simple" solution is to replace any reference to those date fields that could give you an error, with an IIF that will give you some default date if a field is null.  It would probably simplify things if you could create something that would replace the null dates in your data, as Nicobo suggested.

 James
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
megninAuthor Commented:
nishant,

Your code returns ONLY the #Errors.  ;-)  Not the difference between the two dates if there IS a date in both fields.  I'll see if I can swap something around, but I'm not that good at the complicated DateDiff scrips.
0
 
megninAuthor Commented:
Nicobo and James, I'm still trying to figure out exactly what pieces I need to replace with the IIF that returns the default date.  I've only used the DateDiff expression a couple of times in the past so I'm still wrapping my head around the syntax...
0
 
megninAuthor Commented:
Nishant,

When I switch the ,"" to the other side of the expression, then it returns "#Error" just like my original version, so no gain.  Thank you, anyway.  

I'm back at working the other method into my expression.  

One of you wouldn't want to help me out with that would you?  ;-)
0
 
megninAuthor Commented:
Okay, this is good.  It produces no errors.  I get "0" where both fields are blank, Today() - Today().
But I get the date in the field - Today() or Today() - the date in the field if only one of the fields are blank.  This throws off the Average() calculation at the end of the 16 columns of dates that are sequentially "DateDiffed."

Would it be possible to adjust it so that if either of the two dates is empty that it returns "0?"

= IIF (IsDate(Iif(IsNothing(Fields!ReceivedJobDesc.Value), Today(), 
  Fields!ReceivedJobDesc.Value)) AND IsDate(Iif(IsNothing(Fields!ReceivedJobDesc.Value), Today(), 
  Fields!ReceivedJobDesc.Value)), 
  (DateDiff(DateInterval.day,Iif(IsNothing(Fields!ObtainedGADataSheet.Value), Today(), 
  Fields!ObtainedGADataSheet.Value),Iif(IsNothing(Fields!ReceivedJobDesc.Value), Today(), 
  Fields!ReceivedJobDesc.Value))+1)
- (DateDiff(DateInterval.WeekOfYear,Iif(IsNothing(Fields!ObtainedGADataSheet.Value), Today(), 
  Fields!ObtainedGADataSheet.Value),Iif(IsNothing(Fields!ReceivedJobDesc.Value), Today(), 
  Fields!ReceivedJobDesc.Value))*2) 
- IIF(Weekday(Iif(IsNothing(Fields!ObtainedGADataSheet.Value), Today(), 
  Fields!ObtainedGADataSheet.Value),1) = 1,1,0)
- IIF(Weekday(Iif(IsNothing(Fields!ObtainedGADataSheet.Value), Today(), 
  Fields!ObtainedGADataSheet.Value),1) = 7,1,0)
- IIF(Weekday(Iif(IsNothing(Fields!ReceivedJobDesc.Value), Today(), 
  Fields!ReceivedJobDesc.Value),1) = 1,1,0)
- IIF(Weekday(Iif(IsNothing(Fields!ReceivedJobDesc.Value), Today(), 
  Fields!ReceivedJobDesc.Value),1) = 7,1,0)-1, "")

Open in new window

0
 
megninAuthor Commented:
Or if this one is easier to work with?  This one uses OR instead of AND.

= IIF(IsNothing(Iif(IsNothing(Fields!ReceivedJobDesc.Value), Today(), 
  Fields!ReceivedJobDesc.Value)) OR IsNothing(Iif(IsNothing(Fields!ReceivedJobDesc.Value), Today(), 
  Fields!ReceivedJobDesc.Value)), 0,
  (DateDiff(DateInterval.day,Iif(IsNothing(Fields!ObtainedGADataSheet.Value), Today(), 
  Fields!ObtainedGADataSheet.Value),Iif(IsNothing(Fields!ReceivedJobDesc.Value), Today(), 
  Fields!ReceivedJobDesc.Value))+1)
- (DateDiff(DateInterval.WeekOfYear,Iif(IsNothing(Fields!ObtainedGADataSheet.Value), Today(), 
  Fields!ObtainedGADataSheet.Value),Iif(IsNothing(Fields!ReceivedJobDesc.Value), Today(), 
  Fields!ReceivedJobDesc.Value))*2) 
- IIF(Weekday(Iif(IsNothing(Fields!ObtainedGADataSheet.Value), Today(), 
  Fields!ObtainedGADataSheet.Value),1) = 1,1,0)
- IIF(Weekday(Iif(IsNothing(Fields!ObtainedGADataSheet.Value), Today(), 
  Fields!ObtainedGADataSheet.Value),1) = 7,1,0)
- IIF(Weekday(Iif(IsNothing(Fields!ReceivedJobDesc.Value), Today(), 
  Fields!ReceivedJobDesc.Value),1) = 1,1,0)
- IIF(Weekday(Iif(IsNothing(Fields!ReceivedJobDesc.Value), Today(), 
  Fields!ReceivedJobDesc.Value),1) = 7,1,0)-1)

Open in new window

0
 
megninAuthor Commented:
Okay, this does it:
=IIF(Fields!ObtainedGADataSheet.Value is nothing OR Fields!ReceivedJobDesc.Value is nothing,0,

(DateDiff(DateInterval.day,Iif(IsNothing(Fields!ObtainedGADataSheet.Value), Today(), 
  Fields!ObtainedGADataSheet.Value),Iif(IsNothing(Fields!ReceivedJobDesc.Value), Today(), 
  Fields!ReceivedJobDesc.Value))+1)
- (DateDiff(DateInterval.WeekOfYear,Iif(IsNothing(Fields!ObtainedGADataSheet.Value), Today(), 
  Fields!ObtainedGADataSheet.Value),Iif(IsNothing(Fields!ReceivedJobDesc.Value), Today(), 
  Fields!ReceivedJobDesc.Value))*2) 
- IIF(Weekday(Fields!ObtainedGADataSheet.Value,1) = 1,1,0)
- IIF(Weekday(Fields!ObtainedGADataSheet.Value,1) = 7,1,0)
- IIF(Weekday(Fields!ReceivedJobDesc.Value,1) = 1,1,0)
- IIF(Weekday(Fields!ReceivedJobDesc.Value,1) = 7,1,0)-1)

Open in new window


If there is a date in both fields, it returns the number of Working Days between them.  If either date field is empty it returns a "0."
0
 
megninAuthor Commented:
Thank you for the code bit and the clear explanation of what was going on.  I was able to use the two together to get the result I needed.  I learned some new stuff in the process.
0
 
James0628Commented:
You're welcome.  I'm glad you were able to get it working.

 James
0
 
megninAuthor Commented:
I added a row above the existing table for some additional calculations.  Above each of these DateDiff columns I need to get the average of the values.
Everything I've tried says, "The Value expression for the text box 'Textbox99' refers to the field 'Textbox4'. Report item expressions can only refer to fields within the current dataset scope...
0
 
James0628Commented:
You should probably start a new question about that (assuming that you haven't already).  Partly because it does seem to be a separate issue, but also just so that more people will see it.  This question is several days old and already closed, so there probably won't be many people that see your last post, and I'm probably not going to be able to help with this new issue, since I've never used SSRS.  If you think it will help, your new question could include a link to this question, for reference.

 FWIW, the new issue seems to be more about the report structure than the IIf functions.  You may want to start (in your new question) by explaining what Textbox99 and Textbox4 are, and where they're located in the report.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.