The projects that are available for download from the CubeStore have a default date format set in the cubes of "MM/DD/YY". This can be easily changed in the project to accommodate various regional preferences.
To alter the default date format, change the Date table in the staging database. The table will have a clock icon next to it and it is possible that this table is in the data warehouse depending on the version of the project that was downloaded from the CubeStore. Expand the Date table found in the staging database in the Jet Data Manager and identify the DisplayDate field.
Right-click on the "Custom value" and select Edit Custom Transformation. The Custom Transformation window will appear.
The number value "1" is part of the SQL "CONVERT" function and represents the "MM/DD/YY" format. The "1" should be replaced with the appropriate number that represents the desired format. A list of formats can be viewed under the Remarks section at the following link: Cast and Convert . In this example we will use the British/French standard which is represented by the number "3".
Click Ok and then deploy and execute the Date table in the data warehouse.
Click on the Cubes tab in the Jet Data Manager and deploy and execute the OLAP Servers node at the top of the Cubes tab.
The default date format was originally set to "MM/DD/YY" which would display as the following in Excel:
Once the change has been implemented, deployed and executed in the Date table in the staging database, data warehouse and cubes, viewers will see dates with the designated date format in Excel. In this example: "DD/MM/YY".