Avoid Calculated Items

I have never found a situation where the calculated item is useful. The concept of a calculated item is that you can add a new item to an existing field.

Let's say that your company makes three product lines. One division of the company is responsible for product lines ABC and DEF. Another division handles product line XYZ. The theory is that you can add a calculated item to total ABC and DEF. The following code produces the report shown in Figure 12.27:

Sub CalculatedItemsAreEvil() Dim WSD As Worksheet Dim PTCache As PivotCache Dim PT As PivotTable Dim PRange As Range Dim FinalRow As Long

Set WSD = Worksheets("Pivot Table")

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

PT.TableRange2.Clear Next PT

1 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 1 Set up the row fields

PT.AddFields RowFields:="Product", ColumnFields:="Data"

1 Define calculated item along the product dimension PT.PivotFields("Product").CalculatedItems.Add "DIVISION1", "=ABC+DEF" 1 Resequence so that it appears as the 3rd product PT.PivotFields("Product").PivotItems("DIVISION1").Position = 3

With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 1 .NumberFormat = "#,##0" .Name = "Total Revenue" End With

With PT.PivotFields("Profit") .Orientation = xlDataField .Function = xlSum .Position = 2 .NumberFormat = "#,##0" .Name = "Gross Profit" End With

1 Ensure that we get zeroes instead of blanks in the data area PT.NullString = "0"

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

End Sub

Figure 12.27

Unless you love restating numbers to the SEC, avoid using calculated items.

Figure 12.27

Unless you love restating numbers to the SEC, avoid using calculated items.


0 0

Post a comment