Pivot grid for "live" data

[frbo]

I am currently evaluating your pivot grid implementation. In our case the pivot grid will be used to display "live" data: the underlying table model will have several updates every second as a result of external events and the grid should reflect those changes.

I tried to get this to work using a combination of 'dataTableUpdated()', 'bothHeadersUpdated()' and 'calculate()' methods, but I encountered a few problems:

- A 'dataTableUpdated()' call is not enough if a value for a filter or column/row field changes. I have to figure out myself if I need to call the 'calculate' method. This is doable, but it would be nice if the pivot grid has better support for this (do a 'diff' for changed rows in the underlying table model?)

- The current selection in the data table is lost on each 'dataTableUpdated' call. This is especially annoying when trying to navigate the grid using the keyboard (focus jumps back to top-left corner for every update).

- The collapsed/expanded state of elements in the header gets lost when doing a 'bothHeadersUpdated()' or 'calculate' call.

- If you open the popup on a row field to filter values, then it will disappear on a 'calculate()' call. This makes it impossible to filter row values when the grid has to be recalculated often. This does not happen on the filter for the column fields!

Are there any plans to add more support for data changes to the pivot table? Or are there easy workarounds already available?

Thanks,
Frank

[JIDE Support]

It is indeed very challenge in order to support "live" data in pivot table. Microsoft Excel doesn't even do it in their PivotTable. Currently we ask user to call the right method when the data is changed. So you are on the right track. We will also look at your findings below and decide what to do.

For selection lost, you can wrap dataTableUpdated call with TableUtils.saveSelection and loadSelection.

To keep the expansion state is not easy as the flag is saved on an object that is recreated when bothHeadersUpdated is called. But I will try to see if we can store it first and set it back later.

In short, we will keep this requirement in mind and do as much as we can to support it.

Thanks,

[grmo]

Is there any way to determine if a popup is currently open? If so, I could at least not call calculate and fieldsUpdated whilst this is the case, which would enable the popups to be used.

[JIDE Support]

Sorry but which popup do you refer to?

Thanks,

[grmo]

The filter popups, eg. if you click on the arrow next to a column header, next to a row header, or next to a filter.

As soon as I call fieldsUpdated, the popup disappears. If I receive an update every second, say, and call fieldsUpdated each time, then I can never use these popups! Hence, as a user of the UI, I would not be able to filter my pivot table. However if I can determine if such a popup has been displayed, I could choose not to call fieldsUpdated and hence let the user of the UI use the filters.

Further, I also notice that if the user is re-sizing a column or row when fieldsUpdated is called, the re-sizing is stopped and the user has to let go of the mouse and start again. I don't suppose there is any way around this issue?

[JIDE Support]

fieldUpdated will recreate the field box which is the parent of the filter popup. That's why the popup disappears. Do you really need to fieldUpdated which is an expensive call. If just data changes, PivotTablePane#dataTableUpdated could work. If data change caused the row header or column header change, call rowHeaderUpdated or columnFieldsUpdated or bothHeadersUpdated. fieldUpdated should only be called when field is moved to a different area.

The width thing should be fixed already in the latest release. If not, please let me know.

Thanks,

[grmo]

In general it is difficult to determine whether a call to fieldUpdated is required. Unless I am mistaken, my code would have to work out how the data change affects the current configuration of the pivot table, eg.

* whether a new data item creates a new row/column (search for value of rows/columns/filters for new item amongst all existing values for current rows/columns/filters)

* whether an updated data item moves data from one cell to another (check whether the old and new value for current rows/columns/filters is different)

Is that correct?

It's not clear to me when I would want to call (row/column/both)Header(s)Updated -- when you say "if the data change caused the row header or column header change", in what way can a data change cause a row or column header to change? Do you mean when the data change creates or removes a column/row? Or something else?


Regardless there will be times when I have to call fieldsUpdated, and at this point the user will have their popup disappear unless I can delay all updates till the user is finished using the popup. So I return to my original question:

