This seems pretty dangerous to do in the user interface because you have absolutely no indication that the Product dimension in the page field is limited by any criteria. Figure 12.39 shows the resulting pivot table. There is no indication that the numbers exclude XYZ until you happen to touch the Product drop-down, as shown in Figure 12.40.

Figure 12.39

The Product page field excludes XYZ, but there is no visible indication of this.

Figure 12.40

As soon as someone touches the Product drop-down, he may realize that the report has been filtered to include only two products.

Figure 12.39

The Product page field excludes XYZ, but there is no visible indication of this.

This seems to be a pretty misleading state in which to leave your pivot table. However, if you are in control with VBA and add a proper title to the copied report, this seems to be a fine way to produce reports for a certain division.

This process is even easier in VBA. You can immediately build the table with Product in the page field and then loop through to change the Visible property to show only the total of the products in a certain division. The following code produces the report shown in Figure 12.41:

Sub ProduceReportOfTwoProducts()

' Exploit an apparent anomaly to put

1 two products in a single page field to

' produce a summary report of just products ABC and DEF

Dim WSD As Worksheet Dim WSR As Worksheet Dim WBN As Workbook Dim PTCache As PivotCache Dim PT As PivotTable Dim PRange As Range Dim FinalRow As Long

Set WSD = Worksheets("Pivot Table")

' Delete any prior pivot tables For Each PT In WSD.PivotTables

PT.TableRange2.Clear Next PT

' Define input area and set up a pivot cache FinalRow = WSD.Cells(65536, 1).End(xlUp).Row Set PRange = WSD.Cells(1, 1).Resize(FinalRow, 8)

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)

Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Range("J2"),

TableName:="PivotTable1") PT.ManualUpdate = True ' Set up the row fields

PT.AddFields RowFields:="Region", ColumnFields:="Data", _ PageFields:="Product"

1 Define calculated fields

PT.CalculatedFields.Add Name:="GP_Pct", Formula:="=Profit/Revenue"

' Set up the data fields With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 1 .NumberFormat = "#,##0,K" .Name = "Total Revenue" End With

1 Calc the pivot table PT.ManualUpdate = False PT.ManualUpdate = True

' Make sure all Pivotltems along product are visible For Each Pivltem In PT.PivotFields("Product").PivotItems

Pivltem.Visible = True Next PivItem

1 Now - loop through and keep only certain items visible For Each Pivltem In PT.PivotFields("Product").PivotItems Select Case PivItem.Name Case "ABC", "DEF"

PivItem.Visible = True Case Else

PivItem.Visible = False End Select Next PivItem

' Add the remaining data fields With PT.PivotFields("Profit") .Orientation = xlDataField .Function = xlSum .Position = 2 .NumberFormat = "#,##0,K" .Name = "Gross Profit" End With

0 0

Post a comment