When using a pivot table and dragging a dimension level that is associated with a date the user may encounter an error. No message will be displayed, 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. Find the field in your staging database. In this example we will be using the Last Date Modified from our Item table.
3. From the Field Transformation dialog select an operator type of Custom . Click Add .
4. In the Transformation Custom SQL dialog, type a convert statement that uses the date field 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 .
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 dimension level into your pivot table.