DateTime Format on Scheduled Report in Excel
Hello everyone,
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
Hello everyone,
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.
Accepted Solution
Updated: 24 Aug 2015 7:22 EDT
Infosys
US
The solution suggested in the PDN Article hasn't worked for us.
Instead, we found a very similar control named pzExport_DateTime_RD and saved a copy with a new name into our Ruleset.
To get our desired results we changed the values of the aLocale and aPattern parameters passed to the format() method. Our Line 8 looks like
sDatetime = PRDateFormat.format("de", strTimeZone, PRDateFormat.DEFAULT_DATETIME_LONG, curProp.toDate());
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.
Infosys
US
Hi all,
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:
Hi all,
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.
Pegasystems
US
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)
/Eric
Infosys
US
Thank you Eric.
Are you referring to this?
Unfortunately we are on Pega 6.3 SP1 and the Custom Format option is not available, regardless of the chosen Type option:
Maybe there is a hotfix or something similar to introduce the Custom Format for Pega 6.3 SP1?
Pegasystems
US
Yes that looks like the right snapshot of custom format. I'm not sure you need custom format for your situation. I just know it worked for me on Pega Platform 7. /Eric
Infosys
US
We found a PDN Support Article related to list views:
We'll give that a try next week and I'll report if this also solves the issues we are having with the scheduled reports that are emailed as Excel attachments.
Accepted Solution
Updated: 24 Aug 2015 7:22 EDT
Infosys
US
The solution suggested in the PDN Article hasn't worked for us.
Instead, we found a very similar control named pzExport_DateTime_RD and saved a copy with a new name into our Ruleset.
To get our desired results we changed the values of the aLocale and aPattern parameters passed to the format() method. Our Line 8 looks like
sDatetime = PRDateFormat.format("de", strTimeZone, PRDateFormat.DEFAULT_DATETIME_LONG, curProp.toDate());
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.