Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at element61, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

Changing the 1000 separator in Power View

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:

Koen_Table1

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.

Koen_PowerViewFormat

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.

Koen_Table2

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.

Koen_CubeFormat

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.

Koen_SiteSettings

Regional settings make sense since we need to change locale information.

Koen_RegionalSettings

And indeed, we can change to our beloved locale.

Koen_RegionalSettings2

When opening the report again Power View, we get our desired result:

Koen_Table3

Conclusion

The SharePoint regional site settings control how data is displayed in your Power View report.

Comments

Leave a comment on the original post [blogs.lessthandot.com, opens in a new window]

Loading comments...