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

sql-server ssas mdx cube
tedt picture tedt · Feb 15, 2011 · Viewed 16.9k times · Source

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

Craig picture Craig · Feb 15, 2011

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.

Related questions

SSAS MDX WHERE clause syntax - filtering multiple values from the same hierarchy

I'm quite new to data warehousing and MDX queries, and I realize that I'm probably not using the MDX syntax properly in the case below. I'm using SSAS 2005 on MS SQL Server 2005 and would like to query my cube and …

Read More
View MDX query generated while browsing a cube in SSMS

In Sql Server Management Studio once I browse a cube I can drop column fields, row fields and filter fields. This displays the required data. I want to know if there is a way to view the MDX query being …

Read More
MDX: IIf condition on the value of a dimension is always false

I am trying to produce a different output in a column dependent on whether the value of a dimension [Scenario].[Option] is +5 which I've tried to achieve using the IIf function. WITH MEMBER [XorY] AS ( IIf([Scenario].[Option] = +5, 'X', 'Y') …

Read More