SSAS dimension for products shows wrong price format

2
votes

I have a product dimension in my SSAS Cube (SSAS 2016) with different price attributes like sales price and purchase price. The data source is a MS SQL Table with each price column defined as numeric(28,12). I tried different approches to format these columns as currency but failed. The currency format is working fine on the measure groups but not on the product dimension. The browser always shows values like ".320000000000" instead of "0,32". I have no idea left please help.

2
sql-servervisual-studiossasmdx

2 Answers

4
votes

The format option in dimension designer is at least ignored by excel and ssrs.

As a workaround add in your dimension a string/nvarchar attribute, which is formatted according to your desired pattern. You could either manifest the attribute in your datawarehouse dimension table or generate it by using a view.

Addition: To format your string you could use the tsql format function (supported from sql server 2012 on) like in the following example:

SELECT FORMAT(qry.col, 'N2') AS convertedString FROM (
    SELECT CONVERT(numeric(28,12), .320000000000) AS col
    UNION ALL
    SELECT CONVERT(numeric(28,12), 1.1) AS col
    UNION ALL
    SELECT CONVERT(numeric(28,12), 100) AS col
    UNION ALL
    SELECT CONVERT(numeric(28,12), 1000) AS col
) AS qry

The nvarchar result looks like this:

0.32
1.10
100.00
1,000.00
0
votes

You can set the format in the dimension designer. The property lives in the source group, under NameColumn.

SSAS Properties