I'm re-working a report and want to use pivot tables, a pivot graph and slicers to make it more interactive and useful.
I'm using one pivot table to provide an interactive view of the data and then a second pivot to produce a graph. The second pivot isn't very useful for display purposes but is necessary to get the graph looking as I want it. I can use slicers to connect the two pivot tables for filtering, however not for showing detail.
The presentation pivot table can be drilled down (show detail) and I want this action to be repeated in the second graph pivot table so that the extra detail is then displayed on the graph. I've tried a few ways to get around this but have failed and so am looking for help. Essentially I want any action on the presentation pivot to be passed to the graph pivot.
Here's my currently unsuccessful code. However I don't like the approach, I'd have to run this code everytime the presentation pivot is updated and then copy the entire Show Detail statuses for all fields and associated items which seems a lot of work.
Dim i, j As Integer
i = Sheet2.PivotTables("PivotTables2").PivotFields.Count
For i = 1 To Sheet2.PivotTables("PivotTables2").PivotFields.Count
For j = 1 To Sheet2.PivotTables("PivotTables2").PivotFields(i).PivotItems.Count
Sheet2.PivotTables("PivotTables2").PivotFields(i).PivotItems(j).ShowDetail = Sheet4.PivotTables("PivotTables1").PivotFields(i).PivotItems(j).ShowDetail
j = j + 1
Next
i = i + 1
Next
I'm using one pivot table to provide an interactive view of the data and then a second pivot to produce a graph. The second pivot isn't very useful for display purposes but is necessary to get the graph looking as I want it. I can use slicers to connect the two pivot tables for filtering, however not for showing detail.
The presentation pivot table can be drilled down (show detail) and I want this action to be repeated in the second graph pivot table so that the extra detail is then displayed on the graph. I've tried a few ways to get around this but have failed and so am looking for help. Essentially I want any action on the presentation pivot to be passed to the graph pivot.
Here's my currently unsuccessful code. However I don't like the approach, I'd have to run this code everytime the presentation pivot is updated and then copy the entire Show Detail statuses for all fields and associated items which seems a lot of work.
Dim i, j As Integer
i = Sheet2.PivotTables("PivotTables2").PivotFields.Count
For i = 1 To Sheet2.PivotTables("PivotTables2").PivotFields.Count
For j = 1 To Sheet2.PivotTables("PivotTables2").PivotFields(i).PivotItems.Count
Sheet2.PivotTables("PivotTables2").PivotFields(i).PivotItems(j).ShowDetail = Sheet4.PivotTables("PivotTables1").PivotFields(i).PivotItems(j).ShowDetail
j = j + 1
Next
i = i + 1
Next