Pivot charts in LibreOffice: Final part 3

It has been a while when I posted an update on pivot charts. In the mean time I finished what was planned and iterated through cycles of needed fixes and polish. In the mean time we branched off the code for LibreOffice 5.4 and the pivot chart implementation is part of that too. If you want to try it out, you can get the LibreOffice 5.4 pre-release on the download page.

Pivot chart field button actions

Last time I explained about the buttons, but I didn't explain what action is performed when we click on them. The buttons generally have a similar function as in pivot table - to show the pivot table layout and to apply filtering of data. The filtering in the pivot table opens a non-modal windows where you can choose the filtering. For pivot charts I wanted to reuse that, so when clicking on the field button, the request is send from the Chart component back to the Calc, where the same window is shown (shown in Figure 1).

Figure 1: Pivot chart field filter

Improvements to pivot chart buttons

In previous post, the pivot chart field buttons were still very basic. Now I improved them, so they show a down arrow, so they look more like they have a pop-up action attached to them. If there is some filtering applied, then the arrow turns blue (similar to the pivot table), so it is easier to see when a field has any filter applied. 

For page fields we also show what is filtered: when nothing is filtered "- all -" is shown, when some all filtered, then "- multiple -" is shown and when only one value is not filtered, then we show that value.

ODF support and compatibility

A pivot chart is useless if we can't save it to a file and later reopen. For this it was needed to extend the ODF format. Luckily, this was relatively easy to do, as the only thing needed is the name of the pivot table that a chart links to (I added "data-pilot-source" attribute to "chart:chart" element). Everything else is already present in the existing import/export code so no additional elements were needed to recreate the exact state that was present when the document was saved. 

A bit related is also copy and paste, which uses the ODF as an intermediate format (copy saves parts to the ODF format and paste loads the format) so things like copy/paste between documents works. A difference here is that we can copy the pivot chart and paste to a different (empty) document, which doesn't have the pivot table. In this case I had to make sure that a normal chart is pasted, which uses the table internal data and not the pivot table. The table internal data is always written with the chart object even if it is not used, just for situations like this (another one is also when we copy from Calc document and paste in Writer document).

Tests

It would be really hard for me to implement this properly without tests, as they cemented the behaviour and, if they failed, I knew that probably I made a mistake or I have took a wrong approach to solve the problem. First I added a import / export tests, which just used an existing document to get the data, pivot table and already existing pivot chart. The purpose of these are to test the ODF import and export code. 

Later, I added tests which programatically add data into a sheet and create a pivot table from scratch as a set-up, then create the pivot chart and test various pivot table layouts, and assert what we expect to see in a pivot chart. This approach is better as a document is not needed, and it demonstrates that a pivot chart can be made from scratch with the available API.

Final demo

Finally, I want to show the complete demo of the pivot chart feature:

You can find the video on YouTube at the following URL: https://youtu.be/txvL1UrsQCw

Credits

Many thanks to Nantes Métropole and Ville de Nantes for making this work possible.

Read more about Nantes deployment here.

Pivot charts in LibreOffice: Part 2

This time I'll present some necessary changes to make pivot charts actually useful and one unique feature that pivot charts have and normal charts don't - field buttons.

Pivot chart creation

If you watched the first video, you should notice that I showed the pivot chart was already created from the start. The reason for this was that the functionality to create a new pivot chart from the pivot table wasn't implemented yet. I have fixed this, so it is now possible to create a new pivot chart if you position the cursor on the pivot table, and select from the menu to create a new chart. The chart creation code will detect the pivot table and create a pivot chart instead of a normal chart.

Pivot chart wizard

When we want to create a new chart, we first get the chart creation wizard, where we can select the chart type, define the ranges for labels and data, define data series ranges, and add some additional chart elements like title, subtitle,...

For the pivot chart we get a similar wizard now, where we can select the chart type and additional data. The wizard step to add data ranges and the step to define the data series is however disabled as these steps are not needed when we get the data from the pivot table.

Pivot chart creation wizard

Pivot chart buttons

This time the biggest change are the pivot chart buttons, which are unique to pivot charts (normal charts don't have them). The purpose of the buttons is to show the layout of the pivot table, so it shows the pivot table fields. On the top it shows the buttons that represent the page fields (if present) and the data fields of the pivot table. At the bottom it shows buttons for row fields next to each other, and in the legend it show the buttons from column fields stacked.

Field buttons in a pivot chart

As they are buttons, there is an action performed when clicking on them, but this is not implemented yet and I'll described this next time in more detail.

From the implementation point of view, the most challenging thing with the buttons was to position them correctly inside the chart as they are part of the chart structure, and to position everything else accordingly (and not breaking the normal charts in the process).

Demo

This is an updated video of the current state of pivot charts:

You can find the video on YouTube at the following URL: https://www.youtube.com/watch?v=hzl8N9-wpc4

Credits

Again, many thanks to Nantes Métropole and Ville de Nantes for making this work possible.

Read more about Nantes deployment here.

To be continued...

Pivot charts in LibreOffice: Part 1

About

Pivot tables are a powerful tool to reorganise, manipulate and summarise the data set in spreadsheets to get the valuable information from it. To get a quick visual representation of the information, pivot charts can be used. A pivot chart can be created from the output of the pivot tables, and if the pivot table gets changed, so does the pivot chart.

Support for pivot tables in LibreOffice is available for a long time, but there was no support for pivot charts until now. For the past week I was working on pivot charts in a feature branch (feature/pivotcharts) and I got to a first milestone. Pivot charts will be released in LibreOffice 5.4.

Pivot chart data provider

From development point of view, pivot charts are just like normal charts but with a different data provider (source of data), so this was the task with which I started. Normal charts use a data provider which is based around reading from cell ranges, but for pivot charts I created a new data provider, which reads the output data from the pivot table and prepares it for the chart. The data columns are mapped to data series and the data rows become the number of data series in chart (See Figure 1).

Figure1: Pivot table to pivot chart data mapping

Now what is left is naming of each axis and data series in chart. The y-axis categories are mapped to row field names in the pivot table and the data series names, which are shown in the chart are combined names of all column field names of the pivot table.

Each data point and row or column field name also has an associated number format, which needs to be assign to chart data, otherwise the the number format would not the values correctly as in pivot table (this is especially important with date and time).

Updating a pivot chart

Once I managed to do the mapping correctly, the pivot chart showed up as expected, but the pivot chart wasn't updated when I update the pivot table. So to solve this, I had to implement a listener of pivot table updates in the pivot chart data provider, and for every update send the signal to chart to update the data again (which it gets from the pivot chart data provider). The whole update procedure sounds like a ping-pong play between components, but it works quite well.

Demo

In the following video you can see the current status of development:

Credits

One of the real privileges here is working on LibreOffice for a Collabora Productivity customer who funds significant feature work. Many thanks to Nantes Métropole and Ville de Nantes for their investment here, and making this feature available to all LibreOffice users. You can read more about Nantes deployment here.

To be continued...