Forums Home / Wijmo / General Discussion
Posted by: hannie.h.wang on 29 June 2018, 5:21 pm EST
hannie.h.wang
Posted 29 June 2018, 5:21 pm EST
Hi
I’m using “Grand totals” in OLAP to automatically display summary data, but some of the data is incorrect, and I want to get and modify some of the grandTotal data.
I use the itemFormatter method to traverse the cells now, but I find that the cells I get do not have special flags for Grand Totals cells
Can you tell me some methods or suggestions do you have?
manish.gupta
Posted 1 July 2018, 7:59 pm EST
Hi,
Can you please tell us more about your use case and implementation in which you are getting the wrong results so that we may further look into the issue.
If you are trying to get the grand total cell in itemFormatter you can do so by checking 3 conditions:-
Please refer to following code snippet:
pivot.formatItem.addHandler((s,e)=>{
/* check if given cell is part of wijmo.grid.CellType.Cell */
if(s.cells==e.panel){
/* check if cell has 'wj-aggregate' class and is the part of last row */
if(wijmo.hasClass(e.cell,'wj-aggregate')&&e.row==pivot.rows.length-1){
/* this is a grand total cell */
e.cell.innerHTML="custom total";
}
}
});
Here is an example which implements the same:-https://stackblitz.com/edit/js-usuyh2?file=index.js
~Manish
hannie.h.wang
Posted 4 July 2018, 1:45 pm EST
Hi Manish,
Thank you very much for your help in solving my problem.
When I used the itemFormatter () method, I couldn’t get the class name of the “wj-aggregate” in grand total cell, but I got it using formatItem.addHandler () based on your prompt.
manish.gupta
Posted 4 July 2018, 10:02 pm EST
Hi,
We are glad to hear that you were able to solve your problem.
The issue with itemFormatter is arising due to the fact that ‘wj-aggregate’ class is added later to the cell.
This behavior is because of the fact that PivotGrid extends the FlexGrid so first the grid cell is created(that’s when the itemFormatter is called) and after that PivotGrid adds its specific info to the cell(that’s when ‘wj-aggregate’ class is added).
So if cell’s formatting is dependent on some value of the cell’s property itself then it is advised to handle formatItem event instead of using itemFormatter.
~Manish
hannie.h.wang
Posted 10 July 2018, 12:39 pm EST - Updated 3 October 2022, 11:21 am EST
manish.gupta
Posted 10 July 2018, 10:11 pm EST
Hi,
You can get the row keys with the help of getKeys() method provided by PivotGrid.
Please refer to following code snippet:-
pivot.formatItem.addHandler((s,e)=>{
if(s.cells==e.panel){
if(wijmo.hasClass(e.cell,'wj-aggregate')&&e.row==pivot.rows.length-1){
/* this is row total cell */
e.cell.innerHTML="row total";
}else
if(wijmo.hasClass(e.cell,'wj-aggregate')){
/* this is col grand total cell */
let rowData=e.panel.rows[e.row].dataItem; /* get dataItem associated with row */
let keys=pivot.getKeys(e.row,e.col);
let rowKeys=pivot.rowKey;
e.cell.innerHTML=rowKeys.values.join(":");
}
}
});
You may also refer to the following updated sample which implements the same:-
https://stackblitz.com/edit/js-9k24qd?file=index.js
You may refer to the following document for more info on getKeys:-
http://demos.wijmo.com/5/Angular/WijmoHelp/WijmoHelp/topic/wijmo.olap.PivotEngine.Class.html#getKeys
Also can you please elaborate more about your use case in which grand totals values are not right for you?
~Manish
hannie.h.wang
Posted 13 July 2018, 11:27 am EST
Hi Manish,
Thank you very much for your help.
My current usage case: for some of the data we don’t need to get a summary value for a table column, we need to average, not repeat a count number of summary values, and so on, so I need to manually modify those gandtotal cell values.
best regards,
Hannie
abhishek.dutta
Posted 15 July 2018, 10:24 pm EST
Hi,
If we understand correctly then you need to display ‘Average’ values for fields as summary data instead of sum.
In that case, you may set the aggregate property of pivotField to ‘Avg’.
Please refer to following code snippet:-
let downloadsField=panel.fields.getField(‘Downloads’);
downloadsField.aggregate=“Avg”;
You may also refer to following updated sample:-
https://stackblitz.com/edit/js-s5cnzq?file=index.js
Refer here for a list of possible values for aggregate property:-
http://demos.wijmo.com/5/Angular/WijmoHelp/WijmoHelp/topic/wijmo.Aggregate.Enum.html
hannie.h.wang
Posted 27 July 2018, 6:24 pm EST
Hi Abhishek,
Thanks for your reply and your suggestions. =D
I know wijmo provides this attribute, but in my case, it doesn’t fully satisfy my needs.
hanniewang
Posted 7 October 2018, 2:22 pm EST
Hi Manish,
When I replace
e.cell.innerHTML = wijmo.Globalize.format('hello', s.columns[e.col].format);
Is there any solutions?
Thank you for your time!
sharad.tomer
Posted 7 October 2018, 4:30 pm EST
The issue is arising because formatItem is called whenever grid needs to update view and setCellData() method causes the cell value to update which creates the need to update view and hence ultimately triggering the formatItem event again.
Could you please let us know about your requirement so that we may suggest you the best possible solution.
~Sharad
hanniewang
Posted 7 October 2018, 5:53 pm EST
Hi Sharad,
I have to modify some special GrandTotal values, and then I can export to Excel file using this grid. However, if I modified the GrandTotal values by cell.innerHTML, the values exported Excel file are different from displayed in grid.
So I finished my requirement by calling method setCellData() in formatItem.addHandler(). Finally, the result is the formatItem() being called in an infinite loop.
Is there any advice to avoid such a problem?
the code I modify some special Grand Total values:
let specialMea = ['spe1', 'spe2', 'spe3'];
let mea = [
{type: 'spe0', name: 'downloads'},
{type: 'spe1', name: 'sales'}
];
gridPivot.formatItem.addHandler(function(s, e) {
if (e.panel === s.cells && wijmo.hasClass(e.cell, 'wj-aggregate')) {
let dataValue;
if(e.row === gridPivot.rows.length - 1) {
dataValue = ' row total';
} else {
dataValue = 'column total';
}
for (let i = 0; i < mea.length; i++) {
if (specialMea.includes(mea[i].type)) {
if (s.columns[e.col].binding + ':0;'.includes(mea[i].name + ':0;')) {
// e.panel.setCellData(e.row, e.col, dataValue, false);
e.cell.innerHTML = wijmo.Globalize.format(dataValue, s.columns[e.col].format);
}
}
}
}
}
sharad.tomer
Posted 8 October 2018, 4:05 pm EST
Hi,
To fulfil this requirement, you may pass a formatItem function as one of the options while exporting the grid and make necessary changes to the exported cell from the formatItem.
Please refer to the following sample which implements the same: https://stackblitz.com/edit/js-7vruye?file=index.js
~Sharad
hanniewang
Posted 10 October 2018, 11:04 pm EST
hi Sharad,
Thanks for your support!
This helps a lot.
However, modifying the value of cell when exporting Excel files does affect the performance of data export.
sharad.tomer
Posted 11 October 2018, 5:31 pm EST
There should not be any significant drop in performance if all we are doing in formatItem is changing cell’s value. We tested the above-provided sample with and without formatItem and found that on an average there is only about 5-6ms difference between the two.
If you are experiencing a significant drop in performance, you may try to lower the processing done in the formatItem, also do you have any sample that we could analyze and suggested the appropriate solution?
~Sharad
