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.

Correct Answer
January 28, 2019 - 11:29pm

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:

{Param}

Custom:

TIMEZONE('<pega:reference format="pxDisplayText" name="OperatorID.pyDefaultTimeZone" />', TIMEZONE('GMT', {Param}))

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.

Comments

Keep up to date on this post and subscribe to comments

January 28, 2019 - 11:29pm

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:

{Param}

Custom:

TIMEZONE('<pega:reference format="pxDisplayText" name="OperatorID.pyDefaultTimeZone" />', TIMEZONE('GMT', {Param}))

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.

November 28, 2019 - 12:22am
Response to DavidH0692

Can you please give us the screenshot of the custom function that you created. We have a similar issue.

November 28, 2019 - 1:58am
Response to LMovva

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.

Vidyaranjan | Community Moderator | Pegasystems Inc.

December 6, 2019 - 11:52am
Response to DavidH0692

We have a similar issue, Can you post a screenshot.