Posted: 16 Jun 2015 13:53 EDT Last activity: 26 Aug 2020 8:17 EDT
DateTime Format on Scheduled Report in Excel
we are facing an issue regarding DateTime Value Formatting in a Report Definition.
Marked Properties are of type DateTime.
And Format Values is configured this way:
When I run this report manually (my OperatorID's default locale is set to en_US), I get the expected result, also when exporting to Excel
We have set up a shedule task to email this report to a list of users on a daily basis. Report data is set to be sent as an Excel Spreadsheet:
The attached file does not contain tthe desired format of the DateTime Properties. No matter how we configure the report, the output always uses following format:
What steps or settings do we have to consider to have the desired format in our email attachment?
Some, maybe useful information:
The LANG environment variable is set to: en_US.UTF-8
Thanks a lot!
***Updated by moderator: Lochan to close post*** This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
being low priority we didn't work on that topic until all of a sudden it escalated and we needed to come up with a solution.
We implemented a workaround, where we created text properties that are written to via declare expressions that format the datetime values to the desired pattern. Now we report on that text properties and everything works as expected.
We all know that nothing lasts longer than a temporary workaround, nevertheless I want to leave this discussion open as we have a similar problem that needs to be solved but priority is very low.
The other problem is as follows. I believe they issue has the same reason.
In a report we want to show the day of .pxCreateDateTime in the first column. The control is configured to be pxDateTime with the parameters set to type Date with the Date Format 1/1/2001:
When this report is exported to Excel, this is what we get:
All Dates, where the day value is lower or equal than twelve, we get Format 1.1.2001 and where the day value is greater than 12 we get the format 1/1/2001.
First of all, we want the formats to be equal, ideally 1.1.2001
What I think is more important than a solution is to get the understanding of how we can configure the system to behave like we want it so we can solve future requirements on our own.
Try this, and see if you get consistent dates. For this example, use a report definition (I haven't tried this with list-view or summary-view, and I apologize if your Pega version is so old that you don't have report definitions):
Click gear icon on property row. Type “Date” and then click “best bets” instead of “all matches”
Choose the only choice offered that says “Date Time pxDateTime”.
Click magnifying glass and pick type “Date”, format “Custom” and type in “MM/dd/yyyy” (yes, you need uppercase M’s and lowercase d’s and y’s)
Using this version of the control as the format value in report definition brought the desired results. Dates are formatted consistent and in the format we want it to be when we have this report as an Excel attachmend sent as scheduled email.