There is an error generated when users try to pivot on an Excel export generated from a report made with Ad Hoc report wizard in CRM:
“The PivotTabIe field name not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTabIe field, you must type a new name for the field.”
To reproduce the error:
- Export from a report generated with the Ad hoc report wizard, preferably with no groupings.
- Open the Excel file and enable editing.
- Click Insert from the Ribbon, choose PivotTable, follow the usual steps and you will see the error.
Why is this happening? Click on the first cell in the report, and you will notice that the A and B columns are merged, and Excel sees B as a blank column that needs to have column headings. Also, in my example above, the K and L columns are also merged. Also, note that the O column is hidden.
The CRM team knows about this and it should be fixed in an upcoming version. In the meantime, here is the workaround I gave to my client.
- Select merged columns (A and B & K and L in the screen shot above – use the control key to select both groups at the same time, or just do it one group at a time). The O column is not an issue in this example, but it may be something you need to address – look for any missing columns, and expand them to be sure.
- Click the Merge & Center button on the ribbon to unmerge the columns.
3. Delete any blank columns that appear.
You can now insert a pivot table using the standard steps.