Edit cell in Pivot Table

posted on December 13th, 2016

Resolved

Quan Hieu asked on December 13, 2016

Hi team,

Follow this demo about Pivot Table here, do we have any way to edit the cell the same like this. I mean when user click on a summary cell, I can get new value and all the details which are used to calculate the value for this field and then update all of these.

Many thanks.

13 answers

Public

Iryna Kulchytska ⋅ Flexmonster ⋅ December 13, 2016

Hi Quan Hieu,

Thank you for your question.

The component has the cell editing feature. It is turned off by default. The report's property editing should be set to true in order to enable the feature. Please do this as follows (line 79): http://jsfiddle.net/flexmonster/0j0e7exa/

The editing can be done via Drill Through view that is being opened on double-click. Here is a screencast http://screencast.com/t/YjxkHwQebxaI

Please note that the editing feature is available for the reports based on JSON or CSV data and is not available for OLAP cubes.

Please let us know if it works for you.

Kind regards,
Iryna

Public

Quan Hieu ⋅ December 14, 2016

Great, thanks Iryna.

But could we get all the details in Drill Through view when call the celldoubleclick or cellclick event here so that I can do some calculate for the details base on it.

For example here:

I have a summary cell which is calculated from many details like:

A 12
B 13
C 14

So the current cell value will be 39.

So if I can to update the summary value, I can change the value to 45. Then If I can get all Drill through Details, I can do some calculations to update the Details values so these values will be like:

A 14
B 15
C 16

So my question are:

1. Could we get all the details in Drill Through view when call the celldoubleclick or cellclick event here?
2. Could we enable to edit the summary cell (Then I will base on the Drill through Details to recalculate these values)?

Public

Iryna Kulchytska ⋅ Flexmonster ⋅ December 15, 2016

Dear Quan Hieu,

Thank you for the detailed explanation.

1. Do you mean to get all the details as event's parameter? No, it's not available.

2. The summary cell is not editable because it's always an aggregated value from other smaller values and it doesn't exist in the data model as a separate piece of data.

Yes, I understand that it makes editing a little bit complicated for end-user but there is no possibility just to change one number. The multidimensional data model is a quite complex structure.

Regards,
Iryna

Public

Quan Hieu ⋅ December 15, 2016

Ok, many thanks Iryna.

Public

Quan Hieu ⋅ December 15, 2016

Hi Iryna,
Following your example about how to edit the Pivot here http://jsfiddle.net/flexmonster/0j0e7exa/ . I see that after I update a value in Drill Through view. Then Click Save to export the JSON, the value of this object seem like not be changed.
For example: I have a object with property Qty = 1;
Then I come to Drill Through view to update it to 2
And after exporting to JSON and view the result, the value is still 1. Is there anyway to get the updated value when I save the JSON.
I really need this feature. Many thanks.

Public

Dmytro Zvazhii ⋅ Flexmonster ⋅ December 15, 2016

Hello Quan Hieu,
For now, the editing feature does not change the original data source. We added your request to our backlog and will consider adding this feature in future releases.
Regards,
Dmytro.

Public

Quan Hieu ⋅ December 15, 2016

Hi Dmytro,
Thanks for your information about the editing feature. Sorry if I make too much questions here.
I see that after updating a value in Drill Through view. The Pivot will automatically reload. I'm not sure about the way you store the data but I guess you store it in another json datasource and then reload the Pivot using this data.
If it true, could we get this JSON at that time? I mean when you press enter or move the mouse out to update the value in a cell then we can get the current JSON row which is updated or the whole JSON.
Because now I have an column name ID with unique value, so if I can get the current updated row or the whole JSON I can do some calculation later base on this column
Could you have any trick or hotfix to do this?

Or if it is impossible, could we get the json data that you use in export to csv function?
Many thanks.

Public

Ian Sadovy ⋅ Flexmonster ⋅ December 16, 2016

Hello Quan Hieu,

Thank you for the details.
Your description sounds reasonable.
We can add an event that will be triggered when a specific row is updated with a new value.
It will be ready within the next minor release (ETA Jan 03).
Also, we can send you a build a bit earlier to test the functionality.
Does it work for you?

Regards,
Ian

Public

Quan Hieu ⋅ December 16, 2016

Yeah, great. Many thanks Ian.

Public

Quan Hieu ⋅ December 23, 2016

Hi Ian, I've just received your update code and I think it great.

But currently our client want to edit at the summary cell like this. I still don't know how to make like they want.

I mean like I mentiond above:

I have a summary cell which is calculated from many details like:
A 12
B 13
C 14
So the current cell value will be 39.
So if I can to update the summary value, I can change the value to 45. Then If I can get all Drill through Details, I can do some calculations to update the Details values so these values will be like:
A 14
B 15
C 16

So now it would be great if I can make the summary cell become text box when click.

And when I click on a summary cell to view the drill through, I could get all the item related to the current cell in an event, so I can base on it to update the JSON data source and reload the Pivot.

Public

Ian Sadovy ⋅ Flexmonster ⋅ December 23, 2016

Hello Quan Hieu,

Thank you for the feedback.

Your case is clear, but there is no option in the component to edit the "summary" values.
Because updating "detail" values for this "summary" is not obvious and requires very custom logic.

But you can consider the following workaround:

Listen for the cell click or double click on the grid (http://www.flexmonster.com/api/cellclick/)
Render custom text input over the grid
Handle change of the custom input and update data via API (http://www.flexmonster.com/api/updatedata/)

Please let me know your thoughts.

Regards,
Ian

Public

Quan Hieu ⋅ December 23, 2016

Yes, but I'm not sure I understand it correctly.

Firstly, I can use 1 to listen for the cell click on the grid.

So here after I've clicked on a summary cell, rendered the text input and updated it, how could I know the Details that use to calculate for this cell, so I can update on these data (using your API)?

Public

Iryna Kulchytska ⋅ Flexmonster ⋅ December 27, 2016

Hello Quan Hieu,
Thank you for your question.
The handler of 'cellclick' event receives cell object that describes the cell that has been clicked. Here is the list of all cell properties - http://www.flexmonster.com/api/cellclick/. To know what data were aggregated in the clicked cell, please check the following properties of cell object:

columns - a list of members that represents the cell on columns (as coordinates),
rows - a list of members that represents the cell on rows (as coordinates),
measure - a measure aggregated in this cell,
isTotal - boolean property that indicates if the total cell was clicked.

In case you click a summary row cell, columns will be an empty array. In case you click a summary column cell, rows will be an empty array. In case you click a grand total cell, both rows and columns will be empty arrays. Thus, if total or grand total cell is clicked and you still want to know what fields are selected for columns and for rows, the following API calls might be useful:

getColumns() (http://www.flexmonster.com/api/getcolumns/) - to get the list of selected fields for columns;
getRows() (http://www.flexmonster.com/api/getrows/) - to get the list of selected fields for rows.

Please let me know if the above information is helpful for your case.
Kind regards,
Iryna

Edit cell in Pivot Table

13 answers

Please login or Register to Submit Answer