Hiding certain values in a single column within a 2010 pivot table.

[Darren Bartrup]

Hi all,

I'm hoping this is harder to explain than it is to implement - pivot tables aren't my strongest point.

On an Excel 2010 pivot table I'd like to show the subtotal and grand total of a column formatted as [h]:mm:ss, but I want to hide the values making up those totals while keeping the actual row visible.

The data source is an Access query exported to Excel.

My data table consists of five columns:

• Employee_Name - an employee works on different sections through the day, so the name is repeated for for each section.
• Section_Name - each section will appear a maximum of once per employee.
• Shift_Duration - a total for the employee for the day, but appears on each row that the Employee_Name appears on.
• Allocation - per section.
• Complete - per section.

The Employee_Name and Section_Name are row labels (in that order).
Allocation and Complete are summed in the values.

The Shift_Duration is per employee and not per section so I'd like to show this only on the employee level where the subtotals for the different sections are shown.

At the moment I put the Max of Shift_Duration in the values and it appears correctly in the subtotals, but I'd like to hide it in the values part of the pivot table, leaving just the allocated and completed for each section showing.

I tried putting in a dummy section to hold the duration and took it off of the other section rows in the raw data - this made it disappear from the sections but it vanished from the subtotal when I hid the <blank> section row.

I could use a VLOOKUP to return the time from the raw data, but then it won't be included in the pivot table and will cause more work for me.

To make it easier on myself I'm also building the spreadsheet from within Access before turning the final report into HTML and placing it in an email (until the rest of the department upgrades from 2003).

    '''''''''''''''''''''''    
    'Add the pivot table. '
    '''''''''''''''''''''''
    With oXLWrkSht_TMP
        ''''''''''''''''''''''''''''''''''''
        'SourceType 1 = xlDatabase         '
        'Version 4 = xlPivotTableVersion14 '
        ''''''''''''''''''''''''''''''''''''
        oXLWrkBk.PivotCaches.Create( _
            SourceType:=1, _
            SourceData:=.Range(.Cells(1, 1), oXLLastCell_TMP), _
            Version:=4).CreatePivotTable _
            TableDestination:=oXLWrkSht_PVT.Cells(1, 1), _
            TableName:="TeamBreakDown", _
            DefaultVersion:=4
    End With
    With oXLWrkSht_PVT
        With .PivotTables("TeamBreakDown")
            With .PivotFields("Employee_Name")
                .Orientation = 1 'xlRowField
                .Position = 1
            End With
            With .PivotFields("Section_Name")
                .Orientation = 1 'xlRowField
                .Position = 2
            End With
            .AddDataField .PivotFields("Allocated"), "Allocated ", -4157 'xlSum
            .AddDataField .PivotFields("Complete"), "Complete ", -4157 'xlSum

'Duration will be added here as soon as I can work it out.


        End With
    
    End With

Thanks in advance for any input.</blank>