How to make dojo treeGrid categorized by two columns?

Question

I have a simple dojo treeGrid that is categorized just by first column. But how to make it categorized/collapsible by second as well? Note the treeGrid has totals shown in each category. Also, is there a way to move totals to the category level but not to the bottom?

var layout = [ 
    { cells: [ 
       [ {field: "year", name: "Year"}, 
         {field: "childItems", 
           children: [ { field: "unid", name: "unid", hidden: true},
                       { field: "geography", name: "Geography"}, 
                       { field: "country", name: "Coungtry"}, 
                       { field: "status", name: "Status"}, 
                       { field: "credit", name: "Credit"}, 
                       { field: "debit", name: "Debit"}
                     ], 
                  aggregate: "sum" 
                  } 
                  ]] } ]

var jsonStore = new dojo.data.ItemFileWriteStore({ url: <...............>});


var grid = new dojox.grid.TreeGrid({ 
    structure: layout, 
    store: jsonStore, 
    query: {type: 'year'}, 
    queryOptions: {deep: true},
    rowSelector: true, 
    openAtLevels: [false],
    autoWidth: true,
    autoHeight: true
    }, 
    dojo.byId("treeGrid"));

grid.startup();

dojo.connect(window, "onresize", grid, "resize");

sample JSON store:

{
  "identifier": "id",
  "label": "name",
  "items": [
    {
      "id": "2018",
      "type": "year",
      "year": "2018",
      "childItems": [
        {
          "id": "id0",
          "geography": "Asia Pacific",
          "country": "Australia",
          "programname": "Program 1",
          "totalPlanned": 0,
          "totalForecasted": 0
        },
        {
          .....
        }
      ]
    },
    {
          .....
    }
  ]
}

Dubbing

The TreeGrid documentation says that The grid can be any number of levels deep, which means that you can have more than one category, you just need to define that in the structure. And according to the sum level, can please make a fiddle or a demo, so we can investigate on it. – Valetudinarian

I suspect that but need an live example especially for query. – Dubbing

Answer 1

You can find completely working example over here:

Now, let me try to explain it:

Data

First of all to support multiple levels in the grid you must have your data in the same format. For tree with n levels, you need to have n-1 level grouping in your data itself. For example, JSON object below have 2 levels of grouping (year, geography) to support tree with 3 levels (root, parent, and child).

{
   "identifier":"id",
   "label":"name",
   "items":[
      {
         "id":"2018",
         "type":"year",
         "year":"2018",
         "geography":[
            {
               "id":"id1",
               "geography":"Asia Pacific",
               "childItems":[
                  {
                     "id":"ci1",
                     "country":"Australia",
                     "programname":"Program 1",
                     "credit":100,
                     "debit":50
                  }
               ]
            }
         ]
      }
   ]
}

Layout

To render a tree with n-levels you have to make sure layout of the tree is properly configured with same nesting as your data. To support data structure from JSON object above you need to set layout to:

[
   {
      cells:
      [
         [
            { field:"year", name:"Year" },
            {
              field:"geography",
              children:
              [
                  { field:"geography", name:"Geography" },
                  { 
                    field:"childItems",
                    children:[
                        { field:"unid", name:"unid", hidden:true },
                        { field:"country", name:"Country" },
                        { field:"programname", name:"Program" },
                        { field:"credit", name:"Credit" },
                        { field:"debit", name:"Debit" }
                     ],
                     aggregate:"sum",
                  },
              ]
            }
         ]
      ]
   }
]

You can see that, for each child level(s) you have to add a group (as I would like to call it) field and set first field within that group to your actual group field.

I hope this example will clear your doubt. PS: In the jsfiddle version I have used formatters just to hide aggregate values for string fields.

Tsang

Thank you Dipen. That works. I'll mark it as an answer. But do you have any idea how to move aggregated totals to category level when expanded? See picture above – Dubbing

@JohnGlabb You need to use formatter for that. See attached jsfiddle link which uses formatter and displays total on the parent row. – Tsang

@JohnGlabb Not sure about moving it though, technically it can display empty string if you want. jsfiddle.net/rucdz41w/154 – Tsang

In your jsfiddle example if you expand 2018 you will see TOTAL at the very bottom that is OK. But I also want to show subtotals for Asia Pacific and North America on that level – Dubbing

@JohnGlabb Looking at the options in the documentation, not sure if that is possible. – Tsang