Printing PivotCharts with multiple page fields or sorting the printed charts by axis title

[vicky464]

Hi All,

I need to print a PivotChart for each combination of two page fields. I found a little code that helps with printing when there is one page field. When I use it with two page fields it cross references each PageField1 with each PageField2 - if that makes sense. Here is an example of what I mean

Page Field 1 - MedicalPractice
Page Field 2 - Doctor

The data is about how many office visits, well-visits etc.

The code I have used on my other charts (With one Page Field) is:
Sub PrintPivotCharts2()
'prints a chart for each item in the page field
On Error Resume Next
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveChart.PivotLayout.PivotTable
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
pt.PivotFields(pf.Name).CurrentPage = pi.Name
' ActiveSheet.PrintOut
ActiveSheet.PrintPreview 'print preview for testing
Next
Next pf
End Sub



This code works great with only one page field but again when there is more than one it prints for each practice by each doctor. So if there are 10 doctors and 2 practices I end up with 20 charts - which doesn't make sense.

My workaround has been to keep just the doctor in the Page Field area. I put the practice as an Axis Title, so when I print there is just one chart per doctor, and their practice name is listed in the axis title. Seems like a good solution - EXCEPT - there are a few instances where a doctor works for more that one practice (many to many relationship) - therefore the two practices show up at the axis on the same report. I need to send these reports to the practices for each, so that doesn't work. I thought, "why not handle the outliers manually with a slicer" so this is what I am doing.

Here's the kicker - I would really like the reports to print out alphabetically by practice name. Or at least grouped by practice name. As the ultimate goal is to send the reports to the practice. I'd like to avoid someone having to hand sort the reports.

Any thoughts on any of this?

Much thanks!

[vicky464]

May have found my own solution - I concatenated the Practice & DocName in the original data. Used that as the new Page field. I think that did the trick.