CODEWITHSUNDEEP
kendo-uikendo-gridkendo-ui-grid
O. Khan
O. Khan

Reputation: 5

Dynamic Kendo grid columns from response data

I have been trying to create a Kendo grid with dynamic column values based on a date item as part of the response data.

The data I have looks like this:

[
    { Date: '01-01-2018', Name: 'Foo', Value: 1000},
    { Date: '02-01-2018', Name: 'Foo', Value: 2000},
    { Date: '03-01-2018', Name: 'Foo', Value: 3000},
    { Date: '01-01-2018', Name: 'Bar', Value: 1400},
    { Date: '02-01-2018', Name: 'Bar', Value: 2000},
    { Date: '03-01-2018', Name: 'Bar', Value: 5000}
]

My intended structure for the grid is the following:

| Name |  Jan | Feb  | Mar  |
|------|------|------|------|
| Foo  | 1000 | 2000 | 3000 |
| Bar  | 1400 | 2000 | 5000 |

I had a look at https://docs.telerik.com/kendo-ui/controls/data-management/grid/how-to/various/create-with-dynamic-columns-and-data-types but it was not quite what I was trying to do and it required that I have the columns sent as part of the response.

I am working with a wrapper for GridOptions that populates the columns through a staticly defined json. Since my columns are dynamic I am having an issue with defining them there.

On top of that, I am unable to pick out the values for the date besides brute forcing through the values and storing all the unique date entries as columns. And if I have them, then how do I match them up with the correct data entry to display the correct value in the grid?

Upvotes: 0

Views: 2649

Answers (2)

Richard
Richard

Reputation: 27508

You could use the kendo ui PivotGrid component. It is built for dealing with categorical data. However, you probably would find that takes up to much real estate.

That leaves the task of manually pivoting the data yourself. A task that is relatively simple if you make the assumptions that the Date values over all the data never have a month from two different years (If there was a 01-01-2018 and 01-01-2017 they are both Jan) and the is only one row for each date/name combo. (If there were two values for a date/name you would have to decide what is done with the value? min, max, first, last, mean ?)

data = 
[
    { Date: '01-01-2018', Name: 'Foo', Value: 1000},
    { Date: '02-01-2018', Name: 'Foo', Value: 2000},
    { Date: '03-01-2018', Name: 'Foo', Value: 3000},
    { Date: '01-01-2018', Name: 'Bar', Value: 1400},
    { Date: '02-01-2018', Name: 'Bar', Value: 2000},
    { Date: '03-01-2018', Name: 'Bar', Value: 5000}
];

// distinct month nums over all data
months = [];
data.forEach(function(item) {
  var parts = item.Date.split('-');
  var month = parts[0] - 1;
  if (months.indexOf(month) == -1) months.push(month);
});

// sort and convert month num to month name (for columns)
months.sort();
months.forEach(function(monthNum,index,arr) {
  arr[index] = new Date(2018,monthNum,1).toLocaleString("en-US", { month: "short" });
});

function mmddyyyyToMon(mmddyyyy) {
  var parts = mmddyyyy.split("-");
  var jsMonth = parts[0] - 1;
  var jsDay = parts[1];
  var jsYear = parts[2];
  return new Date(jsYear,jsMonth,jsDay).toLocaleString("en-US", { month: "short" });
}

// helper to make sure pivot item has one field for every month observed over all data
function newPivotItem () {
  var result = { Name: '' };
  months.forEach(function(month) {
    result[month] = undefined;
  })
  return result;
}

// presume data grouped by Name and ordered by month within
var pivotData = [];
var pivotItem = {};
data.forEach (function (item) {
  var parts = item.Date.split("-");
  var jsMonth = parts[0] - 1;
  var jsDay = parts[1];
  var jsYear = parts[2];
  var month = new Date(jsYear,jsMonth,jsDay).toLocaleString("en-US", { month: "short" });

  if (pivotItem.Name != item.Name) {
    // start next group of data for a name
    pivotItem = newPivotItem();
    pivotData.push(pivotItem);
    pivotItem.Name = item.Name;
  }

  // set value for month for the name
  pivotItem[month] = item.Value;
})

console.log (pivotData);

Upvotes: 1

David Lebee
David Lebee

Reputation: 596

I hope this helps you. I made a dojo for you and pasted the code bellow for the future. I used the possibility of having a callback transport for the read.

https://dojo.telerik.com/imeNOdUh/2

<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <title>Untitled</title>

  <link rel="stylesheet" href="https://kendo.cdn.telerik.com/2018.1.221/styles/kendo.common.min.css">
  <link rel="stylesheet" href="https://kendo.cdn.telerik.com/2018.1.221/styles/kendo.rtl.min.css">
  <link rel="stylesheet" href="https://kendo.cdn.telerik.com/2018.1.221/styles/kendo.default.min.css">
  <link rel="stylesheet" href="https://kendo.cdn.telerik.com/2018.1.221/styles/kendo.mobile.all.min.css">

  <script src="https://code.jquery.com/jquery-1.12.3.min.js"></script>
  <script src="https://kendo.cdn.telerik.com/2018.1.221/js/angular.min.js"></script>
  <script src="https://kendo.cdn.telerik.com/2018.1.221/js/jszip.min.js"></script>
  <script src="https://kendo.cdn.telerik.com/2018.1.221/js/kendo.all.min.js"></script></head>
<body>
  <div id="my-grid"></div>

  <script>

    function fetchData(success, fail) {
        success([
        { Date: '01-01-2018', Name: 'Foo', Value: 1000},
        { Date: '02-01-2018', Name: 'Foo', Value: 2000},
        { Date: '03-01-2018', Name: 'Foo', Value: 3000},
        { Date: '01-01-2018', Name: 'Bar', Value: 1400},
        { Date: '02-01-2018', Name: 'Bar', Value: 2000},
        { Date: '03-01-2018', Name: 'Bar', Value: 5000}
      ]); 
    }    

    var $gridElement = $('#my-grid');
    $gridElement.kendoGrid({
        dataSource: {
            transport: {
            read: function(options) {

              fetchData(function(data) {

                // get month names
                var monthNames = data
                    .map(function(t) {
                    var monthName = kendo.format("{0:MMM}", kendo.parseDate(t.Date, 'MM-dd-yyyy'));
                    return monthName;
                  })
                    .reduce(function(p, t) {
                        if (p.indexOf(t) == -1)
                        p.push(t);

                        return p;                        
                  }, []);

                // transform
                var result = data.reduce(function(p, t) {
                    var monthName = kendo.format("{0:MMM}", kendo.parseDate(t.Date, 'MM-dd-yyyy'));

                  var existing = p.filter(function(t2) {
                    return t2.Name == t.Name;
                  });

                  if (existing.length) {
                    existing[0][monthName] = t.Value;
                  } else {
                    var n = {
                        Name: t.Name
                    };
                    monthNames.forEach(function(m) {
                      n[m] = 0;
                    });

                    n[monthName] = t.Value;
                    p.push(n);
                  }

                  return p;
                }, []);

                options.success(result);

              });


            }
          }
        }
    });

  </script>
</body>
</html>

Upvotes: 0

Related Questions