Is there any way to determine whether a popup is currently visible?

[JIDE Support]

fieldUpdated is not a common thing. It is only needed fields are moved btween different areas. It happens either user dragn-drop or you write code to do it. If you just have data updated, call either (row/column/both)Header(s)Updated. If you are not sure, just call bothHeadersUpdated. This call will not make filter popup disappear.

Thanks,

[grmo]

When I try this, calling bothHeadersUpdated will make a pop up for a row filter disappear (note this does make a pop up for a column filter disappear).

Further, I find that bothHeadersUpdated does not display the data correctly if the data changes such that it now contributes to a different row/column. For example, if you show profit from sales in a pivot table of product against store, and you change which store a particular sale was made in, its contribution to profit should move from the column for the old store to the column for the new store, but that is not what I see if I call bothHeadersUpdated (and/or rowHeaderUpdated and/or columnHeaderUpdated). Instead, the profit figures do not change, and if I then increase the profit value of this moved sale, the increase appears under the old store (ie. as if it had not moved store). If I then remove the data field from the pivot table, and then re-add it, the table displays the data correctly.

However, calling columnFieldsUpdated and rowFieldsUpdated does display the data correctly, but the row filter/column filter pop ups again disappear.

I am using version 2.1.2 (downloaded but a few days ago).

[JIDE Support]

Did you call this method when your data changed?
You have to call this whenever your data is changed.

Code: Select all

pivotDataModel.calculate();


Thanks,

[grmo]

[I hadn't read your previous post before I posted this!]

Actually I've just found that with the example in my last post, if I call calculate() as well as bothHeadersUpdated(), then the profit figures do get updated correctly in this case. However, the row pop up still disappears (though not the column pop up nor the filter field pop up!).

If I call columnHeaderUpdated() instead of bothHeadersUpdated() the column pop up does disappear, which I find very odd, though the row pop up does not disappear, which is more understandable.

Something odd is going on here. Can you reproduce this behaviour? (Specifically, calling bothHeadersUpdated() causes a row pop up to disappear.)

[JIDE Support]

I finally figured out what's wrong. It is hidden not because the field box is recreated but because the field box is moved (JidePopup will be hidden if it's owner is moved, by default). I will fix it so that even if the popup will move with the field box.

Thanks,

[grmo]

In the example I was trying, the data change makes a new column and does not alter the row position of the data, yet calling bothHeadersUpdated() causes the row popup to disappear, not the column popup. I don't fully understand your explanation of course as it refers to the implementation, but I wasn't sure it sounded correct since my example shows no row "movement" at all, but does show column "movement" (ie. a new column is added), yet it's the row popup that disappears, not the column popup.

If you have found the problem I'm seeing, how long would you expect it to be before a fix is released?

[grmo]

I have found another issue: If I drag and drop a row or column field (say "product") onto a field (say "sales") in the field list (so that a box appears around "sales"), the field "product" is removed from the pivot table, as expected, but does not appear in the field list. If I then drag and drop another field elsewhere, the missing field reappears in the field list.

