https://bugs.documentfoundation.org/show_bug.cgi?id=162417
--- Comment #17 from Robert Lacroix <mux...@hotmail.com> --- > > 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. > > That means that the refresh in Calc added the items to the Filter as > "checked" (not hidden, not filtered out), whereas Excel adds the items to > the Filter as "unchecked" (i.e. filtered out). Please correct me if I'm > wrong. Right, data for the new field value is not included in the "data" cell calculations and does not appear as a new row/column of the PT, except when all field values were already checked before refresh. In Excel, WHEN ALL VALUES ARE CHECKED, then Excel does the same thing as Calc for new values. But when at least 1 value is unchecked before refreshing, then Excel has the opposite behaviour. It adds new values 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. > > > So, you currently prefer that newly-added data items in the original data > table would be set as "shown" (not hidden) in the filter of the PT when the > PT is refreshed, as already done by Calc. No changes here. No. My comment is only about prompting when the PT will overwrite adjacent nonempty cells, which happens only when a PT expands. > > - 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). > > In your example, you added a new row of data, which impacts the Filter (with > the differences against Excel as described above, "shown" vs "hidden"). But > now you are mentioning other fields of the PT (which is not the same as > adding data). > > I am possibly misunderstanding, and I am also looking for an actual concise > reply to the questions I presented in comment 8: > > * If you want a non-changing resulting table, can't you just copy the > content of the PT to another simple table, in order to "disconnect" the > (pasted) result from the original data? Not really. Dozens of PTs use the same 7000-row source table, and each PT has different dimensions. I don't want to scroll through the list of field values of every PT to find the new values and uncheck them when I add new source data rows (or edit existing ones). Excel does it correctly, LO-Calc does not. In LO-Calc, the real workaround is that I MUST document the list of values which are supposed to be checked for each field, because I won't remember precisely which values are supposed to be checked, and LO-Calc always adds new values with checks. > * How Excel manages (or achieves) the suggested "locked" status? Automatically. In Excel the array of field VALUES is always extended with new values upon refresh. However the state of the CHECKBOX with new values depends on whether any of the preexisting values (before refresh) are unchecked. If all were checked, new values also have checks. If any are unchecked, then new values are not checked. > Again, I might be misunderstanding the real goal or the real meaning of this > "locked" expression – an expression that is so ambiguous that should be > avoided anyway. The goal is to avoid having the subset of checked values in a field change automatically when a particular subset is already selected. For example. A source table contains the list of everyone in a school and other columns of info. One column is the "position", say "student", "admin", and "teacher". The PT is set up with only "student" selected. Then a new source row is added with "position" of "librarian". LO-Calc transforms the PT from students-only to students+librarian, and we won't even notice a change of dimensions if the field is a page field. That's not good, we set it up already to be only students. Excel does the right thing and maintains it as students-only. Forget about the term locking, it is superfluous because Excel never changes the underlying SQL query if you think about it that way, only the user can change it. The page/column/row fields of the PT taken together define the key for rows in the source data. Checked items in these fields are key values to match in the query. Data fields are the summarized query result (sum, average, count, etc.). If all values of a field are checked, this means effectively "select ... where field_x=*" on the part of the key denoted by field_x. If any values of field_x are not checked, then the query includes individual checked values to matched "select ... where (field_x=value_a or field_x=value_b or ...)". The query is immutable (by Excel). Only the user can change the query, by changing checkboxes. -- You are receiving this mail because: You are on the CC list for the bug.
