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;

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:

SSAS_Format_Dimension_Attribute

 

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

SSAS_Format_Dimension_Column

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:

SSAS_Format_Dimension_Attribute2

 

Now Process the dimension.

Result:

SSAS_Format_Dimension_Attribute3

 

 

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 antwoord

Het e-mailadres wordt niet gepubliceerd.