SSAS: How to format a dimension attribute?

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;

10.00
10.50
11.50

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.

SELECT DISTINCT
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.

Result:

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