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.
Find the field in your staging database.
In this example we will be using 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.
Right click the field and select Field Transformations
From the Field Transformation dialog select an operator type of Custom. Click Add.
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.
For more information on the CONVERT statement see the following link: CONVERT Statement
Navigate to the field in 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 .
From the ribbon, click Deploy and Execute Modified Tables and Views to apply the changes above.
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.