Format Date for a Dimension in Cube

1
votes

I have a SQL Server Analysis Service OLAP cube where one of the dimension fields, is a datetime field. This is not a Date Dimension (Like the ones that can be generated.). This represents a month end period for the data. It can be filtered for example to see the end of year 2013.

The problem is the sql server data field is a datetime field, other than changing the type to just date, how can I change the format to exclude the time? Basically; MM/DD/YYYY.

Measures seem to have the ability to be formatted (Currency for example), but not Dimensions.

I looked at the view, but there's not formatting options their either. Maybe I need to look at the view code and add sql formatting code there?

Excel: I can't change the format on the cells in Excel either.

1
formattingssas

1 Answers

0
votes

Try to format the Dimension Attribute field in your view first. Example:

FORMAT(hub.DateAlternateKey,'MM/dd/yyyy','en-US') AS DateAlternateKey

Then you should go under attribute properties of your Date Dimension's Day Attribute and set NameColumn=DateAlternateKey. This attribute should be set to Type=Date.