Date Format is showing incorrect format in Excel file on Pega's "Export to Excel"
I've a report definition where one of the columns is a text column containing "Date" content in "dd-Mon-yyyy" format. Business reported the issue saying the column data is not properly sorted as it is sorting based on the text but not on the latest date values. This is expected.
To fix this, we've referred a function alias rule which will convert text to date format and is showing appropriately on viewing the report in the application. However, it is showing in "dd/mm/yyyy" format in the downloaded excel on click of Pega's "Export to Excel".
I am using Pega 7.2.2. Can somebody help me how to fix this?
I see that you are suggesting our own way of downloading the excel file. I'm launching the report via the action "Open URL in Window" on click of a link passing my report definition. Hence, I don't have control to configure my own excel file. PFA doc for the design screenshots.
How would you suggest me to leverage your solution?
Note: The user is also editing the report via "Edit Report" button. I don't want to lose that with this solution.
I reviewed your design, it isn't much difference how you're going to configure activity.
You can follow step 2 and 3 from my configuration additionally with the new date time format created as mentioned.
Please let me know if you have query further. Also, I ain't sure whether you have shared have a configuration as I wasn't able to identify "How you are passing report content to export to excel activity.
PFA doc "Launching the Report Definition on click of a link.docx" for the design screenshots. I'm displaying the report definition via "Call doUIAction" in my activity which will display the report browser options such as "edit report, actions dropdown containing 'refresh', 'summarize', 'export to PDF' and 'export to Excel' ". I'm assuming we don't have control over background logic on these options as these are Pega's Report Browser's OOTB.
Wondering, even if I write an activity with steps 2 and 3 that you mentioned, where to invoke this activity?
Looking forward for your inputs. Thanks in advance.
1. Create a xlsx binary template and upload the file which has field values in it.
2. Run the report definition and have the results in the page list.
3. Loop through the page list and convert the date values to the format you are needed using FormatDateTime function and save it in each results in a text property. Refer this in the field values of the template.
4. Call the MSOGenerateExcelFile activity on the page list having results.
Note: I have tried function alias in the report definition, and some other things like, ignore export option etc. It is not at all working. I have worked on this and is working fine, it will work for CSV, Excel and PDF file formats.