[libreoffice-documentation] What is data hierarchy in pivot table data field options?

[Kohei Yoshida]

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?

https://help.libreoffice.org/7.0/en-US/text/scalc/01/12090106.html

> 
> 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:

Field1
1
2
3
4
5
6
7
8
9
10

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?

Kohei