Date formatting when exporting ("share button") to Excel

#1

Hi All,

How to remove the timepart of date columns when exporting to Excel?

I have a table with these date(time) columns. The time dimension is Year->Quarter->Month->Day and in this case the day is showing in the table.
image

When exporting to Excel through the share button excel is adding 00:00:00 timepart…
image

does anyone have any tips for exporting this as a date?

Strangely enough if I uncheck the ‘sortable dates’ option in the export dialog, the dates are exported correctly. However to “train” the end-users to remember this is not easy so would prefer if there’s a solution that does not confuse the end-users :slight_smile:

#2

Hi Ole,

There was a change in Dundas BI version 8.0.1.1000, due to which the sortable dates export the date column in the following format - YYYY-MM-DD hh:mm:ss. Due to this change you get the entire date time in the exported excel sheet.
In this case, as you have found unchecking the “Sortable Dates” checkbox will do the trick. However, currently we do not have a system property that can use to change the default values of the export dialog. But, Dundas BI has a wide library of the Public functions and APIs that you can use to perform customizations.
You can add the following script to the loading event of the dashboard that has the table where you do not want the “Sortable Table” to be checked by default.

var exportService = dundas.context.getService("ExportService");
var viewService = dundas.context.getService("ViewService")
var getUI = exportService.getCustomConfigurationUI;
exportService.getCustomConfigurationUI = function(providerId){
  $(viewService.currentDialogShown).bind(dundas.controls.ExportDialogConstants.loadUICompletedEventName, function() {
     $("#" + dundas.constants.EXCEL_SORTABLE_DATES.toUpperCase(), dundas.context.currentDialogShown.contents)[0].checked = false;
  })     
  return getUI.apply(this, arguments);
}

This will uncheck the “Sortable Dates” checkbox by default. Or you can add a button which on clicked can generate the excel export for the end user. You can have the required property added to the script that would generate the excel export. We do have a sample script that you can refer to if you want to use this route - https://www.dundas.com/support/developer/script-library/export/create-an-excel-export

Regards,
Pankaj

2 Likes
#3

Also worth noting that there can be some formatting discrepancies when you generate an export via one culture in Dundas and open it using a machine/Excel that uses a different culture. Dates can be especially fickle with this.

2 Likes
#4

Thanks for good answers!

That script was really helpful Pankaj! That way the end-users dont have to worry as much as the sortable dates will be unchecked by default :slight_smile:

And yes Christian you are right, dates can be nightmare regarding this.

#5

That first script option that clears the checkbox can work for you as a special case here since it came from one of our developers (just don’t look at reassigning getCustomConfigurationUI as an example of how normally to use our public APIs). You can also use that second option to use our API to export directly using your preferred options.

As a possible alternative, we can also take another look at the change that led to this scenario and hopefully leave out the time component when you’re displaying the Day level of a time dimension. I added you to that ticket (87841).

1 Like
#6

Thanks Jamie, that would be even better :slight_smile: