<html><head> <style id="css_styles" type="text/css"></style></head><body class="plain"><div>Kohei,</div><div><br /></div><div>Many thanks for that insight.</div><div><br /></div><div>In terms of the Calc Guide, I'm not keen to fully explain a feature that doesn't exist without using an extension! Would you be happy with the following note?</div><div><ul style="list-style-type: disc;"><li>"For the majority of users, Calc does not provide multiple hierarchies for a single field and so this option is normally grayed. If<span> you use a pivot table data source extension, that extension could define multiple hierarchies for some fields and then the option could become available. See the documentation supplied with that extension for more details".</span></li></ul></div><div><span><br /></span></div><div><span>Regards,</span></div><div><span><br />Steve</span></div><div><span> </span></div> <div><br /></div> <div>------ Original Message ------</div> <div>From: "Kohei Yoshida" <<a href="mailto:kohei@libreoffice.org">kohei@libreoffice.org</a>></div> <div>To: "Steve (GMail)" <<a href="mailto:stevemfanning.wh@gmail.com">stevemfanning.wh@gmail.com</a>></div> <div>Cc: "Documentation Team" <<a href="mailto:Documentation@global.libreoffice.org">Documentation@global.libreoffice.org</a>>; "LibreOffice Developers" <<a href="mailto:libreoffice@lists.freedesktop.org">libreoffice@lists.freedesktop.org</a>></div> <div>Sent: 21/08/2020 14:33:56</div> <div>Subject: Re: [libreoffice-documentation] What is data hierarchy in pivot table data field options?</div><div><br /></div> <div id="xe7fb12c5b3d9401"><blockquote type="cite" class="cite2"> <div class="plain_line">Hi Steve and Celia,</div> <div class="plain_line"> </div> <div class="plain_line">On 21.08.2020 05:31, Steve (GMail) wrote:</div> <div class="plain_line"> </div> <blockquote type="cite" class="cite2"> <div class="plain_line">Just for information, anything you attach to your email will be</div> <div class="plain_line">stripped off again by this mailing list; we can't see your screenshot.</div> </blockquote> <div class="plain_line"> </div> <div class="plain_line"> </div> <div class="plain_line">I'm assuming we are talking about the dialog that is described here?</div> <div class="plain_line"> </div> <div class="plain_line"><a href="https://help.libreoffice.org/7.0/en-US/text/scalc/01/12090106.html">https://help.libreoffice.org/7.0/en-US/text/scalc/01/12090106.html</a></div> <div class="plain_line"> </div> <blockquote type="cite" class="cite2"> <div class="plain_line"> </div> <div class="plain_line">Nevertheless I fully understand your question. Unfortunately I do not</div> <div class="plain_line">recall ever seeing the Hierarchy drop-down on the Data Field Options</div> <div class="plain_line">dialog in any state other than grayed. I don't know what circumstances</div> <div class="plain_line">might lead to it becoming available - maybe one of the developers</div> <div class="plain_line">could have a look at the code to see? (email copied to Developers</div> <div class="plain_line">mailing list)</div> </blockquote> <div class="plain_line"> </div> <div class="plain_line">I provided my own explanation below.</div> <div class="plain_line"> </div> <blockquote type="cite" class="cite2"> <blockquote type="cite" class="cite"> <div class="plain_line">What is data hierarchy in pivot tables?</div> </blockquote> </blockquote> <div class="plain_line"> </div> <div class="plain_line">Data hierarchy in a pivot table is an additional layer of grouping applied to the source values of a field. I will explain this concept as follows.</div> <div class="plain_line"> </div> <div class="plain_line">First, when you initially create a pivot table with source data within your Calc document, the table output is created with however many fields you may have in the source data. Let's say you have the following field values:</div> <div class="plain_line"> </div> <div class="plain_line">Field1</div> <div class="plain_line">1</div> <div class="plain_line">2</div> <div class="plain_line">3</div> <div class="plain_line">4</div> <div class="plain_line">5</div> <div class="plain_line">6</div> <div class="plain_line">7</div> <div class="plain_line">8</div> <div class="plain_line">9</div> <div class="plain_line">10</div> <div class="plain_line"> </div> <div class="plain_line">then the pivot table will create a field for this Field1, initially with no hierarchy i.e. the raw values will get shown.</div> <div class="plain_line"> </div> <div class="plain_line">You can assign grouping to this field, by moving the cell cursor to where this field data is displayed within pivot table, and either press F12, or select Data -> Group and Outline -> Group from the menu. For this set of values, you can only create a numeric range group, to group the values in some specific intervals. If you have a set of date values, you can also apply date-based grouping i.e. years, quarter, months, days, and so on.</div> <div class="plain_line"> </div> <div class="plain_line">This grouping is what is referred to as hierarchy. The two are interchangeable, but in the source code, this is referred to as hierarchy pretty much exclusively.</div> <div class="plain_line"> </div> <div class="plain_line">Under normal circumstances, each field can have only one hierarchy, or none at all, as far as I know. Someone please correct me please if this is not accurate, but I'm certain this is the case.</div> <div class="plain_line"> </div> <blockquote type="cite" class="cite2"> <blockquote type="cite" class="cite"> <div class="plain_line">This is a question for concept: What is a data hierarchy in this option dialog? And more, when/how do I use it?</div> </blockquote> </blockquote> <div class="plain_line"> </div> <div class="plain_line">As Steve said, this option is always grayed, because it will get active only when the field has more than one hierarchies, which, as I said above, almost never happens under normal circumstances.</div> <div class="plain_line"> </div> <div class="plain_line">Having said this, here is a scenario where that option may become active, but it needs a bit of an explanation.</div> <div class="plain_line"> </div> <div class="plain_line">Calc's pivot table implementation is split into two parts. You can refer to them as the front end and back end portions. Some people may hate these terms, but I don't care. The back-end part takes care of defining the structure of the source data, in terms of fields (or dimensions as they are referred to in the code) and the structure of each field. Each field consists of three layers - first layer is hierarchies, the second layer is levels, and the last layer is members. A hierarchy is the type of grouping applied (i.e. value range, years, quarters etc), a level is a specific bucket within the defined hierarchy i.e. range 1-4, year 1980, month of August etc. A member is an original value.</div> <div class="plain_line"> </div> <div class="plain_line">The front-end part receives this structure provided by the back-end, and displays it in sheets, or recently in charts, and/or provides various dialogs for tweaking the parameters.</div> <div class="plain_line"> </div> <div class="plain_line">The back-end part is implemented as a UNO component, and in theory it can be swapped with one implemented by an extension. But when your data source is Calc's own sheet, you are using Calc's own back-end implementation, and this one does not allow multiple hierarchies in a field. This is why that hierarchy option is always grayed out.</div> <div class="plain_line"> </div> <div class="plain_line">Because the UNO API itself allows each field (or dimension) to have multiple hierarchies to be defined, in theory if you use an pivot table data source extension, that extension can define multiple hierarchies for some fields, and then that option will become active. Having said that, I have never seen any pivot table extension in existence, so I have never seen it active myself. At one point I almost wrote a pivot table data source extension myself as an experiment, but I never got to finish it up to see the light of day.</div> <div class="plain_line"> </div> <div class="plain_line">Does this explanation help?</div> <div class="plain_line"> </div> <div class="plain_line">Kohei</div> </blockquote></div> </body></html>