Question
BlueRose Technologies
AU
Last activity: 11 Jan 2019 2:09 EST
Date Format is showing incorrect format in Excel file on Pega's "Export to Excel"
Hi All,
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?
Thanks in advance.
Regards,
Giridhar Metikal
EPAM Systems, Inc.
ES
Hi Giridhar,
Thanks for posting on PSC.
You were close to implementation, just create an "Edit Input" rule to format the DateTime as per your requirement.
Navigate, Record Explorer -> Data Model -> Edit Input -> Landing Page (Select DateTime format).
Customise it as per your requirement.
Now, write an activity to be launched from a button/link(as you want) as below:-
Step1,
Configure pyReportName, pyReportClass and StepPage. Set Param.pyPageName same as step page.
Step2,
Method Name:- Call Rule-Obj-report-Definition.pxRetrieveReportData
Check Pass current parameter Page.
Hi Giridhar,
Thanks for posting on PSC.
You were close to implementation, just create an "Edit Input" rule to format the DateTime as per your requirement.
Navigate, Record Explorer -> Data Model -> Edit Input -> Landing Page (Select DateTime format).
Customise it as per your requirement.
Now, write an activity to be launched from a button/link(as you want) as below:-
Step1,
Configure pyReportName, pyReportClass and StepPage. Set Param.pyPageName same as step page.
Step2,
Method Name:- Call Rule-Obj-report-Definition.pxRetrieveReportData
Check Pass current parameter Page.
Step3, Configure OOTB pxConverResultsToCSV activity.
Method Name : Call pxConvertResultsToCSV, Pass your step page as in step 1.
Call your own CSVPropertyTypes.
Hope it helps in implementation.
Kindly notify, if it does.
Regards,
Asif
BlueRose Technologies
AU
Hi AsifHasan,
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.
EPAM Systems, Inc.
ES
Hey Gridhar,
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.
Regards,
Asif
BlueRose Technologies
AU
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.
Regards,
Giridhar Metikal
EPAM Systems, Inc.
ES
I am not sure then how to implement it for OOTB "Export to Excel" option. I will try to research more, if I get anything to modify the date format for OOTB export to excel functionality.
Pegasystems Inc.
IN
Hi Giridhar,
Can you please check if giving control format on the report definition suits for your scenario:
Thanks!
BlueRose Technologies
AU
Hi Gudam,
I tried both the approaches but it isn't working for me. Posted my comment in the same Post.
Let me know if you have any alternative approaches. Thanks in advance.
Regards,
Giridhar Metikal
Tech Mahindra
IN
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.