Date: prev next · Thread: first prev next last
2020 Archives by date, by thread · List index

I have amended the Help page with a note on the subject.

Em 21/08/2020 11:12, Steve (GMail) escreveu:

Many thanks for that insight.

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?
"For the majority of users, Calc does not provide multiple hierarchies
for a single field and so this option is normally grayed. If 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".



------ Original Message ------
From: "Kohei Yoshida" <>
To: "Steve (GMail)" <>
Cc: "Documentation Team" <>;
"LibreOffice Developers" <>
Sent: 21/08/2020 14:33:56
Subject: Re: [libreoffice-documentation] What is data hierarchy in pivot
table data field options?

Hi Steve and Celia,

On 21.08.2020 05:31, Steve (GMail) wrote:

Just for information, anything you attach to your email will be
stripped off again by this mailing list; we can't see your screenshot.

I'm assuming we are talking about the dialog that is described here?

Nevertheless I fully understand your question. Unfortunately I do not
recall ever seeing the Hierarchy drop-down on the Data Field Options
dialog in any state other than grayed. I don't know what circumstances
might lead to it becoming available - maybe one of the developers
could have a look at the code to see? (email copied to Developers
mailing list)

I provided my own explanation below.

What is data hierarchy in pivot tables?

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.

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:


then the pivot table will create a field for this Field1, initially
with no hierarchy i.e. the raw values will get shown.

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.

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.

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.

This is a question for concept: What is a data hierarchy in this
option dialog? And more, when/how do I use it?

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.

Having said this, here is a scenario where that option may become
active, but it needs a bit of an explanation.

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.

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.

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.

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.

Does this explanation help?


Olivier Hallot
LibreOffice Documentation Coordinator
Comunidade LibreOffice
Rio de Janeiro - Brasil - Local Time: UTC-03:00

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
Privacy Policy:


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.