Recently I was designing a simple Power View report on top of a multi-dimensional SSAS cube. Out of the box, one of the tables looked like this:
For readability purposes, a 1000 separator is added to the numeric value. However, the comma was used, as is common in the United States. In Belgium (and other countries in Europe), we use the single point as the 1000 separator and the comma as the decimal separator. Of course the business preferred to use the point as the separator. But where do we change this?
In Power View itself?
When clicking on a cell in the table, you can change the formatting in the Home tab of the ribbon.
First you have to set the type to Number, at which point you can select the comma style. Unfortunately, this only changes the type to Accounting while using the same separators.
There is no way to influence what kinds of separators are used. Remarkably, you can set the comma style on, but you cannot turn it off.
In the underlying cube?
I vaguely remember that Excel from time to time picks up the formatting that is specified in the cube. Maybe Power View does the same. You can set the format string of a measure in the properties pane.
This great article by Valentino Vranken (blog|twitter) details the various options for format strings: Formatting Numbers [SSRS]. Yes, it is about SSRS, but a lot of the concepts are true for other tools as well. However, when we delve deeper into custom format strings, it becomes clear that a format string does not dictate what symbol is used, but that this is determined by the culture. As per MSDN:
The NumberGroupSeparator and NumberGroupSizes properties of the current NumberFormatInfo object determine the character used as the number group separator and the size of each number group.
So we need to look into the culture used when the Power View report is run. Since we are using Power View in SharePoint, it makes sense to take a look at SharePoint.
SharePoint to the rescue…
Let’s take a look at the site settings.
Regional settings make sense since we need to change locale information.
And indeed, we can change to our beloved locale.
When opening the report again Power View, we get our desired result:
The SharePoint regional site settings control how data is displayed in your Power View report.