[Bug 162417] PIVOTTABLE: Warning does not show range when overwriting destination range contents

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=162417

--- Comment #13 from Robert Lacroix <muxlab at hotmail.com> ---
Your example does not make use of field value selections. Try this example. For
simplicity of demonstration we allow LO-Calc to create the pivot table in a new
sheet.

XACTION   AMOUNT
deposit       30
deposit        5
withdraw      -2
billpay      -10
deposit        4
endofdata      0

Step 1: create a LO-Calc spreadsheet with the above table of information

1a) highlight the above 7 rows in this Bugzilla comment
1b) press Ctrl-C to copy the example to the clipboard
1c) start LO-Calc; a blank spreadsheet appears with active cell Sheet1.A1
1d) press Ctl-V to paste the data into the spreadsheet; Text Import dialog box
appears
1e) press Enter (or click OK); the example appears in LO-Calc at Sheet1.A1:B7

- The last row "endofdata" is included so that we can easily extend the source
range with new data by using row insertions, avoiding the need to interact with
the Pivot Table Layout dialog each time new source data is added.

Step 2: create the pivot table

2a) select range A1:B7 (pressing Ctrl-Home Ctrl-Shift-End does this)
2b) choose menu Insert > Pivot Table...
2c) press Enter (or click OK) to use the current selection; Pivot Table Layout
dialog box appears
2d) drag XACTION from Available Fields to Row Fields
2e) drag AMOUNT from Available Fields to Data Fields; SUM - AMOUNT appears
there
2f) click OK

- The pivot table is created at "Pivot Table_Sheet1_1".A1:B6
- The Total Result value in B4 is 27
- It includes rows for "billpay", "deposit", "endofdata" and "withdraw"

Step 3: remove the "billpay" and "endofdata" rows from the XACTION field of the
pivot table

3a) expand the field XACTION dropdown in "Pivot Table_Sheet1_1".A1
3b) uncheck "billpay" and "endofdata"
3c) click OK

- The pivot table shrinks by two rows
- It includes rows for deposit and withdraw
- The Total Result value in B4 becomes 37
- The XACTION field heading in A1 has a dot to show at least 1 value is
unchecked

Step 4: extend the source data with new data

4a) go to Sheet1
4b) insert a row above row 7 (right-click row 7's selector, select Insert Rows
Above)
4c) enter interest in Sheet1.A7
4d) enter 0.1 in Sheet1.B7

Step 5: refresh the pivot table

5a) go to "Pivot Table_Sheet1_1"
5b) right-click anywhere in the pivot table; the popup menu appears
5c) click Refresh

In LO-CALC:
- The pivot table expands to A1:B5
- Total Result value in B5 becomes 37.1
- A new row appears in the pivot table containing "interest".
- The XACTION field dropdown shows the new value "interest" as checked.

In Excel:
- The pivot table does not change dimensions
- Total Result value in B5 remains 37
- The XACTION field dropdown shows the new value "interest" as unchecked.

- LO-Calc's current behaviour when a pivot table's dimensions change is better
than Excel's and should remain as-is, as you describe.

- I want Excel's behaviour for addition of new field values when some field
values are already unchecked before the pivot table is refreshed. This should
apply to row fields and column fields, and also to page fields for consistency
(although new field values do not affect table dimensions).

- By the way, I just found an obscure bug in LO-Calc where now inserting
another source data row with the value "interest" (including double-quotes) and
refreshing confuses the XACTION dropdown selector, so that neither version can
be removed from the pivot table by unchecking it, until the Pivot Table Layout
dialog box is opened and OK clicked.

-- 
You are receiving this mail because:
You are on the CC list for the bug.