Search code examples
htmljsonasp.net-mvcgoogle-visualization

Dynamically filling Google Gantt with JSON data from database

I am using Gantt Charts from Google Developers and I am attempting to generate the chart using data from Database instead of hardcoded data.

The chart structure is: Task ID, Task Name, End Date, Duration, Percent Complete, Dependency in order to display data. (example- https://developers.google.com/chart/interactive/docs/gallery/ganttchart#data-format)

My code is as followings:

My controller JSON method that populates the Gantt is below:

 public ActionResult GetChartData()
    {
        var schedulingData = db.Schedules.Select(x => new{ x.StartDate,x.EndDate,x.TaskName});

        return Json(schedulingData,JsonRequestBehavior.AllowGet);
    }

I only need Start Date, End date, task name pulled from the database. The other fields will be hardcoded. (hence where I believe my problem is).

My code is below:

<html>

<head>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
    google.charts.load('current', { 'packages': ['gantt'] });
    google.charts.setOnLoadCallback(drawChart);

     function drawChart() {
         var data = new google.visualization.DataTable();
         data.addColumn('string', 'Task ID');
         data.addColumn('string', 'Task Name');
         data.addColumn('string', 'Resource')
         data.addColumn('date', 'Start Date');
         data.addColumn('date', 'End Date');
         data.addColumn('number', 'Duration');
         data.addColumn('number', 'Percent Complete');
         data.addColumn('string', 'Dependencies');   

  
         $.getJSON("@Url.Action("GetChartData")", null, function (chartData) {
              $.each(chartData, function (i, item) {
                  data.addRow([item.TaskName, item.TaskName, "test", item.StartDate, 
              item.EndDate, null, 100, null]);
              });

         var options = {
            height: 400,
            gantt: {
              trackHeight: 30
            }
          };

         var chart = new google.visualization.Gantt(document.getElementById('chart_div'));
          chart.draw(data, options);
      
       }
     </script>
   </head>
<body>
<div id="chart_div"></div>
</body>

As you can see I am hardcoding about half the fields I need for the row, and retrieving the rest using javascript as following:

$.getJSON("@Url.Action("GetChartData")", null, function (chartData) {
          $.each(chartData, function (i, item) {
              data.addRow([item.TaskName, item.TaskName, "test", item.StartDate, item.EndDate, 
               null, 100, null]);
          });

My chart does not show up at all when using the code above. The $.getJSON section of code seems to break it, because if I hardcode some data for the chart, and comment out the $.getJSON call it works fine.

What am I doing wrong? Thank you.

Update: With Rahaturs code changes I have the following code:

 function drawChart() {
         var data = new google.visualization.DataTable();
         data.addColumn('string', 'Task ID');
         data.addColumn('string', 'Task Name');
         data.addColumn('string', 'Resource')
         data.addColumn('date', 'Start Date');
         data.addColumn('date', 'End Date');
         data.addColumn('number', 'Duration');
         data.addColumn('number', 'Percent Complete');
         data.addColumn('string', 'Dependencies');


  
         $.getJSON("@Url.Action("GetChartData")", null, function (chartData) {
             $.each(chartData, function (i, item) {
                 data.addRow([item.TaskName, item.TaskName, "test", item.StartDate, item.EndDate, null, 100, null]);
             });
         }

             $.ajax({
                 method: "GET",
                 url: "/GetChartData"
             })
                 .done(function (msg) {

                     var options = {
                         height: 400,
                         gantt: {
                             trackHeight: 30
                         }
                     };

                     var chart = new google.visualization.Gantt(document.getElementById('chart_div'));
                     chart.draw(data, options);

                 });
 }

Here is the error I receive on Runtime:

Syntax Error Upon Runtime

Solution

  • Your code will not wait for the $.getJSON to fiinish executing. Before you get data from the server the chart.draw(data, options); will be executed with null values.

    Do jquery ajax call and on success execute the following chart rendering codes like this:

    <script type="text/javascript">
    google.charts.load('current', { 'packages': ['gantt'] });
    google.charts.setOnLoadCallback(drawChart);

    function drawChart() {
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Task ID');
        data.addColumn('string', 'Task Name');
        data.addColumn('string', 'Resource')
        data.addColumn('date', 'Start Date');
        data.addColumn('date', 'End Date');
        data.addColumn('number', 'Duration');
        data.addColumn('number', 'Percent Complete');
        data.addColumn('string', 'Dependencies');   


        $.ajax({
            method: "GET",
            url: "/GetChartData"
        })
        .done(function( data ) {
        
            var options = {
                height: 400,
                gantt: {
                  trackHeight: 30
                }
              };

            var chart = new google.visualization.Gantt(document.getElementById('chart_div'));
            chart.draw(data, options);
    
        });   
    }
</script>