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.
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)= 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, "")ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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...
ASKER
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? ;-)
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? ;-)
ASKER
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?"
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, "")ASKER
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)ASKER
Okay, this does it:
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."
=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)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."
ASKER
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.
You're welcome. I'm glad you were able to get it working.
James
James
ASKER
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...
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...
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
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
Please try using below code.
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.