Changing Grouping order on a Pivot Grid

Posted by: elaas on 16 May 2020, 1:58 pm EST

  • Posted 16 May 2020, 1:58 pm EST

    Hi Support,

    First let me say what an excellent job you've done on the Wijmo controls. They are very powerful and well thought out. Now down to business. After a lot of time spent with the FlexGrid, I am making my first foray in to the PivotGrid. I understand that the PivotGrid inherits from FlexGrid. My grid is working, but not like I want it too. The header grouping order is different from what I want it to show. Because I cannot share my application details, I've mocked up a grid which shows which values are shown in the PivotPanel, how the grid renders currently, and how I would like it to render (see attached image).

    In an effort to override the default functionality, I have tried the setting autogenerateColumns=false, but they generate anyway, no matter what I do. Forget about what I want for a second. If I set autoGenerateColumns=false, and do nothing else, there should be no columns. It renders as if I changed nothing. I am setting this before the itemsSource is set.

    Next (and this assumes that the columns do not auto-generate), I have tried logic that implements the pattern shown here:

    https://jsfiddle.net/Wijmo5/gobtdg7t/

    It might work, but I wouldn't know, because no matter what, it keeps auto-generating the columns.

    Other insights:
    1) I am building columns based upon a payload from the server. The full set of columns are not known at the time of page load. An AJAX call returns data that is interrogated to create the columns.
    2) My attempt at trying to build the columns per the instructions in the link above is below. I had to modify it to remove some things that are proprietary, so it may not work as posted, but you should generally get the idea. There are no runtime exceptions.

    My questions:
    1) Is there any way to do what I want to do.

    2) Assuming #1 is true, what is the best way to achieve what I want to do. If no coding errors, should the approach in the URL above work? Is there a better way?

    3) Does setting autoGenerateColumns=false work in the PivotGrid. If so, why might it not be working here. If not, why not?

    Thank you!

    Eric

    CODE SAMPLE:

    import {Column,Row,AllowMerging} from 'wijmo/wijmo.grid';

    export class Demo {

    constructor() {
    this.tests = [
    { binding: 'testA', header: 'testA'},
    { binding: 'testB', header: 'testB'},
    ]
    //this is actually returned from the server
    this.weeks = [
    'week1','week2','week3'
    ]
    //set prior to calling buildColumns
    this.grid = null;
    }
    // ...
    // SNIP
    // ...

    buildColumns(data) {

    if(!this.grid) {
    throw "no grid available";
    }
    let weeks = this.weeks
    let headers = this.grid.columnHeaders;

    headers.rows.splice(headers.rows.length, 0, new Row());

    weeks.forEach((week) => {
    this.tests.forEach((test) => {
    let column = new Column();
    column.binding = week +'_' + test.binding;
    column.header = test.header
    this.grid.columns.push(column);
    headers.setCellData(0, headers.columns.length - 1, column.header);
    });
    });


    mergeColumnGroups(this.grid);

    // direct copy from the URL
    function mergeColumnGroups(flex) {
    debugger;
    // merge headers
    var colHdrs = flex.columnHeaders;
    flex.allowMerging = AllowMerging.ColumnHeaders;

    // merge horizontally
    for (var r = 0; r < colHdrs.rows.length; r++) {
    colHdrs.rows[r].allowMerging = true;
    }

    // merge vertically
    for (var c = 0; c < colHdrs.columns.length; c++) {
    colHdrs.columns[c].allowMerging = true;
    }

    // fill empty cells with content from cell above
    for (var c = 0; c < colHdrs.columns.length; c++) {
    for (var r = 1; r < colHdrs.rows.length; r++) {
    var hdr = colHdrs.getCellData(r, c);
    if (!hdr || hdr == colHdrs.columns[c].binding) {
    var hdr = colHdrs.getCellData(r - 1, c);
    colHdrs.setCellData(r, c, hdr);
    }
    }
    }

    // handle top-left panel
    for (var c = 0; c < flex.topLeftCells.columns.length; c++) {
    flex.topLeftCells.columns[c].allowMerging = true;
    }
    }
    }
    }
  • Replied 17 May 2020, 10:18 pm EST

    Hi Eric,

    Thank you for your feedback, we are glad that you like the controls.

    The PivotGrid inherits the FlexGrid but it is actually very different from it. The PivotGrid only works with PivotEngine or PivotPanel. All of the data in the PivotGrid is generated by the PivotEngine including the columns and rows. That is why the autoGenerateColumns does not have any effect. From the image provided, it seems that you have added row, columns and value fields. The columns generated in the PivotGrid are based on these values.
    Your desired result cannot be achieved using PivotGrid because it does not match the functionality of the OLAP. In Olap, if you have set weeks as the column fields and scoreA and scoreB as the value fields, the data will be first divided into different weeks and then each of the items in the each week will be aggregated and displayed on the PivotGrid. That is why, the weeks will be displayed in the upper row and the values in the lower rows.

    If you need to create your own layout, then you will need to use FlexGrid. It works with simple JSON data and its layout can be altered as per the requirements.

    I hope this clears your doubts but let us know in case you have any further queries.

    Regards,
    Ashwin
  • Replied 18 May 2020, 12:29 am EST

    Hi Ashwin,

    I was afraid that would be the case. I do understand the PivotGrid works differently than the FlexGrid. It has to, really, because the rendering is so different. Implementing custom pivots in a FlexGrid seems like it will be a significant amount of work, and may not be possible in current project timelines. Have you experience with such an implementation? Is there a general approach that you can share?

    Also, I'd like to request a feature that allows developers to specify a grouping order for the PivotGrid control, which would facilitate this functionality. I realize that the PivotGrid is a complex control, and that this feature will take time. While it would likely not be available for me to use in my current project, this would make the PivotGrid a bit more flexible going forward.

    Thanks for your assistance,

    Eric
  • Replied 18 May 2020, 11:46 pm EST

    Hi Eric,

    Can you provide the structure of your data so that I can investigate this further?

    ~regards
  • Replied 19 May 2020, 7:44 am EST

    Hi Arshwin,

    What I am envisioning is a callback hook (a sorting function) on the PivotGrid, or PivotEngine that will sort of the grouping of the header columns (e.g. sortHeaderGroups). In my example, Week 1 and Week 2 are broken out by Score A, and Score B, but I wanted the grouping to be reversed, such that Score A and Score B were broken out by Week 1 and Week 2. (This should have been, Test A, Test B to match my code example, sorry. They are analogous. I'll use Test A, Test B in the example below).

    Thus, the existing (and default) behavior for this hypothetical "sortHeaderGroups" callback in my example would look something like this;


    //colA and colB would be the bindings of the columns
    function mySortHeaderGroups(colA, colB) {
    let groupOrder = {
    week:0,
    test:1,
    anotherCol:2
    };
    //default to 99 (last) for all other groups
    let a = groupOrder[colA] || 99, b = groupOrder[colB] || 99;
    return (b<a)?-1:(b>a)?1:0;
    }


    Changing the integer values in
    groupOrder
    would adjust the order of the rendering. To get the rendering I desire, it would be like so:


    function mySortHeaderGroups(colA, colB) {
    let groupOrder = {
    test:0,
    week:1,
    anotherCol:2
    };
    //default to 99 (last) for all other groups
    let a = groupOrder[colA] || 99, b = groupOrder[colB] || 99;
    return (b<a)?-1:(b>a)?1:0;
    }



    Using a callback here will be useful, because most users will also have access to the PivotPanel, so a static configuration would be insufficient to handle the grouping of dynamic pivot columns. It also allows for extreme sorting customization from other data that may be relevant in scope, such as a user defined grouping order. Default behavior would be executed if the callback is not assigned.

    I certainly do not have all of the understanding of the consequences from a development standpoint, and thus cannot tell you if this is feasible, or even possible, but that's what I'm thinking.

    Thanks,

    -EL
  • Replied 19 May 2020, 7:57 am EST

    Hi Arshwin,

    I think i misunderstood your question on the first read, but please take this into consideration. While I cannot share the exact structure, I've tried to produce some sample data that illustrates the pivot in the example I have provided. This is what I think the sample data might look like:

    [
    {city: 'Anchorage', state: 'AL', testA: 90, week: 1},
    {city: 'Anchorage', state: 'AL', testB: 30, week: 2},
    {city: 'Atlanta', state: 'GA', testA: 65, week: 1},
    {city: 'Atlanta', state: 'GA', testB: 90, week: 2},
    {city: 'Houston', state: 'TX', testA: 30, week: 1},
    {city: 'Houston', state: 'TX', testB: 75, week: 2},
    {city: 'Sarasota', state: 'FL', testA: 25, week: 1},
    {city: 'Sarasota', state: 'FL', testB: 50, week: 2}
    ]
  • Replied 19 May 2020, 10:51 pm EST

    Hi Eric,

    Thank you for the data and the information. I am investigating this and will update you as soon as I will have any further information.

    ~regards
  • Replied 20 May 2020, 1:32 am EST

    Hi Ashwin,

    Firstly, I realized that I've been misspelling your name. Sorry about that. Secondly, on further review, I think the rows may have a value for each test. so data would look more like:

    [
    //---
    {city: 'Anchorage', state: 'AL', testA: 90, testB: 80, week: 1},
    //...
    ]


    Again. Sorry for the miscommunication.

    Thanks,

    -EL
  • Replied 20 May 2020, 4:40 am EST

    Hi Ashwin,

    I'm looking to use GroupPanel to do the grouping and summation instead of PivotGrid now. However, it seems that the behavior has changed since I last used it. Before, (v2018.1), GroupRow objects were generated with a cell for each non-grouped column, and aggregates could be applied for each column.

    Now it appears that there is one merged cell for all non-grouped columns, with the "group by" aggregate in there. IMO, this is a step backwards, as I could use a CustomMergeManager and formatItem to accomplish this if that's what I wanted. However now I cannot sum totals per non-grouped column any more. I see nothing in the documentation of GroupPanel that provides for any configuration to control this. Am I missing something? Is there a way to regain this functionality.

    Thanks,

    Eric
  • Replied 20 May 2020, 5:52 am EST

    HI Ashwin,

    Please disregard the last message. It's been a while since I used GroupPanel, and I forgot to add the aggregate property on to the grid column. This allowed the each column to sum.

    Happy to hear your thoughts on the remaining topics.

    Thanks,

    Eric
  • Replied 21 May 2020, 8:54 pm EST

    Hi Eric,

    Sorry for the delayed response.

    First of all, there is no need to worry about misspelling my name. Also, I am glad that your grouping issue is resolved.

    Now, regarding your main issue, we cannot add a group order property like you suggested, because it is already implemented. In the PivotPanel, the order of the column fields represent how the data should be grouped. If you have added more than 1 column field, you can change their order by dragging the field and the data will be grouped accordingly.
    The issue with this is that this cannot be done with the values displayed on the columns, which is your requirement. The values are always grouped last. I have added a feature request for changing this behavior. The internal tracking id of the case is 436943. I will update once I will hear from the developers.

    ~regards
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels