Menu
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
227
Now you can limit the decimal places. Select the number cell and in the Menu, go to Format > Number > More Formats > Custom number format.
The ROUND() function rounds a number to a specified number of decimal places.
Rounding a decimal number to two decimal places is the same as rounding it to the hundredths place, which is the second place to the right of the decimal point. For example, 2.83620364 can be round to two decimal places as 2.84, and 0.7035 can be round to two decimal places as 0.70.
We can use DecimalFormat("0.00") to ensure the number always round to 2 decimal places. For DecimalFormat , the default rounding mode is RoundingMode. HALF_EVEN , and we can use setRoundingMode(RoundingMode) to set a specified rounding mode.
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.
121
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'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
