Search code examples
javascriptajaxvb.netgoogle-visualization

How to properly bind Google timeline chart with Ajax/JSON request

I am trying to make a timeline chart using Google Charts and having a few issues I will detail below.

I started with an extremely simple example and then am trying to convert this to use an Ajax call to populate the chart.

To start I used a simple implementation with a hardcoded single line of data that works just fine.

google.charts.load("current", { packages: ["timeline"] });
    google.charts.setOnLoadCallback(drawChart);
    function drawChart() {
        var container = document.getElementById('CurrentOrders');
        var chart = new google.visualization.Timeline(container);
        var data = new google.visualization.DataTable();
        data.addColumn({ id: 'OrderNum', label: 'Order Number', type: 'string' });
        data.addColumn({ id: 'PN', label: 'Part Number', type: 'string' });
        data.addColumn({ id: 'StartDate', label: 'Start', type: 'date' });
        data.addColumn({ id: 'EndDate', label: 'End', type: 'date' });
        
        data.addRows([                
            ['M12345', 'MyPartNumber', new Date(2023, 1, 4), new Date(2023, 1, 6)]
        ]);
            var options = {
            timeline: {
                groupByRowLabel: true
            }
        };
        chart.draw(data, options);
    }

enter image description here

Next I created a Webmethod that returns the same dataset.

<System.Web.Services.WebMethod>
Public Shared Function GetOrders() As String

    Dim mOrder As List(Of Morder) = New List(Of Morder)()
    Dim constr As String = ConfigurationManager.ConnectionStrings("HYDMCS").ConnectionString
    Dim Query As String = "SELECT TOP(1) OrderNumber,Part_Number, CONVERT(DATE,GETDATE()) AS StartDate, CONVERT(DATE,DATEADD(DAY,2,GETDATE())) AS EndDate, MfgDueDate FROM HYD_GP_ProductionSchedule ORDER BY Part_Number DESC "
    Using con As New SqlConnection(constr)
        Try
            con.Open()
            Using sqlCmd As SqlCommand = New SqlCommand(Query, con)
                Using reader As SqlDataReader = sqlCmd.ExecuteReader
                    While reader.Read
                        If reader.HasRows Then
                            mOrder.Add(New Morder() With {
                                .OrderNum = reader(0).ToString(),
                                .PN = reader(1).ToString(),
                                .StartDate = reader(2),
                                .EndDate = reader(3)
                            })
                        End If
                    End While
                End Using
            End Using
            con.Close()
        Catch ex As Exception
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Using
    Dim js As JavaScriptSerializer = New JavaScriptSerializer()
    Return js.Serialize(mOrder)
End Function

It returns a result as expected.

[{"OrderNum":"M531270 ","PN":"VXLD0500510 C ","StartDate":"/Date(1704344400000)/","EndDate":"/Date(1704517200000)/"}]

However when trying to consume the results I have several issues. First the code.

 google.charts.load("current", { packages: ["timeline"] });
    // Set a callback to run when the Google Visualization API is loaded. 
    google.charts.setOnLoadCallback(drawChart);
    function drawChart() {
        $.ajax(
            {
                type: 'POST',
                dataType: 'JSON',
                contentType: 'application/json',
                url: 'PlannersWorkBench.aspx/GetOrders',
                success:
                    function (response) {
                        drawGraph(response.d);
                    }
            });
    };
    // Callback that creates and populates a data table,  
    function drawGraph(dataValues) {
        // Initialization.  
        var data = new google.visualization.DataTable();
        // Setting.            
        data.addColumn({ id: 'OrderNum', label: 'Order Number', type: 'string' });
        data.addColumn({ id: 'PN', label: 'Part Number', type: 'string' });
        data.addColumn({ id: 'StartDate', label: 'Start', type: 'date' });
        data.addColumn({ id: 'EndDate', label: 'End', type: 'date' });
        // Processing.              
        for (var i = 0; i < dataValues.length; i++) {
            // Setting.  
            data.addRow([dataValues[i].OrderNum, dataValues[i].PN, dataValues[i].StartDate, dataValues[i].EndDate]);
            //data.addRow(['M1', 'PN1', new Date(2023, 1, 4), new Date(2023, 1, 6)]);
        }
        // Instantiate and draw our chart, passing in some options.  
        var container = document.getElementById('CurrentOrders');
        var chart = new google.visualization.Timeline(container);
        // Draw chart.  
        var options = {
            timeline: {
                groupByRowLabel: true
            }
        };
        chart.draw(data, options);
    }

The first and formost issue is that I do not get a chart, just an error.

enter image description here

When trying to troubleshoot I hardcoded the row value to insert to see if the JSON response is the issue.

If I change..

for (var i = 0; i < dataValues.length; i++) {
            // Setting.  
            data.addRow([dataValues[i].OrderNum, dataValues[i].PN, dataValues[i].StartDate, dataValues[i].EndDate]);
            }

to

for (var i = 0; i < dataValues.length; i++) {
            // Setting.  
            data.addRow(['M1', 'PN1', new Date(2023, 1, 4), new Date(2023, 1, 6)]);
        }

I do see a change, however there are still issues. If you look at the screenshot below you can see that the order number is not visible, also there are 136 rows in the chart. For whatever reason dataValues.length=136 even though I am only getting one result from the Webmethod.

enter image description here

Any advice would be great or a working example. Thanks in advance for any help!

Solution

  • Two items were needed to correct this.

    While I can't find any documentation detailing this the data needs to be parsed before looping through it to grab the values.

    The original code:

    var data = new google.visualization.DataTable();
    // Setting.            
    data.addColumn({ id: 'OrderNum', label: 'Order Number', type: 'string' });
    data.addColumn({ id: 'PN', label: 'Part Number', type: 'string' });
    data.addColumn({ id: 'StartDate', label: 'Start', type: 'date' });
    data.addColumn({ id: 'EndDate', label: 'End', type: 'date' });
    // Processing.              
    for (var i = 0; i < dataValues.length; i++) {
        // Setting.  
        data.addRow([dataValues[i].OrderNum, dataValues[i].PN, dataValues[i].StartDate, dataValues[i].EndDate]);            
    }

    Was changed to..

    var data = new google.visualization.DataTable();
        // Setting.            
        data.addColumn({ id: 'OrderNum', label: 'Order Number', type: 'string' });
        data.addColumn({ id: 'PN', label: 'Part Number', type: 'string' });
        data.addColumn({ id: 'StartDate', label: 'Start', type: 'date' });
        data.addColumn({ id: 'EndDate', label: 'End', type: 'date' });
        // Processing.

        parsedData = JSON.parse(dataValues);
        
        for (var i = 0; i < parsedData.length; i++) {
            //Setting.  
            data.addRow([parsedData[i].OrderNum, parsedData[i].PN, new Date(parsedData[i].StartDate), new Date(parsedData[i].EndDate)]);
        }

    Note the parsedData variable.

    Next issue was the JSON formatting on the date field. According to Google documentation when using JSON the date needs to come over as a string.

    "Using the Date constructor is useful when manually constructing your DataTable using the addColumn(), addRow(), and addRows() methods, as well as the arrayToDataTable() method. However, if using JSON to specify data, the string representation needs to be used."

    That being said the data type for the date fields was changed to a string and formatted properly in SQL in my query.