• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Slicers not connecting to PivotTables

S

salir

New Member
I have created a dashboard using multiple slicers connecting to a pivot table. This dashboard works as designed. I then added another pivot table using the same datasource in another workbook. Now when I connect the slicers to this new pivot table I get the following error 'an error occurred and the slicer cannot currently be created or connected to pivot tables'. The same fields are on both pivot tables..Kinda frustrating.


Thanks,

Salir
 
Hi Salir. Slicers work on pivotcaches, and pivotcaches can't be shared across workbooks. You'll have to create both pivots in the same workbook to use slicers. Is there any reason you can't have both pivots in the same workbook?


Note that I recently posted some code at the Contextures blog that could be amended to your needs. See http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/


Note that this code will require substantive changes so that it operates across workbooks, and I'm not volunteering to do it (because the code is very complex, and adjusting it will take a bit of time). But if you are proficient in VBA then this may be a good starting point.
 
The dashboeard has three slicers controlling a pivot chart. The underlying pivot table is in a separate workbbok. I initially created the new pivot table which is basically tabular data of for the same datasource. I then cut/paste this pivot table into the main dashboard. With the hope that I can connect the three slicares to this tabular pivot table.


My basic thought process was to have a chart at the top of the dashboard and the corresponding detatils being dsplayed at the bottom using slicers to control both the chart and the pivot table.


So, to answer your question my pivot table and pivot chart are in the same workbook. Its very odd that one of the three slicers is able to connect to both the pivot tables. I deleted the other two slicers and recreated them again. But I still get the same error.
 
Can you upload a sample workbook and post a link here?

You could also run this code, which will tell you what the pivotcache is of each pivottable:

Option Explicit
Option Base 1

Sub PivotReport()

Dim pc As PivotCache
Dim pt As PivotTable
Dim lngPivots As Long
Dim lngPosition As Long
Dim wks As Worksheet
Dim varSettings As Variant
Dim bWorksheetExists As Boolean
Dim wksOutput As Worksheet
Dim rngOutput As Range
Dim loOutput As ListObject

'The purpose of lngPosition? I don't want to hard-code the array position of each element at this early development stage.
' So I just increment a counter, which means I can move these headings around, and as long as the correstponding
' code that records the actual attributes is in the same order, I don't have to renumber the array elements
' any time I make a change.
lngPosition = 1
ReDim varSettings(14, 1)
varSettings(lngPosition, 1) = "Pivot Name"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "Worksheet"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "Worksheet Protected?"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "CacheIndex"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "Pivot Address"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "RowGrand"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "ColumnGrand"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "MissingItemsLimit"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "RecordCount"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "MemoryUsed (kB)"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "SaveData"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "EnableRefresh"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "SourceData"
lngPosition = lngPosition + 1
varSettings(lngPosition, 1) = "EnableDrilldown"
lngPosition = lngPosition + 1

lngPivots = 1

For Each wks In ActiveWorkbook.Worksheets
For Each pt In wks.PivotTables
lngPosition = 1
lngPivots = lngPivots + 1
ReDim Preserve varSettings(14, lngPivots)

With pt
varSettings(lngPosition, lngPivots) = .Name
lngPosition = lngPosition + 1
varSettings(lngPosition, lngPivots) = wks.Name
lngPosition = lngPosition + 1
varSettings(lngPosition, lngPivots) = wks.ProtectContents
lngPosition = lngPosition + 1
varSettings(lngPosition, lngPivots) = .CacheIndex
lngPosition = lngPosition + 1
varSettings(lngPosition, lngPivots) = .TableRange2.Address
lngPosition = lngPosition + 1
varSettings(lngPosition, lngPivots) = .RowGrand
lngPosition = lngPosition + 1
varSettings(lngPosition, lngPivots) = .ColumnGrand
lngPosition = lngPosition + 1
varSettings(lngPosition, lngPivots) = .PivotCache.MissingItemsLimit
lngPosition = lngPosition + 1
varSettings(lngPosition, lngPivots) = .PivotCache.RecordCount
lngPosition = lngPosition + 1
varSettings(lngPosition, lngPivots) = .PivotCache.MemoryUsed / 1000
lngPosition = lngPosition + 1
varSettings(lngPosition, lngPivots) = .SaveData
lngPosition = lngPosition + 1
varSettings(lngPosition, lngPivots) = .PivotCache.EnableRefresh
lngPosition = lngPosition + 1
varSettings(lngPosition, lngPivots) = .SourceData
lngPosition = lngPosition + 1
varSettings(lngPosition, lngPivots) = .EnableDrilldown
lngPosition = lngPosition + 1

