Excel Pivot Table - Calculated Items Problem

[BobsySpud]

I have created a Pivot table with the following fields:

Customer; Product; Actual; Budget; Variance where 'Variance' is
calculated Item within field name 'Version' using formul
'Actual-Budget'. (ie Some records in the raw data have version set t
'Actual' and others have version set to 'Budget'. I am trying t
compare Actual and Budget). The problem I have is that when I use thi
calculated Item, the results displayed include combination of ever
possible Product and Customer even if the data does not include such
combination. This means that I have a huge pivot table with hundreds o
blank lines.

Has anyone encountered this problem, and if so, does any one know of
suitable fix?

Please advise.

Thanks

Bobs:confused

 

[Debra Dalgleish]

If you have Excel 2002, or later version, you can use a macro similar to
the following, to hide calculated items that are zero:

'=============================
Sub HideZeroCalcItems()
'hide rows that contain zeros for calculated items
'by Debra Dalgleish
Dim r As Integer
Dim i As Integer
Dim pt As PivotTable
Dim pf1 As PivotField
Dim pf2 As PivotField
Dim df As PivotField
Dim pi As PivotItem
Dim pi2 As PivotItem
Dim pd As Range
Dim str As String
Set pt = Sheets("Pivot").PivotTables(1)
Set df = pt.PivotFields("Units") 'data field
Set pf1 = pt.PivotFields("Item") 'column field
Set pf2 = pt.PivotFields("Rep") 'row field
Set pi = pf1.PivotItems("YearVar") 'calculated item

For Each pi2 In pf2.PivotItems
pi2.Visible = True
Next pi2

i = pf2.PivotItems.Count
For r = i To 1 Step -1
On Error Resume Next
str = Cells(r + 5, 1).Value
Set pd = pt.GetPivotData(df.Value, pf1.Value, _
pi.Value, pf2.Value, str)
If pd.Value = 0 Then
pf2.PivotItems(str).Visible = False
End If
Next r

End Sub
'==============================

For earlier versions, you could use the following code to hide the rows:
'==========================
Sub HideZeroRows()
'hide worksheet rows that contain all zeros
'by John Green
Dim rRow As Range

For Each rRow In ActiveSheet _
.PivotTables(1).DataBodyRange.Rows
If Application.Sum(rRow) = 0 Then
rRow.EntireRow.Hidden = True
Else
'DD --I added this to unhide
'any previously hidden rows
rRow.EntireRow.Hidden = False
End If
Next rRow
End Sub
'=========================