Filter Pivot Fields and Inspect Detail Records in OpenOffice Calc

Filter a pivot summary to one region, then recover the records contributing to a numeric result. The important distinction is between double-clicking a category label and double-clicking an aggregate value: those actions have different documented outcomes.

Build a small audit fixture

This guide uses Apache OpenOffice Calc 4.1 desktop terminology. In a blank workbook enter the following in Sheet1.A1:E7. Keep G1:P30 empty for the pivot output. Save a clean ODS copy.

Row	A: Record	B: Region	C: Agent	D: Product	E: Amount
2	R01	East	Ada	Kit	120
3	R02	East	Ben	Kit	80
4	R03	West	Ada	Kit	90
5	R04	East	Ada	Pad	60
6	R05	West	Ben	Pad	50
7	R06	East	Ada	Kit	40

Select A1:E7 and choose Data > Pivot Table > Create, then Current selection. Put Product in Row Fields, Amount in Data Fields, and Region in Page Fields. Double-click the Amount field and explicitly choose Sum if necessary. Do not put Record in the layout: it remains available in the source for the audit.

Expand More, set Results to to $Sheet1.$G$1, and enable Enable drill to details. Retain totals so the overall sum is visible. Click OK. The complete summary should show Kit 330, Pad 110, and overall total 440. The exact output row positions depend on the displayed page/filter controls; find numeric results by their Product labels rather than assuming a cell address. For a slower introduction to the layout dialog, see pivot table basics.

Filter the pivot itself

Use the Region page-field drop-down above the result and choose East. Kit should now be 240, Pad 60, total 300. The other region has not been deleted. Return the drop-down to its all-items choice to restore 440, then select East again.

Do not filter the source worksheet and assume that the pivot inherits the visible rows. The official guide states that source AutoFilter or standard filtering does not limit the pivot analysis. The page field is also useful because the selected region remains visible beside the summary. Keep that control and the total together when presenting the result.

Inspect the contributing records

1. With East selected, double-click the numeric 240 for Kit, not the word Kit.
2. The documented data-value drill-down produces a new sheet containing the source records that contribute to that value. Locate columns by their headings and check the Record values.
3. Expect exactly R01, R02 and R06, with amounts 120, 80 and 40. Their sum is 240. R03 is also Kit but belongs to West, so it must not appear in this East-only reconciliation.
4. Return to Sheet1. Restore the all-items Region selection and double-click the numeric Kit result, 330. The new detail output should also contain R03 and total 330.

A detail sheet is an extracted audit result. Treat it as a snapshot, not an editable route back into the source. If the result differs, stop and check the pivot filter, source range, aggregation and clicked cell before drawing conclusions.

Refresh, growth and a deeper breakdown

Change E3 from 80 to 100. Select a cell in the pivot and use Data > Pivot Table > Refresh. With East selected, Kit should become 260 and the regional total 320; with all regions selected, Kit is 350 and total 460. Generate a fresh detail sheet to check R02=100. Label or remove obsolete audit snapshots so a previous 240 is not mistaken for the current result.

Restore E3 to 80 before the growth test. Select a cell in the original pivot at G1 and choose Data > Pivot Table > Refresh before appending anything. With East selected, verify Kit 240 and regional total 300; with all regions selected, verify Kit 330 and overall total 440. This refresh removes the previous change-test values from the original six-record summary. Now add R07, East, Ben, Kit, 20 in A8:E8. Select the enlarged A1:E8 source block and create a new pivot from Current selection, with the same Product/Amount/Region layout. Keep S1:AB30 empty and set Results to to $Sheet1.$S$1 so the old summary is not overwritten. Enable drill to details again. In the newly created pivot, select East and check Kit 260 and regional total 320. With all regions selected, check Kit 350 and overall total 460. Compare these with the refreshed original pivot: East Kit 240/total 300 and all-region Kit 330/total 440. The original still covers A1:E7; the new pivot covers A1:E8. Compare the old and new summaries explicitly; the official guide directs recreating the pivot when records are appended outside its original source area. A fixed A1:E7 source cannot include R07 merely because Refresh was clicked.

For the final category-level drill-down, return explicitly to the refreshed original six-record pivot at G1, whose source remains A1:E7 and whose restored E3 is 80. Select East in that original pivot and verify Kit 240/total 300 before double-clicking its Kit label instead of its number. Do not use the grown pivot at S1 for this check: it includes R07 and would give Ben 100 instead of 80. If prompted, choose Agent as the additional breakdown. For East, Ada contributes 160 and Ben 80. Data > Group and Outline > Hide Details hides that category breakdown. If the additional field persists in the layout, remove it there. This is separate from extracting individual source rows to a new sheet.

Recovery and limits

If double-click enters edit mode, check Enable drill to details in the pivot layout. If Amount is counted rather than summed, correct the data-field function and confirm all six Amount cells are numeric. Do not edit the generated summary cells to repair a discrepancy.

Save as ODS and reopen. Recheck the chosen Region, source bounds and totals, then refresh and extract a new detail sample. Use the original fixture or clean saved copy to restore the exercise after reopening. On macOS, use Control-click where a context menu is needed; this article otherwise uses the guide’s desktop menu names.

The official 4.1 Pivot Tables chapter, especially Page Fields, More Options, Drilling, Filtering and Updating, supports these procedures. This original fixture has mathematical checks but no captured native run or screenshot. Verify filter-sensitive drill output in the installed build before relying on it for an audit.