Google Chart Dashboard with Slider

Question

I have successfully created a web site with dynamic charts using Google Charts. Then I tried to add a slider but I failed. The error I get is "One or more participants failed to draw()" and "Column 0 is not numeric".

The script of the working web site is:

<script type="text/javascript">

google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);

function drawChart() {
  var jsonData = $.ajax({
    url: " ulsuccessperop.php ",          
    dataType: "json",
    async: false
  }).responseText;

  var data = new google.visualization.DataTable(jsonData);
  var options = {
     'title': 'UL Success per operatore',              
     'width':1200,
     'height':900,
     chartArea:{left:80,top:50,width:"70%",height:"80%"}
     };

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

</script>

The script of the not working web site is:

<script type="text/javascript">

google.charts.load('current', {'packages':['corechart', 'controls']});
google.charts.setOnLoadCallback(drawDashboard);

function drawDashboard() {
   var jsonData = $.ajax({
       url: "ulsuccessperop.php",               
       dataType: "json",
       async: false
       }).responseText;

   var data = new google.visualization.DataTable(jsonData);                     
   var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard_div'));        

   var dataRangeSlider = new google.visualization.ControlWrapper({      
      'controlType': 'NumberRangeFilter',
      'containerId': 'filter_div',
      'options': {
            'filterColumnLabel': 'Data'
            }
       });

   var lineChart = new google.visualization.ChartWrapper({
       'chartType': 'LineChart',
       'containerId': 'chart_div',
       'options': {
           'title': 'UL Success per operatore',             
           'width':1200,
           'height':900,
           chartArea:{left:80,top:50,width:"70%",height:"80%"}
           }

      });

dashboard.bind(dataRangeSlider, lineChart);
dashboard.draw(data);

}

</script>

The data is provided by a PHP page (ulsuccessperop.php) that return a JSON formatted array of values (multiple column). Probably the data to be provided to Dashboard is different to those to be provided to Chart, but I can't find anything in the Google Chart documentation about that.

New code after changes suggested by WhiteHat:

google.charts.load('current', {'packages':['corechart', 'controls']});

google.charts.setOnLoadCallback(drawDashboard);

function drawDashboard() {
    var jsonData = $.ajax({
        url: "data/regperop.php,
        dataType: "json",
        async: false
        }).responseText;

    var data = new google.visualization.DataTable(jsonData);                
    var view = new google.visualization.DataView(data);                     
    view.setColumns([{
      calc: function (data, row) {
        return new Date(data.getValue(row, 0))
      },
      type: 'date',
      label: 'Data'
    }, 1, 2, 3, 4]);


    var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard_div'));   

    var dataRangeSlider = new google.visualization.ControlWrapper({     
        'controlType': 'DateRangeFilter',
        'containerId': 'filter_div',
        'options': {
            'filterColumnLabel': 'Data'
       }
    });


    google.visualization.events.addListener(dataRangeSlider, 'ready', function () {
        var state = dataRangeSlider.getState();
        console.log(state.lowValue, state.highValue);
      });


    var lineChart = new google.visualization.ChartWrapper({
        'chartType': 'LineChart',
        'containerId': 'chart_div',
        'options': {
            'title': 'Numero registrazioni per operatore',              'width':1200,
            'height':900,
            chartArea:{left:80,top:50,width:"70%",height:"80%"}
        }

    });


    dashboard.bind(dataRangeSlider, lineChart);
    dashboard.draw(data);

}

An example of JSON data follows:

{"cols":[{"label":"Data","type":"date"},
        {"label":"OP1","type":"number"},
        {"label":"OP2","type":"number"},
        {"label":"OP3","type":"number"},
        {"label":"OP4","type":"number"}],
 "rows":[{"c":[{"v":"08-01-2016"},{"v":0},{"v":0},{"v":0},{"v":0}]},
         {"c":[{"v":"08-02-2016"},{"v":0},{"v":0},{"v":0},{"v":0}]},
         {"c":[{"v":"08-03-2016"},{"v":0},{"v":0},{"v":0},{"v":0}]},
         {"c":[{"v":"08-04-2016"},{"v":0},{"v":0},{"v":0},{"v":0}]},
         {"c":[{"v":"08-05-2016"},{"v":0},{"v":0},{"v":0},{"v":0}]},
         {"c":[{"v":"08-06-2016"},{"v":0},{"v":0},{"v":0},{"v":0}]},
         {"c":[{"v":"08-07-2016"},{"v":0},{"v":0},{"v":0},{"v":0}]},
         {"c":[{"v":"08-08-2016"},{"v":0},{"v":0},{"v":0},{"v":0}]},
         {"c":[{"v":"08-09-2016"},{"v":0},{"v":0},{"v":0},{"v":0}]},
         {"c":[{"v":"08-10-2016"},{"v":0},{"v":0},{"v":0},{"v":0}]}]}

if you look at json, are number values surrounded by quotes? (e.g. --> "v":"100") -- if so, this can cause problems with google charts — WhiteHat, Oct 21 '16 at 17:02
are you using `json_encode` in PHP? if so, what does that statement look like? — WhiteHat, Oct 21 '16 at 17:03
The two examples use the same data, the former works the latter doesn't. The only difference is that the former doesn't use dashboard. — Giorgio Torassa, Oct 21 '16 at 21:15
i understand, it would help to see a sample of the data -- code looks fine — WhiteHat, Oct 21 '16 at 21:22
$table=array(); $table['cols'][]=array('label' => 'Data', 'type' => 'string'); foreach ($uniqueNetworks as $net) $table['cols'][]=array('label' => $net, 'type' => 'number'); $rows = array(); for ($j=0; $j<=$i; $j++) { $temp=array(); $temp[] = array('v' => (string) $uniqueData[$j]); foreach ($uniqueNetworks as $net) $temp[] = array('v' => (float) $p[$net][$j]); $rows[] = array('c' => $temp); } $table['rows'] = $rows; $jsonTable = json_encode($table); echo $jsonTable; — Giorgio Torassa, Oct 21 '16 at 22:15
Sorry if it is not easly readable. $uniqueNetworks contains a list of networks, $uniqueData contains a list of dates, $p[$net][$i] contains a value for a specific pair of network and data ($i is the number of dates) — Giorgio Torassa, Oct 21 '16 at 22:18
{"cols":[{"label":"Data","type":"string"},{"label":"OP1","type":"number"},{"label":"OP2","type":"number"},{"label":"OP3","type":"number"},{"label":"SFR(208-10)","type":"number"}],"rows":[{"c":[{"v":"2016-10-18"},{"v":1},{"v":0},{"v":1},{"v":0}]},{"c":[{"v":"2016-10-19"},{"v":1},{"v":0},{"v":1},{"v":0}]},{"c":[{"v":"2016-10-20"},{"v":1},{"v":0},{"v":1},{"v":0}]},{"c":[{"v":"2016-10-21"},{"v":1},{"v":0},{"v":1},{"v":0}]},{"c":[{"v":""},{"v":1},{"v":0},{"v":1},{"v":0}]}]} — Giorgio Torassa, Oct 21 '16 at 22:25

WhiteHat · Answer 1 · 2016-10-22T15:29:18.070

from the 'Data' it appears you want to use a DateRangeFilter vs. NumberRangeFilter

e.g. --> controlType: 'DateRangeFilter',

but if you want to use either, the first column needs something other than...
type: 'string'

need type: 'date' for 'DateRangeFilter'

type: 'number' for 'NumberRangeFilter'

also, if using a 'DateRangeFilter',
the JSON will need to be modified for google to recognize first column as a date

1. if you don't want to change the JSON, use a DataView to change the first column

see following working snippet...

google.charts.load('current', {
  callback: drawDashboard,
  packages: ['corechart', 'controls']
});

function drawDashboard() {
  var jsonData = {
    "cols":[
      {"label":"Data", "type":"string"},
      {"label":"OP1", "type":"number"},
      {"label":"OP2", "type":"number"},
      {"label":"OP3", "type":"number"},
      {"label":"SFR(208-10)", "type":"number"}
    ],
    "rows":[
      {"c":[{"v":"2016-10-18"},{"v":1},{"v":0},{"v":1},{"v":0}]},
      {"c":[{"v":"2016-10-19"},{"v":1},{"v":0},{"v":1},{"v":0}]},
      {"c":[{"v":"2016-10-20"},{"v":1},{"v":0},{"v":1},{"v":0}]},
      {"c":[{"v":"2016-10-21"},{"v":1},{"v":0},{"v":1},{"v":0}]},
      {"c":[{"v":""},{"v":1},{"v":0},{"v":1},{"v":0}]}
    ]
  };

  var data = new google.visualization.DataTable(jsonData);
  var view = new google.visualization.DataView(data);
  view.setColumns([{
    calc: function (data, row) {
      return new Date(data.getValue(row, 0))
    },
    type: 'date',
    label: 'Data'
  }, 1, 2, 3, 4]);

  var dataRangeSlider = new google.visualization.ControlWrapper({
    controlType: 'DateRangeFilter',
    containerId: 'filter_div',
    options: {
      filterColumnLabel: 'Data'
    }
  });

  google.visualization.events.addListener(dataRangeSlider, 'ready', function () {
    var state = dataRangeSlider.getState();
    console.log(state.lowValue, state.highValue);
  });

  var lineChart = new google.visualization.ChartWrapper({
    chartType: 'LineChart',
    containerId: 'chart_div',
    options: {
      title: 'UL Success per operatore',
      width: 1200,
      height: 900,
      chartArea: {
        left: 80,
        top: 50,
        width: '70%',
        height: '80%'
      }
    }
  });

  var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard_div'));
  dashboard.bind(dataRangeSlider, lineChart);
  dashboard.draw(view);
}

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="dashboard_div">
  <div id="filter_div"></div>
  <div id="chart_div"></div>
</div>

2. if you want to change the JSON, it should be formatted as in the following working snippet...

// NOTE: Months using this constructor are zero-based! (9=OCT)
{"v":"Date(2016, 9, 18)"}
or
{"v":null}

google.charts.load('current', {
  callback: drawDashboard,
  packages: ['corechart', 'controls']
});

function drawDashboard() {
  var jsonData = {
    "cols":[
      {"label":"Data","type":"date"},
      {"label":"OP1","type":"number"},
      {"label":"OP2","type":"number"},
      {"label":"OP3","type":"number"},
      {"label":"SFR(208-10)","type":"number"}
    ],
    "rows":[
      {"c":[{"v":"Date(2016, 9, 18)"},{"v":1},{"v":0},{"v":1},{"v":0}]},
      {"c":[{"v":"Date(2016, 9, 19)"},{"v":1},{"v":0},{"v":1},{"v":0}]},
      {"c":[{"v":"Date(2016, 9, 20)"},{"v":1},{"v":0},{"v":1},{"v":0}]},
      {"c":[{"v":"Date(2016, 9, 21)"},{"v":1},{"v":0},{"v":1},{"v":0}]},
      {"c":[{"v":null},{"v":1},{"v":0},{"v":1},{"v":0}]}
    ]
  };

  var data = new google.visualization.DataTable(jsonData);

  var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard_div'));

  var dataRangeSlider = new google.visualization.ControlWrapper({
    controlType: 'DateRangeFilter',
    containerId: 'filter_div',
    options: {
      filterColumnLabel: 'Data'
    }
  });

  var lineChart = new google.visualization.ChartWrapper({
    chartType: 'LineChart',
    containerId: 'chart_div',
    options: {
      title: 'UL Success per operatore',
      width: 1200,
      height: 900,
      chartArea: {
        left: 80,
        top: 50,
        width: '70%',
        height: '80%'
      }
    }
  });

  dashboard.bind(dataRangeSlider, lineChart);
  dashboard.draw(data);
}

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="dashboard_div">
  <div id="filter_div"></div>
  <div id="chart_div"></div>
</div>

The number of columns of the JSON data is not fixed (it depends on the result of a DB SELECT in the PHP script), how can I change the SetColumns? — Giorgio Torassa, Oct 21 '16 at 23:39
build the columns array dynamically -- also, i've seen timezone issues using `yyyy-MM-dd` format for creating dates from strings (google charts only -- some dates get created as the next or previous day) -- `MM/dd/yyyy` works correctly — WhiteHat, Oct 21 '16 at 23:41
also seen [problems with aligning to gridlines](http://stackoverflow.com/a/32430511/5090771) using `yyyy-MM-dd` — WhiteHat, Oct 21 '16 at 23:50
I tried you first solution but I get a "c.lowValue is Null" error with a slider starting from oNaN-NaN-NaN to oNaN-NaN-NaN — Giorgio Torassa, Oct 22 '16 at 14:31
made an edit to first example, added `'ready'` event -- seems to work fine here -- did you try building the columns array dynamically? -- edit question with latest code and i'll take a look... — WhiteHat, Oct 22 '16 at 15:30
made edit of the question with new code and JSON data. On the Console of my browser I see "null null". I haven't still used dynamic configuration of columns, I want to have the slide working before doing it. — Giorgio Torassa, Oct 23 '16 at 09:47
sorry missed this comment -- need to change first column type to a string -- since the date is not in valid format for google, the values get blown away when the data table is created -- which is why data view conversion fails -- leave the code the same but change json column definition to string --> `{"cols":[{"label":"Data","type":"string"},` — WhiteHat, Oct 26 '16 at 11:57

Google Chart Dashboard with Slider

1 Answers1