locked
Help with formatting date dimension in Excel RRS feed

  • Question

  • Hello,

    I'm using SQL Server 2014 and Microsoft Excel 2013. I have an OLAP cube hosted in SSAS with a date dimension. In the SQL (dimension) table the data type for the key column is SQL type "date"). The SQL (time) table primary key field is set to both display and key column in the cube dimension.

    When I connect to the cube in Excel the dates come in as expected (correct sort order with display YYYY-MM-DD (e.g. 2016-02-08). If I want to format a date field I can select an empty cell in the Excel Workbook and perform an operation like this "=TEXT(A9, "mmm")" and the expected conversion result is returned. However, if I use the mouse the select all the date cells in the area of the Workbook controlled by the PivotTable, right-click and choose "Format" and try to apply a date format to the selected cells nothing happens (no error, the format dialog just closes and the cells containing date in format YYYY-MM-DD remain the same.

    The desired functionality is to be able to accomplish the format functionality inside the area of the worksheet controlled by the pivottable. What can I change in my cube to make this possible? If it's not possible to do this, is there any documentation on-line I can reference to verify. I need to be able to prove this is not possible and someone who believes it is is challenging this. 

    Best Regards!

    Jeremy

    Tuesday, February 9, 2016 9:11 PM

Answers