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.

14 thoughts on “Drilling down data in Data Pilot”

  1. Thanks Kohei for working on such issues that need some love. Here are the points I would consider as the 3 most annoying:

    – issue 23658 – Be able to change the data range once the datapilot has been created ()

    – issue 50886 – Datapilot defaults to the current worksheet, instead of a new worksheet (an easy one to implement)

    – Formatting of the datapilot: example 1, if you sum cells with euros, the resulting cells should display euros. example 2: if for the data field>displayed value, you ask for %, then the resulting cells should be formatted as percents. (some issue numbers: 44422, 32303, )

    – Manual sorting (32307) and sorting using “user defined sort lists” (don’t know if the issue already exists)

    A last one nice, but maybe less important:

    – issue 72304 – DataPilot : sorting by dataField

    I hope this helps.

    Best regards,
    Pierre-Andre

  2. Thanks guys, for bringing those issues numbers to my attention. :-)

    I can’t guarantee you that I will nail down all of those listed issues, but I’ll keep a close eye on these in case they can be easily addressed while working on the drill-down feature.

    The data pilot features still needs a lot of work, so let’s get to it!

  3. Seeing your notes on Calc and datapilot prompted me to enquire about issues I have with an otherwise superb product.

    I am a basic user with a little extra knowledge which is possibly a most dangerous combination!

    Is it possible to bring in an Excel formating feature. Column contents – size autofit. It would save me significant time.

    Formating in datapilot is frustrating. For example apart from text I use Dates, Week No, Year and also numbers to 2 decimal places. Change one and all change!

    I have tried saving my layout as a template but clicking on some cells can totally upset the setting out.

    Any assistance will be very much appreciated.

    If I have come to the wrong place please be kind enough to point me in the right direction.

    Thank you

    Kind regards

    Derek Ednie

  4. Hi Derek,

    One thing you can do is to see if the problem you experience has already been filed either as a bug or a feature request. The following page

    http://qa.openoffice.org/issues/query.cgi

    lets you query the bug database for the OpenOffice.org (OO.o) project. If you can’t find any existing bug, then you can file a new one, where you describe your problem, perhaps with an example document to demonstrate the problem, then that will get assigned to an appropriate developer.

    Here is the page to enter a new bug (or issue, whichever you prefer).

    http://qa.openoffice.org/issues/enter_bug.cgi

    I agree that the Data Pilot feature in Calc still has some problems. I can’t make any promises, but I’ll see if I can invest some time looking into some of them.

    All the best,
    Kohei

  5. This is exactly what I repquire to satisfy a large customer I have. They are are heavey pivot table users and therefore wqill be heavey data pilot users. They won’t deploy until this feature is in place.

    Can you give me any indiaction as to when this will be ready

    Kind Regards

    Michael Johnson

  6. Hi Michael,

    This feature will definitely appear in the go-oo version of OO.o first [1]. As for the time-line, I would probably say by the end of this year, tentatively speaking (subject to change without notice, of course ;-). As for the upstreaming schedule, I can’t really say as I don’t have the authority to make that kind of decision. Hopefully someone from Sun can answer that question.

    [1] http://go-oo.org/

  7. Thanks for your reply. I have to have a work around now and I was thinking of building something simple using the API. Is this possible. Is the data pilot stuff held in cell properties or can I get to it anywhere else.

    For example click on a cell then go to a button press it and it opens a new sheet for every componenet of that cell. Is this possible through the API?

    Kind Regards

    Michael

  8. The data pilot data is independent of cell properties, so it’s entirely a separate entity as far as where the data are stored. But what you are saying in your second paragraph is possible. In fact, I’m now working on exposing this new functionality via UNO API to precisely make that kind of operation possible.

  9. Ahh, I take that back. The UNO API I’m working on is for something else (UNO component data pilot), and it was not for programmatically inserting a new sheet with the data component for the data field cell. That may be something that will be worked on after this functionality is made available in the UI.

    But you’re right. We eventually need to make it available via UNO API.

  10. Can you explain what it is you are working on and if I can help. I am not expecting to open a new sheet. I just want to expose the data that makes up the cell they have alighted upon

    What do you think

    Kind Reagrds

    Michael

  11. Michael,

    What I’m working on is adding a new UNO API for the backend data pilot provider to be able to provide the constituent rows that make up a given data field cell when requested (this is used only internally). And if I understand correctly, you want a front end UNO API to pull the constituent rows by specifying the cell position within a data pilot box. I could perhaps find a way to add a API that, given a cell position, returns the constituent rows to the caller.

    Do I understand your need correctly?

    Kohei

Comments are closed.