Value formatting based on client machine locale

  • Article

In Analysis Services 2000 the vast majority of query and calculation work was done on the client machine via Pivot Table Services.  This included numeric value formatting, and so the client machine locale id was taken into account when the formatting was done.  This meant that a client machine with a US locale id and a client machine with an ES locale id could connect to the same Analysis Services server via ProClarity and have the values rendered on their machine in a format appropriate to their regional settings.

However, with SSAS 2005 an architectural change was made to do almost all querying and calculation work on the server.  This solved a lot of issues for client applications such as ProClarity, particularly in the area of performance, but meant that value formatting was no longer based on the client regional settings, but rather on the server.  This of course means that for clients connecting with ProClarity Professional, all formatting will be the same.  This post will explore the options for formatting values based on client regional settings for ProClarity Professional connections to SSAS 2005.

The first option for setting the value formatting appropriate to the client locale id is to modify the grid properties in ProClarity to show either numeric or custom values.  This will set ProClarity to modify the format of the value that is sent from the cube, and therefore cause regional settings to be accounted for in the application.

Another option is to use a stored procedure (also sometimes referred to as a UDF) in SSAS to set the language via script.  This does require a small customization, but is implemented on the server side, and therefore the more robust option.  For a sample of what this stored procedure might look like, you may refer to this post on the Microsoft Connect site.  https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=468521

For reference, SSAS determines the appropriate formatting based on the settings for the server in this order:

  1. Script (MDX query or calc script)
  2. Cube
  3. Database
  4. Server (in msmdsrv.ini)
  5. Default (US)

Though untested for this blog post, in theory it should also be possible to set formatting based on cube or database language settings, or regional settings for the server hosting SSAS.  Of course this would require considerable overhead in copying cubes or replicating information between SSAS instances hosted on seperate servers, and then would require work to insure users are connecting to the appropriate server/database/cube for their client locale.  It's likely that both the modification of grid properties and the stored procedure referenced above would be a much more efficient and cost effective solutions.

Thanks to Didier, Manlon, and Amanda for their work in researching the question of how formatting based on the client locale can be accomplished, and especially to Didier for his work in putting together the stored procedure example and making the post to MS Connect.