Problem in connecting Slicer to PivotTables

[mfexcel]

Hi all,

When I tried to connect a slicer to different PivotTables, the following message comes:
"An error occurred and the slicer cannot currently be created or connected to PivotTables."

I have around 20 PivotTables and >50 Fields.

The thing is there is no problem for me to make connection to exactly the same set of PivotTables by using another Fields. The problem seems to apply to two particular fields only. What could be the reason? And any chance to rectify it???

Advice is appreciated!

 

[mfexcel]

Hi, anyone?

 

[ACurtin]

I know this response is too late for the original question but I noticed the same behavior in my workbook and was able to work around it so here was my solution in case others with the same issue find this post. The issue was the "Include new items in manual filter" option on fields I was attempting to link with the slicer.

In pivot table 1 I had checked off that option on my filter fields after creating slicers for them. When I went to pivot table 2 which is fed by the same datasource but didn't have the same fields added to the filters and attempted to connect the slicer to that table I got the error: "An error occurred and the filter control cannot currently be created or connected to PivotTables." In pivot table 2 I added all the fields I wanted to connect to the pivot filters, right-clicked each one and opened that field's settings, and checked off the option to "Include new items in manual filter". After doing that I was able to go into the Filter Connections for that table and link all the fields to my slicers from the first table after which I could remove the filters from the pivot while still filtering both pivots as a unit.

It would appear that when connecting pivot tables via slicer, the "Include new items in manual filter" option must be the same (on/off) for each filtered field in all pivot tables you are connecting to the slicer. This seems like it would make sense since a filter that was operating on multiple tables couldn't possibly do so if one table was adding new items by default and the other was not. I just wish that Microsoft had been kind enough to mention that in the error message.

 

[PWHITTAK]

Thank you for saving me from going completely insane. I Must have checked this option on one of the PT's. Couldn't understand why it wouldn't work. I have finally got there thanks to you.

 

[Dobo_Bobo]

Thank you ACurtin, thank you Mr Excel, thank you Google! You have all saved me from jumping out of the window!!!

Many thanks for taking the time ACurtin to provide the root cause!

 

[vargumedo]

Hi all,

When I tried to connect a slicer to different PivotTables, the following message comes:
"An error occurred and the slicer cannot currently be created or connected to PivotTables."

I have around 20 PivotTables and >50 Fields.

The thing is there is no problem for me to make connection to exactly the same set of PivotTables by using another Fields. The problem seems to apply to two particular fields only. What could be the reason? And any chance to rectify it???

Advice is appreciated!

Just modify the Pivot table "Field Settings" doing the following:

1. Right-click one of the items in the pivot field, and click Field Settings.
2. On the Subtotals and Filters tab, in the Filter section, remove the check mark for 'Include New Items in Manual Filter'
3. Click OK.
4. Repeat the steps for all the Pivot Tables.