Problem with number format when exporting data to a dynamic worksheet

(1) Share

Report

Posted on by Community Member

I need to export some data from Excel to a dynamic worksheet.

Exporting alone works fine. However, there's a problem with ALL numbers containing fractions (currently the vast vast majority of those come from CRM decimal fields). Allow me to explain: I'm using Polish culture (as my client will be using), CRM is using Polish culture. Polish culture calls for using a comma as the decimal symbol. However, despite everything working in the same culture, MS CRM returns the data using a full stop as a decimal symbol.

This is a problem, because when this data is opened in Excel (or refreshed in Excel) then it's impossible to calculate sums, averages or use pivot tables with anything but the basic "count" functionality.

Given that all across the board I'm using the same culture, I don't know what could be the issue.

I CAN change the culture on the system where I'm opening the dynamic worksheet to use a full stop as a decimal symbol, and it works, but this is hardly a proper solution for my client.

How can I tackle this issue?

PS. I'm marking this a development - I have full control over the CRM, so I can add / modify plugin code.

PPS. Chances are this is an Excel issue - at this point I cannot say which part is responsible for this issue.

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Community Member on at

Like (0)

Report

I would also like to add that the data is created on the fly by a PostRetrieve plugin for a specially created CRM entity. The decimal fields are filled, well, as decimal fields, so it seems there's no way to modify anything related to region and formatting from that end.

Was this reply helpful? Yes No
Friedhelm Wolf on at

Like (0)

Report

We have the exact same issue with CRM Online 2015 Update 0.2 and German Regional Settings which have a comma as decimal separator as well!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I have got the same issue using the German Regional Settings. Have you already found a solution?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Well if the data is manipulated on the fly using a post retrieve plugin, it should not be difficult to format the data using the system/usersettings retrieved inside the plugin. You may also try changing your O365 region and language settings and that of your local excel.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Formatting the data on post retrieve is a workaround, but it's as bad as just changing the system regional settings. It's short-sighted.

The problem is that CRM serves the data as HTML and column types get lost. Ideally it should preserve the type of the column and leave the formatting to the client. After all - you have no idea who will use the data in the end and what their system locale is. It doesn't have to be the same as the CRM setting.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Having the same issue in Sweden.

Was this reply helpful? Yes No
Fede Bergamaschi 100 on at

Like (0)

Report

Hello everybody,

any update on this issue? We are facing the same behaviour with italian settings, Dynamics 365 on prem.

Thanks in advance

Federica

Was this reply helpful? Yes No
Steve Dohm 5 on at

Like (0)

Report

This is STILL an issue. I have pivot tables built around the date format. When the format changes on refresh (not every time), my pivot table fails because months and years are no longer recognized. Also, when the data is filtered and sorted, the filter and sort is eliminated. I have to build macros to filter and sort after each refresh. After 4 years, you would think this would get addressed?

Was this reply helpful? Yes No