Getting Error in an Excel 2010 PivotTable “The field that you are moving cannot be placed in that PivotTable area” when referring to PowerPivot data sources

We kept getting this error when attempt to drag and drop dimensions attributes into a PivotTable in Excel 2010

“The field that you are moving cannot be placed in that PivotTable area”

We were trying to use an Excel workbook to drop in dimension attributes that are sourced from data living in the PowerPivot for Sharepoint Farm. We had our ODC file pointing to Analysis Services (MSOLAP provider) and the data source as the HTTP address of the XLSX living in the sharepoint farm. This means we are using Excel Services to feed in data to the client’s Excel Pivot Table from the server side PowerPivot workbook.

I used the sample PowerPivot workbooks from here to do some trial and error https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=102

I confirm the problem is by design. Here’s why…

You cannot use cube dimensional attributes in the Values area box in an Excel PivotTable. That is the way regular PivotTables work. You may have been spoiled by PowerPivot Pivot Tables which allow you to drop in dimensional data attributes from a hierarchy into the Values area.

If you need this in a regular PivotTable to do the same for a PowerPivot workbook, there is a workaround. You can create the implicit measures in the server side PowerPivot workbook to get the ones that you need to share with the read only users ahead of time.

The PowerPivot workbook when stored in PowerPivot for Sharepoint, and accessed via Excel Services over HTTP is much like a Tabular Cube being accessed via MSOLAP provider.

To quote this KB article Differences between OLAP and non-OLAP PivotTables in Excel: https://support.microsoft.com/kb/234700

Layout and Design Differences

Dimensions vs. Measures

When you are working with a PivotTable report based on OLAP source data, dimensions can be used only as row, column, or page fields. Measures can be used only as data fields. When you drag a dimension to the data field drop area, or a measure to the row, column, or page field drop area, you receive the following error message:

The field that you are moving cannot be placed in that PivotTable area.

When a PivotTable report based on OLAP source data is active, the PivotTable toolbar displays an icon next to each row of fields. The icon indicates where Excel will allow you to place the field in your PivotTable report. If the icon is darker in the upper left, then the field is a dimension that you can drag to the row, column, or page field drop areas. If the icon is darker in the lower right, then the field is a measure that you can drag to the data field drop area.

The confusion between classic Excel Pivot Tables and PowerPIvot Pivot Tables

There is a conceptual difference between a classic Excel OLAP-based pivot table and a PowerPivot pivot table.

When using your source workbook as a regular PivotTable in this manner, you can only use those fields that are measures. In your case, the only measures that you have designed is “Sum of MeasuresDistinct Count of patient”. This is one of the advanced modeling activities that PowerPivot supports that Excel pivot tables do not.

• In a PowerPivot pivot table, you can pull down any numeric measure.
• In a classic Excel pivot table, you can only use measures in the cube

In PowerPIvot, when you pull down a numeric and drop it into the Values area , we create what we call an “implicit measure” behind your back. Classic Excel pivot tables cannot do that kind of automatic implicit measure.

Possible Solution:

What I suggest that you do is to take your numeric fields and drop them into the Values area in the original PowerPIvot workbook which lives in the PowerPivot gallery in Sharepoint. Doing so will create the implicit measure for you.

Then when you walk through the process to refer from Excel Services regular PivotTable to the PowerPivot “cube” in sharepoint, you will see a new measure group that you can drag down in the Excel pivot table.

It will show the implicit measures and their aggregate type (Count of, Sum of, etc).

Example of the PowerPivot workbook:

=====================================

Let’s prove it gives the error by design, and try the workaround:

A> I can repro the problem like this:

1. Upload your favorite PowerPivot xlsx into the PowerPivot gallery in Sharepoint.

https://site/PowerPivot/PowerPivot Healthcare Audit.xlsx

2. Now make an Excel workbook which refers to data on the HTTP address of the PowerPivot workbook.

Let’s point the Excel piivot table to data in the server side PowerPivot HTTP via Excel Services.

The steps are listed in the following article under the heading “Import data from PowerPivot Workbook”.
https://technet.microsoft.com/en-us/library/gg399165.aspx

 

As the MSDN article said, refer to Analysis Services in the Data tab

Point to the HTTP address of the PowerPivot workbook in Sharepoint.

The PowerPivot’s internal cube will appear

Rename if desired, and finish the ODC connection.

3. We can insert a PivotTable report into the workbook when prompted.

4. We can add measures from the PowerPivot’s “cube” into the Aggregated Values pane.

5. We  can add Dimensional data into Rows or Columns

6. We cannot add Dimensional attribute data into the Aggregated Values pane.

 

B> Contrast that with an OLAP based PivotTable. The same error applies to any PivotTable pointing to Adventureworks Multidimensional OLAP. Dimensional data cannot be places in Values pane.

1. Make a ODC connection to a multidimensional Analysis Server (MSOLAP provider where the Data Source is an AS server ,not the http PowerPivot .xlsx workbook)

2. Create a Excel PivotTable based on that connection.

 

3. Try to move a dimensional hierarchy attribute into the Values box.

C. Let’s compare to a PowerPivot Pivot table where Dimensional Data can be dropped into the Values box.

1. Click the PowerPivot ribbon > Pivot Table button

2. Note the PowerPivot Field List is different – and has 6 area boxes rather than the 4 above.

In a PowerPivot Pivot table you can use Dimensional data in the aggregated Values box. The same field which fails in a regular Pivot Table (as shown above) can be added just fine.

So dropping a dimensional measure created the “Count of _____” implicit measure on your behalf.

D. Now Let’s try to resolve the problem by making implicit measures in the PowerPivot living in Sharepoint.

In the original PowerPivot workbook in the Gallery, the BI Developer can designate which fields should be declared as implicit measures.

1. Open the PowerPivot workbook from the gallery.

2. Open the Workbook in Excel (make sure the PowerPivot for Excel add-in is installed)

3. Make a new sheet and using the PowerPivot ribbon, add a “PivotTable” (the PowerPivot kind) and use the dimensional values that you need to use later in Excel Services in the PowerPivot field list for Values to create the implicit measures, such as COUNT OF ____ and SUM OF ____ shown here.

4. Save the workbook, so the new implicit measures are added back into the PowerPivot for Sharepoint server.

5. Now on the client in a different regular workbook, opened from Excel Services, or from Excel 2010 client itself… make a regular Pivot Table and choose the HTTP connection to the PowerPivot  XLSX in sharepoint (http uses MSOLAP via Excel Services)

6. In this workbook, you can see the implicit measures which were defined automatically in the PowerPivot “cube” itself living in Sharepoint. I can use those implicit measures in the regular PivotTable as aggregated values.

Voila!