1 of 1 people found this helpful
you cannot subtract dates directly; instead of defining calculation field for Current Date and another for Handling Time, define calculated field Handling Time with:
Formula Type Pre-Defined
Functions Date Difference - In Seconds, ARJDBC
Resulting Field Type Metric
Start Date Submit Date
End Date Current Date + 0 Days
Result will be in seconds, but you can use column formatting to make it humanly readable. For example:
Format Hours and Minutes (these are units used in output)
Unit Selection Seconds (these are units used in calculated field)
It seems I was a bit hasty and assumed Current Date substitutes $TIMESTAMP$, which it doesn't; when used in filter it is possible to define value as Current Date - 0 Seconds, but in Date Difference, accuracy of Current Date is limited to days at best. In your example (Current Date + 0 Days) translates into '2019-05-14 00:00' thus date difference is negative and equal to time part of Submit Date, or invalid if Start Date and End Date aren't swapped; if you were to use Current Date + 1 instead, difference would always be positive, but it would still be offset by the number of seconds left between now and the end of current day. This could be handled by datediff if it were possible to select calculated flelds as either Start Date or End Date.
Could you replace DATEDIFF('ss', 'HPD:Help Desk'.'Submit Date', '2019-05-14') with DATEDIFF('ss', 'HPD:Help Desk'.'Submit Date', CurrentDate()) manually?
P. S. You could get desired results even with this report in special circumstances -- reports run at exactly midnight will give correct results since there will be no offset.
Thank you Sinisa, not quite sure who things can be replaced manually...
If one of options offered in Formula Type of calculated field's definition is "Freehand SQL", select it and simply paste in replacement expression; if that option is not available, and it seems that it often isn't, Mohammad Rehman's answer to https://communities.bmc.com/message/754648#754648 might help.
Alternatively, it might be possible to alter .jar which defines available units of accuracy of Current Date in Date Difference and add missing ones (hours, minutes, seconds).
To summarize, in order to achieve your goal, you should create calculated field using:
Formula Type Freehand SQL
The Freehand SQL builder DATEDIFF('ss', `HPD:Help Desk`.`Submit Date`, CurrentDate())
There are two possible issues preventing you from doing that:
- Freehand SQL is not shown in Formula Type menu -- siadmin has to enable Allow Freehand SQL Calculated Fields in Administration => Admin Console => Roles => Admin => Report Builder
- Calculated field can not be validated/saved, "Ambiguous column name 'DATEDIFF'" warning is shown instead -- click on pencil icon in lower left corner (Edit View), click on table where Start Date is coming from (in your case, that would be HPD:Help Desk), click on its gear icon, mark it as Mandatory (there is a check box just above Columns) and Publish View to save the change.
Once these two issues are resolved, you'll be able to enter DATEDIFF expression and save Calculated Field.