In a pivot table while dragging a dimension level that is associated with a date, the user may encounter an error. No message will be shown, but the user will be unable to select / drag the dimension level.
To resolve this error, you will need to convert the field from a datetime to a text data type.
1) Inside Staging database, we will use the Last Date Modified from our Item table.
If you hover over the field with your cursor, it will display the data type of the field.
2) Right click the field and select Field Transformations.
3) From the Field Transformation dialog select an operator type of Custom. Click Add.
4) In the Transformation Custom SQL window, type the convert statement that uses the date field which you are trying to convert as the expression.In this example, we are trying to convert the Last Date Modified field. To do this, we start typing our convert statement, enter a data type of varchar(20), drag the field from the right pane to the left pane, and apply a style as the final parameter. Once finished, click OK.
For more information on the CONVERT statement see the following link: CONVERT Statement
5) Drag and drop the Last Date Modified field into your data warehouse. Right click the field and select Edit Field. The Edit Custom Field dialog opens. In this dialog, make sure the data type matches the one you stated in your convert statement above. Click OK.
6) From the ribbon, click Deploy and Execute Modified Tables and Views to apply the changes above.
7) Deploy and execute any dependent objects in our OLAP database.Once the deployment and execution is completed, you should now be able to drag the date dimension level into your pivot table.
