Posted: 29 Jun 2018 10:22 EDT Last activity: 6 Jul 2018 13:29 EDT
How to list items in Report Definition with deadline after two days after
We have a scenario where we need to create a report definition scheduled daily. The report will collect all items with Deadline Time = Current Day+2. To implement this we have tried using function pxDifferenceInHours() as well as pxDifferenceInDays(). However looks like since these functions doesn't consider business calendar, the report will not work properly for Friday.
The below we tried in filter:
The difference in hours between ( The current system date and time as a DateTime value ) and A.pxDeadlineTime >= 48
The difference in hours between ( The current system date and time as a DateTime value ) and A.pxDeadlineTime <= 72
I know of no OOTB way of doing this. Weekend days can be determined programmatically, but holidays cannot, if for no other reason than that holidays vary from locale to locale. The only way I know of is to maintain a table of holiday dates in the database, and reference that in an SQL function so those dates can be excluded from your report query.
I believe that applications and reports that depend on knowing business days (e.g., banking or finance) often subscribe to services that will return the requested business date.