Clearly the workaround is to drag and drop it elsewhere (anywhere in the pivot table seems to suffice, though intuitively I thought I had to drop it into the field list), but it seems quite feasible that someone would accidentally drop it onto a field in the field list (I've done it several times now).

Can you reproduce this behaviour?

[grmo]

I have another(!) issue/question: When I persist layout information using PivotTablePersistence, it appears not to persist which values have been ticked in the filter popups (ie. the filter settings). Is that correct?

[JIDE Support]

You could give a test patch at http://www.jidesoft.com/downloads/jide.2.1.2.01.zip (or http://www.jidesoft.com/evaluation/jide ... 1.eval.zip if you are still evaluating) a try which should fix the filter popup disappearing problem.

I have found another issue: If I drag and drop a row or column field (say "product") onto a field (say "sales") in the field list ...

I couldn't reproduce this problem. Can you reproduce it it in our demo?

I have another(!) issue/question: When I persist layout information using PivotTablePersistence, it appears not to persist which values have been ticked in the filter popups (ie. the filter settings). Is that correct?

We don't persist the possible values from the filter to the layout. Saving those information will explode the xml file as we have to say each value.

Thanks,

[grmo]

You could give a test patch at http://www.jidesoft.com/downloads/jide.2.1.2.01.zip (or http://www.jidesoft.com/evaluation/jide ... 1.eval.zip if you are still evaluating) a try which should fix the filter popup disappearing problem.

Yes, that fixes it, thanks!

I have found another issue: If I drag and drop a row or column field (say "product") onto a field (say "sales") in the field list ...

I couldn't reproduce this problem. Can you reproduce it it in our demo?

Yes, I could. Start the Pivot Grid Demo. Drag "Product" off the pivot table and onto the field "Sales2" (eg. to be sure, place your mouse pointer on the "e" of "Sales2" when you let go of the mouse button, though you don't need to be that exact in my experience). I then see "Product" disappear. If I then drag and drop another field, "Product" reappears in the field list.

For reference, I'm running Fedora Core 6 Linux with JDK 1.5.0_10.sun.

[grmo]

Further, I also notice that if the user is re-sizing a column or row when fieldsUpdated is called, the re-sizing is stopped and the user has to let go of the mouse and start again. I don't suppose there is any way around this issue?

The width thing should be fixed already in the latest release. If not, please let me know.

I still observe this issue, even with the patched release 2.1.2.01, and even if I call bothHeadersUpdated instead of fieldsUpdated.

[JIDE Support]

I fixed the field not showing up problem when dragging.

Can you help me to reproduce popup hidden issue? I added this button to getOptionPanel method.

Code: Select all

        panel.add(new JButton(new AbstractAction("Update") {
            public void actionPerformed(ActionEvent e) {
                Thread runnable = new Thread() {
                    public void run() {
                        try {
                            Thread.sleep(5000);
                        }
                        catch (InterruptedException e1) {
                            e1.printStackTrace();
                        }
                        Runnable runnable = new Runnable() {
                            public void run() {
                                System.out.println("updated");
                                _pivotTablePane.bothHeadersUpdated();
                            }
                        };
                        SwingUtilities.invokeLater(runnable);
                    }
                };
                runnable.start();
            }
        }));

[JIDE Support]

Just so you know, 2.1.2.01 is released which fixed the product field not show up on the unassigned field area. I am still waiting you to provide a test case to reproduce disappearing filter popup problem as I couldn't reproduce it in the demo.

Thanks,

[grmo]

Yes, the latest 2.1.2.01 has fixed the field disappearing on drag and drop onto another field, thanks!

You misunderstood my other post though, the only remaining issue is this one, that I reported a week or two ago:

Further, I also notice that if the user is re-sizing a column or row when [bothHeadersUpdated] is called, the re-sizing is stopped and the user has to let go of the mouse and start again. I don't suppose there is any way around this issue?

In response you wrote at the time:

The width thing should be fixed already in the latest release. If not, please let me know.

But this issue remains, even in the latest version of 2.1.2.01 (downloaded today).

[JIDE Support]

Yes, I indeed misunderstood. And I don't think there is a way around this issue (as you guessed).

Thanks,

[grmo]

That's a shame -- given you can normally see tables with cells being updated, rows added/removed, and still the user can re-size a row or column, what is different about this situation?

[JIDE Support]

In the normal case you described, the table is not recreated. You can try to add a column to normal table and see if you can still resize without being interrupted. In our case, the table will be recreated so of course resize opertaion will stop too. Saying that, I will see if we don't recreate table if the table column count not changed after updating. If the table column count changed, then there is really no workaround.

Thanks,

[grmo]

If I call calculate() then bothHeadersUpdated() every second, even if no rows or columns are added or removed (I only ever have two rows and two columns -- though the data in the cells is changing), I cannot continue to re-size rows or columns.

Can you reproduce this behaviour?