Home / Analysis Services / SSAS: How to format a dimension attribute?

SSAS: How to format a dimension attribute?

 I was looking for a method of formatting a dimension attrbibute in SSAS2008R2 to, for example a currency symbol €. In search of this question, I found out this is not possible within the default SSAS settings (format column). I thougt this was possible just like the measures settings with the “format” option.

Situation; business users needed a way to analyse “hours worked” by “hourly rate”. Solution was to create a SSAS dimension “Hourly Rate” with attribute “Hourly Rate”.

After setting up everyhing like DataSourceView, Dimension and processing the Cube, the dimsion looked like this;


And in Excel, it even looked worse like 10.0000000 and 11.50000000, not so user-friendly. This also gives me issues with excel browsing, sorting etc.

After research on the internet, settings could be made via the “Formatting options” into the dimension attribute. The “format” option is not visible in SSAS by default. The trick is to set to “Name column” first, the additional options wil appear:



But after setting the format to Currency OR #,##0.00 \€;(#,##0.00 \€), nothing happens.

The solution for this is to set proper format in the Source SQL for the data. My place is the view which I use for the datasource.

CAST(UurtariefKracht * 1000 AS INT) AS UurtariefKrachtID
, REPLACE('€ ' + CAST(CAST(UurtariefKracht AS MONEY) AS VARCHAR(16)), '.', ',') AS TariefKracht
FROM dbo.FactDeclaratieRegel AS FD


Now after changing my Source SQL, I needed to update my Data Source View. After this, I changed my dimension. Setup the new column (or changed column) as “NameColumn” in the attribute properties:



Now Process the dimension.





So I showed you how to format the attribute dimension properties in SSAS to show a Currency format.

Check Also

Analysis Services, Reporting en Excel 2007

Momenteel werk ik op een project waarbij SQL Server 2005 (64-bit), Analysis & Reporting Services …

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *