Question
How to calculate DateTime values in report using operator's timezone?
I have a report where I must select cases that were resolved in a specific timeframe
I'm using functions like Day of the week of and Difference in days between to compare dates in the required logic, but I see it doesn't take the operator's timezone (MST), this results in incorrect report results where some cases that fall in one day in MST but are actually in the next one in GMT are not being selected.
This report is being used in the My Reports tab and thus, can't implement a response data transform in a data page, and there doesn't seem to be a way to transform the dates using activities or java functions either.
Can you please give us the screenshot of the custom function that you created. We have a similar issue.
Hi,
Thank you for posting your query in the PSC. This looks like an inactive post and hence, we suggest you create a new post for your query. Click on the Write Post button here. Once created, please reply here with the URL of the new post.
You may also refer this discussion link as a reference in the new thread.
An example of the use of this in a function alias rule would be:
CASE WHEN {1} IS NOT NULL THEN CAST(TIMEZONE('<pega:reference format="pxDisplayText" name="OperatorID.pyDefaultTimeZone" />', TIMEZONE('GMT', {1})) AS varchar(32)) ELSE '' END
pxConvertDateTimeToText is an example rule you can do a save as from to build your own function. You can then use it in your reports - you can set the return type to either text or datetime for example.
I'll answer my own post for future reference.
Since both function aliases are final, I created custom ones using the OOTB as a base, then wrapped the DateTime params as described below:
Edit: Please note this SQL function is for Postgresql only, and your app might use a different DB system.
OOTB:
Custom:
What this does is simply set GMT as base timezone to the datetime value from the param, then convert it to the operator's defined timezone, I had to do the first part because we use Postgresql and this system stores datetime values with no defined timestamp. I hope this helps anyone until Pega supports timezones in their function aliases for Report Definitions.