End With
Next pt
Next wks

'Create a worksheet to dump the output into
Set wksOutput = Sheets.Add
Set rngOutput = Range("A1").Resize(UBound(varSettings, 2), UBound(varSettings, 1))
rngOutput.Value = Application.Transpose(varSettings)
Set loOutput = wksOutput.ListObjects.Add(xlSrcRange, rngOutput, , xlYes)

End Sub
 
Unfortunately my code behind skills are weak. May not be able to test what you have provided. Thanks a lot for taking the time to help.


Salir.
 
Hi all,
I have the same problem here. But all my pivot tables are within the same workbook. And I had successfully created and connected many slicers to all pivot tables; only 2 slicers (fields) fail. Any idea???
 
Hi, WMF!

Are you very hurried? If so please take a break and dedicate 5 minutes to what follows. Posting without adding any useful information just for bumping up a topic doesn't guarantee neither a faster assistance nor getting the interest of people who might be reading that post, but rather the opposite effect of discouraging them.

If you'd have read the 1st forum at the main page...
http://chandoo.org/forum/forums/new-users-please-start-here.14/
...you should have noticed this points (and if you did it seems as if you should do it again):

"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."

"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."

"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."

"Say "Thanks", whenever you can. Recognize when someone has bothered to go to the trouble and time to assist you with your question for free. Often readers will spend several hours working on a solution to a problem, a line of recognition will go a long way."

Regards!

PS: If it's a high priority issue and you can't even wait... how much?... oh, almost 23 hours... absolutely unbearable... but you always have the alternative of hiring a local consultant who may provide you the exact tailored service. Should I remember you that these forums are supported by contributors who dedicate part of their time in an absolutely free way to help other people and answer questions?
 
Hi SirJB7,

Thanks for your response although it is quite discouraging, esp. to someone new to the forum.

To answer your question,

Firstly, I am not in particular hurry. As I was riding on an old thread that was posted about half year ago, I just wanna find out if my question was seen by the readers as a question, not an answer to the original post. Now I know it.

Secondly, I googled the topic a bit before I came to this thread. I was curious that if someone is having the same problem that I have and that curiosity drove me to post a follow-up post the following day. Curiosity is a drive for self-learning. Pushing for an answer was not my intention.

Finally, I respect all everyone who contributes to this forum as all of them make the forum a great one. Effort is highly appreciated.

Regards,
 
Hi Sir JB7,

I had a similar situation. The root cause in my case was that i had two data sources with at least one the same field name. I have created certain pivots and slicers using one data source and using that field name, and wehn creating the slicers for the second data source set using the field with the same name I got the error message. In my case I just renamed the field in one of the data source set and it has worked.

I hope it will help you.


Regards,
Valentin
 
I agree with @Valentin, but I would describe differently.
I have 5 PivotTables connected to the same data source. I also had several slicers, created from one PivotTable. I tried to connect the Slicers to all of the PivotTables. All but one Slicer would connected to my multiple PivotTables.

When I took a look, I noticed that one of the column headers in my source data (your data before any Pivots) matched the name of the Slicer. I had not named the Slicer, Excel did. I changed the column header in my Source Data, went back to the PivotTable that created all of the Slicers and re-inserted the column. All worked well after that.

It's interesting, because all of the names are the same now (just changed to the new name), but the Slicer now connects to multiple PivotTables.
 
You must log in or register to reply here.
Back
Top