專案需求: kendoGrid需依指定欄位分群加總並依預設格式顯示小計。

例如,資料有Color、Size、Count三欄,報表需呈現SUM(Count) GROUP BY Color的小計結果。

kendoGrid支援群組化(Grouping),參考API文件在DataSource定義group,並為Color及Count column定義groupFooterTemplate,便可完成分群統計並加入小計列(即上圖所示)。[Live Demo]

    var items = [
      { Color: "Blue", Size: "L", Count: 1024 },
      { Color: "Blue", Size: "M", Count: 256 },
      { Color: "Red", Size: "M", Count: 16 },
      { Color: "Red", Size: "S", Count: 8 }
    ];
    var ds = new kendo.data.DataSource({
      data: items,
      group: {
        field: "Color", aggregates: [
          { field: "Count", aggregate: "sum" }
        ]
      }
    });
  $("#grid").kendoGrid({
    columns: [
      { 
        field: "Color",
        groupFooterTemplate: "Subtotal"
      },
      { field: "Size" },
      { 
        field: "Count", 
        groupFooterTemplate: "#=kendo.format('{0:n0}', sum)#"
      }
    ],
    dataSource: ds
  });

下一步挑戰來了: 規格書要求在Subtotal字樣需加註該筆小計所屬顏色,例如: Subtotal (Blue),並取消上方的群組收合功能。groupFooterTemplate除了指定範本字串,還可提供自訂函式function(data) { … },data參數物件將包含宣告於aggregates的群組項目,例如: data.Count.sum即為計數加總結果。本想如法炮製,在aggregates增加{ field: "Color", aggregate: "max" },以為能像SQL MAX(Color)一樣取得Blue/Red,Kendo UI只支援average, count, max, min, sum五種彙總運算,不支援自訂彙總函式,而且一律當數字處理,發現data.Color.max的結果為0而非顏色字串內容。

爬文後,找到Telerik RD在討論區示範的替代解法: 定義Color的groupHeaderTemplate函式,可由data.field可得欄位名稱"Color"、data.value則可取得顏色字串"Blue"或"Red",將結果保存於全域變數,稍後於groupFooterTemplate便可將顏色字串放入小計標題,程式如下: [Live Demo]

  $("#grid").kendoGrid({
    columns: [
      { 
        field: "Color",
        groupHeaderTemplate: function(data) {
          //data.field = "Color", data.value = color_value
          groupColor = data.value; //store in global variable 
          return data.value;
        },
        groupFooterTemplate: "#= 'Subtotal (' + groupColor + ')'#"
      },
      { field: "Size" },
      { 
        field: "Count", 
        groupFooterTemplate: "#=kendo.format('{0:n0}', sum)#"
      }
    ],
    dataSource: ds
  });

搞定小計列,隱藏群組收合功能相對簡單,透過幾行CSS就搞定囉~[Live Demo]

<style>
    .k-grouping-row { display: none; }
    .k-grid .k-group-col { display: none; }
    th.k-group-cell, td.k-group-cell { display: none; }
</style>


Comments

Be the first to post a comment

Post a comment