Named range as data source in DataPilot table

I have hinted in my previous post that you can now use a named range as the data source of a DataPilot table, but you couldn’t create a new DataPilot table with a named range as the source.

Well, now you can.

I tried to come up with a clever way to add this functionality, but ended up with just another radio button in the existing source selection dialog (the dialog that pops up when you select Data – DataPilot – Start without an existing DataPilot table).

Here is a screenshot of the new dialog as evidence:
calc-dp-named-range-source

This functionality is currently available on the master branch of LibreOffice. For those of you who can build LibreOffice directly from the repository, go check it out!

For those of you who would rather wait for a released version, this will be available in 3.4 – the next minor release. Refer to this page for more detailed release plan of the upcoming versions of LibreOffice.

FOSDEM 2011 slide & latest updates

I’ve just uploaded the slide for my talk during FOSDEM 2011 here. It was very nice to be able to talk about our somewhat ambitious plan to bring LibreOffice Calc to the next level. Also, I regret that I haven’t been able to blog about what’s been going on lately; lots of time spent on writing, reviewing code, fixing bugs and integrating patches, and sadly little time is left on writing blogs.

Having said all that, let me talk about a few things that are new on the master branch (since I’m already in the writing mode).

The first one is the new move/copy sheet dialog

new-copy-move-sheet-dialog

which is based on the design suggestion from Christoph Noak and coded by Joost Eekhoorn. The idea is to provide a quick way to rename a copied sheet, and also to make the layout more ergonomic and more appropriate to modern HIG. There are still some minor issues that we have yet to work out, but this is a step in the right direction.

The second one is related to DataPilot. In fact there are two new enhancements landed on master with regard to DataPilot.

The first enhancement is the support for unlimited number of fields. Previously, DataPilot could only support up to 8 fields in each dimension (page, column, row and data). But now you can define as many fields in each dimension as you desire, provided that you have enough memory and CPU cycles to handle extra load.

calc-dp-unlimited-fields

The second DataPilot enhancement is the support for named range as the data source. Now, you can use a named range as the data source of a DataPilot table, instead of raw range reference. This has the advantage that, when your source range grows, you can simply update the named range and refresh the DataPilot table.

calc-dp-named-range-source

However, I have not yet added a way to create a new DataPilot table with a named range as data source. I will work on that sometime soon, hopefully in time for our 3.4 release.

Other than that, I’ve fixed quite a number of bugs and added performance enhancements particularly with regard to external reference handling. Still, there are lots of other tasks I need to do on master before we hit the 3.4 release. Stay tuned for more updates.

Custom sort in DataPilot

Just checked in this piece to the master branch of the Go-OO repository, to support sorting of DataPilot’s field members using custom sort lists. I’ve extended the popup window I wrote for the hide field members functionality to provide this additional sorting functionality. The result is the following popup window:

In the upper half of the window I’ve added a menu-like control, with the custom sort lists being provided in the submenu. The UI is fully functional, but still a bit rough around the edges. The custom sort list submenu, in particular, may need some additional work to handle a large set of custom sort lists, a sort list that is very long, or stuff like that. But as long as your sort list is in modest size, it should work just fine.

This feature didn’t make it in to 3.1 since we are in a stabilization phase for 3.1. But as soon as we branch master for the stable 3.1 branch, I will enable this feature in the default build in the master branch.

Some minor enhancement with DataPilot

Just checked into the master branch of go-oo repository (oh BTW we just switched our repository from svn on gnome.org to git on freedesktop.org) is a minor enhancement in DataPilot, to allow the users to filter results by field members directly from the field buttons in the table output. The following screenshot will tell you the change I just made:

The new field buttons also provide visual feedback on fields that are filtered; the same way the autofilter buttons already do. This way it’s visually obvious to the users which fields are currently filtered.

I call this a “minor” enhancement, simply because it doesn’t provide a new functionality per se; the same filtering functionality was already present but hidden deep beneath multiple layers of dialogs. You needed to go from the datapilot layout dialog (the main dialog), open up the field dialog for the field you want to filter results by, and then finally the field options dialog to get to the Hide items list in that dialog. This enhancement will push that functionality up-front and make it more accessible to the users, along with some visual feedback.

Anyway, this one made it into 3.1, so testing and feedback is greatly appreciated. :-)

Drilling down data in Data Pilot

Ok. Here is the feature I’ve been working on while I was in Barcelona, and I think it’s got to a point where I can show some progress to the world.

This feature is about supporting a drill-down of a data field cell in Calc’s Data Pilot feature. Excel already has an equivalent feature in its Pivot Table functionality, and what I’m doing initially came out of a request from current and former Excel users who rely on that feature.

Here is what this feature does. Let’s say you have a data pilot box somewhere in your Calc document like the one below.

data pilot view

You then decide to double-click on one cell within the data field to see the original data rows that comprise that cell. Calc then inserts a new sheet, containing the appropriate subset of the original data table (see below).

drill down data on new sheet

At this point, it works pretty reliably for me, but that’s not to say that it’s ready to be integrated into the main branch. There are still a few things to take care of.

  • The drill down sheet is not decorated with auto formatting. Excel formats the drill down sheet nicely, but currently it’s not implemented. But I have to wonder whether that is really essential (I think not).
  • Currently this feature works only on internal data source i.e. data source within the same document. But Calc’s Data Pilot supports two other data source backends (registered database and data pilot UNO component), and I need to add support for them too.

Once that is done, I’ll be happy to see this piece integrated upstream (under JCA, of course), since this feature is also requested in Issue 57030. While I’m in the data pilot code, it may be a good idea to review other data pilot related issues (if there is any) and tackle them at the same time. If you know of any hot data pilot related issues, please let me know.