Any way to control number of decimal places while browsing SSAS cube?

Question

When I browse the cube and pivot Sales by Month ,(for example), I get something like 12345.678901.

Is there a way to make it so that when a user browses they get values rounded up to nearest two decimal places, ie: 12345.68, instead?

Thanks,

-teddy

Answer 1

You can enter a format string in the properties for your measure or calculation and if your OLAP client supports it then the formatting will be used. e.g. for 1 decimal place you'd use something like "#,0.0;(#,0.0)". Excel supports format strings by default and you can configure Reporting Services to use them.

Also if you're dealing with money you should configure the measure to use the Currency data type. By default Analysis Services will use Double if the source data type in the database is Money. This can introduce rounding issues and is not as efficient as using Currency. See this article for more info: The many benefits of money data type. One side benefit of using Currency is you will never see more than 4 decimal places.

Answer 2

Either edit the display properties in the cube itself, so it always returns 2 decimal places whenever anyone edits the cube.

Or you can add in a format string when running MDX:

WITH MEMBER [Measures].[NewMeasure] AS '[Measures].[OldMeasure]', FORMAT_STRING='##0.00'

Answer 3

You can change format string property of your measure. There are two possible ways:

1. If measure is direct measure -

   Go to measure's properties and update 'Format String'

2. If measure is calculated measure -

   Go to Calculations and update 'Format String'