SA-34533

Summary

In one of the reports, time is not shown as expected after exporting to Excel.

The user said when they pull up a report, they are getting decimal value in place of time. It should display as HH:MM:SS but instead of this, it is displaying a decimal value.

Error Messages

Not Applicable

Steps to Reproduce

1. Create a report with few fields and a drilldown report.
2. Associate a property which is of type decimal. Associate custom format to the property to display the required format.
3. After drilling down the report, the value is shown in HH:MM:SS.
4. However, after exporting to Excel, it is not displayed in the required format. It is displayed as decimal format.

Root Cause

A software use or operation error.

Normal scenario:

When the report is opened directly, the field values are populating in proper format:

When the data is exported to Excel, because of the format is specified already, the value is converting properly and sent to Excel as it is:

<tr ExpandLevel='Auto!$$TesUser!$$'><td class='excelText'>&nbsp;&nbsp;&nbsp;&nbsp; TestUser</TD>
<TD>3498:58:12</TD>
<TD> 2 </TD>
<TD>1749:29:06</TD>
</TR>

Because of this, the value is shown as it is in Excel.

Drilldown scenario:

Whereas after drilling down, the value "04:04:34" is shown properly in the report:





While exporting to Excel, the value is not displayed(0.169837963) properly:

This is because the logic is different here, which is the ideal logic. The below is the markup sent to Excel when exporting.


<TR>
<TD class='excelText'> EMAIL</TD>
<TD class='excelText'>
<span> Resolved-Completed </span>
</TD>
<TD class='excel-datetime'>9/16/10 4:54 PM</TD>
<TD class='excelText'> Test Services </TD>
<TD class='excelText'> Test</TD>
<TD class='excelText'> Test</TD>
<TD class='excelText'>S-10876</TD>
<TD class='excel-number'>04:04:34</TD>
</TR>


The value is passed properly from Pega. As the property is defined as Decimal, it is converting it to Number format when exporting to Excel. And Excel treats this as General format and displays it in General format.





This is neither an issue with Pega nor with Excel. Pega is passing the data type as it is defined at the decimal level which is as designed and Excel is converting it as per its specifications. The design needs slight alteration as to define the correct datatype.

If there is no significance of having Decimal property for a dateTime field, then its ideal to create DateTime property.

Resolution

Perform the following local-change:

In order to overcome this, user needs to have a new control format at the drilldown property level.

Define a new control format "NewFormat" and associate it only at this drilldown property: Donot associate it with the normal fields which are already working.




The logic for this "NewFormat" would be: