CODEWITHSUNDEEP
javascriptgoogle-visualization
Chris
Chris

Reputation: 97

Google Charts API - Dashboard: Hide rows in table

I am trying to create a dashboard with the Google Charts API.

The dashboard includes a ControlWrapper with the type ChartRangeFilter and two ChartWrapper (LineChart and Table). This setup is working fine, but I want to add one addition.

The LineChart shall show the whole dataset, the Table only a part of it. For example I have 10 rows and in the Table only the first 8 should be shown.

This is my current ControlWrapper:

control = new google.visualization.ControlWrapper({
 'controlType': 'ChartRangeFilter',
 'containerId': 'control',
 'options': {
   'filterColumnIndex': 0,
   'ui': {
     'chartType': 'LineChart',
     'chartOptions': {
       'chartArea': {'width': '95%'},
       'hAxis': {'baselineColor': 'none'}
     },
     'chartView': {
       'columns': [0,1]
     },
     'minRangeSize': 86400000
   }
 }
});

And this the Table:

chart = new google.visualization.ChartWrapper({
 'chartType': 'Table',
 'containerId': 'table',
 'options': {
        width: 470, 
        height: 340, 
        sortColumn: 0,
        sortAscending: false
 },
 'view': {
   'columns': [0, 1]
   //,'rows': [0,1,2,3,4,5,6]
 }
});

When I uncomment the row "//,'rows': [0,1,2,3,4,5,6]" in the table definition I get an error when I move the slider of the ControlWrapper:

Invalid row index 6. Should be in the range [0-5]

Is there a way to get this working?

The reason is that (in this example) in the last two rows are only data in column 3 which is only shown in the LineChart but not in the table.

Thanks in advance!

//edit:

Here is an example page: http://circlecount.com/test.chart.range.php

Here is the code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML+RDFa 1.1//EN" "http://www.w3.org/MarkUp/DTD/xhtml-rdfa-2.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml" version="XHTML+RDFa 1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.w3.org/1999/xhtml http://www.w3.org/MarkUp/SCHEMA/xhtml-rdfa-2.xsd" lang="de" xml:lang="de" dir="ltr" xmlns:og="http://ogp.me/ns#" >
<head>
  <script src="http://www.google.com/jsapi?key=" type="text/javascript"></script> 
  <script type="text/javascript">google.load("jquery", "1.7.1");</script>
 </head>
<body>
<script type="text/javascript">

// Load the Visualization API and the piechart package.
google.load('visualization', '1.0', {'packages':['corechart', 'table', 'controls']});

// Set a callback to run when the Google Visualization API is loaded.
google.setOnLoadCallback(fctDrawChart);

var control, chart;

function fctDrawChart() {
  //follower
  var data = new google.visualization.DataTable();
  var myTempDate = new Date();
  data.addColumn('date', 'Day');
  data.addColumn('number', 'Followers');


data.addRows(10);
data.setCell(0, 0, new Date('11/07/2011'));
data.setCell(0, 1, 11);
data.setCell(1, 0, new Date('11/08/2011'));
data.setCell(1, 1, 222);
data.setCell(2, 0, new Date('11/09/2011'));
data.setCell(2, 1, 1242);
data.setCell(3, 0, new Date('11/10/2011'));
data.setCell(3, 1, 1420);
data.setCell(4, 0, new Date('11/11/2011'));
data.setCell(4, 1, 1609);
data.setCell(5, 0, new Date('11/12/2011'));
data.setCell(5, 1, 1676);
data.setCell(6, 0, new Date('11/13/2011'));
data.setCell(6, 1, 1734);
data.setCell(7, 0, new Date('11/14/2011'));
data.setCell(7, 1, 1773);
data.setCell(8, 0, new Date('11/15/2011'));
data.setCell(8, 1, 1857);
data.setCell(9, 0, new Date('11/16/2011'));
data.setCell(9, 1, 1874);


var dataView = new google.visualization.DataView(data);
dataView.setColumns([0, 1]);



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

    control = new google.visualization.ControlWrapper({
     'controlType': 'ChartRangeFilter',
     'containerId': 'control',
     'options': {
       'filterColumnIndex': 0,
       'ui': {
         'chartType': 'LineChart',
         'chartOptions': {
           'chartArea': {'width': '95%'},
           'hAxis': {'baselineColor': 'none'}
         },
         'chartView': {
           'columns': [0,1]
         },
         'minRangeSize': 86400000
       }
     }
    });

    google.visualization.events.addListener(control, 'statechange', function() {
      $("#periodSelector").val("custom");
    });



    chart = new google.visualization.ChartWrapper({
     'chartType': 'Table',
     'containerId': 'table',
     'options': {
            width: 470, 
            height: 340, 
            sortColumn: 0,
            sortAscending: false
     },
     'view': {
       'columns': [0, 1]
       ,'rows': [0,1,2,3,4,5,6]
     }
    });

    chart2 = new google.visualization.ChartWrapper({
     'chartType': 'LineChart',
     'containerId': 'chart',
     'options': {
            width: 470, 
            height: 340, 
            sortColumn: 0,
            sortAscending: false
     },
     'view': {
       'columns': [0, 1]
     }
    });

    dashboard.bind(control, chart);
    dashboard.bind(control, chart2);
    dashboard.draw(data);

  }


</script>
  <div id='dashboard' >
    <div id='table' style='width:240px;height:400px;'></div>
    <div id='chart' style='width:240px;height:400px;'></div>
    <div id='control' style='width:840px;height:40px;'></div>
  </div>
</div>


</body>
</html>

What I want to get: - a Chart over the complete period (10 days) - a Table only over the first 7 days - a ControlWrapper over the complete period (10 days), controlling the chart and the table

I hope that makes my issue more clear. If not, please give me a note.

Thanks in advance!

Update: With the ideas of jmac I got it working with the following function:

google.visualization.events.addListener(control, 'statechange',
  function(event) {
    if (control.getState()["range"]["end"] > myLastRealDate) {
      var myRowsArr = new Array();
      var myTempDate = control.getState()["range"]["start"];
      for (var i=0;i<myLastRealRow-(myDateArr[(myTempDate.getMonth()+1)+"/"+myTempDate.getDate()+"/"+myTempDate.getFullYear()]);i++) {
        myRowsArr.push(i);
      }
      chart.setView({'rows': myRowsArr});
    }
    else {
      chart.setView({'rows': null});
    }
  });

You can find a working example here: http://circlecount.com/test.chart.range.php

Upvotes: 3

Views: 4393

Answers (2)

Chris
Chris

Reputation: 97

The problem can be solved with the following function:

google.visualization.events.addListener(control, 'statechange',
  function(event) {
    if (control.getState()["range"]["end"] > myLastRealDate) {
      var myRowsArr = new Array();
      var myTempDate = control.getState()["range"]["start"];
      for (var i=0;i<myLastRealRow-(myDateArr[(myTempDate.getMonth()+1)+"/"+myTempDate.getDate()+"/"+myTempDate.getFullYear()]);i++) {
        myRowsArr.push(i);
      }
      chart.setView({'rows': myRowsArr});
    }
    else {
      chart.setView({'rows': null});
    }
  }
);

The number of the row has to get saved in an array per date, for example:

myDateArr['11/7/2011'] = 0;

The row and the date of the "break" has also to get saved in variables:

myLastRealDate = new Date('11/14/2011');
myLastRealRow = 7;

Here is a working example: http://circlecount.com/test.chart.range.php

Upvotes: 1

jmac
jmac

Reputation: 7128

The issue you are running in to is that your table is setting a fixed number of rows while the selector can reduce the data table below that number of items.

One way to fix this would be to change the options, specifically ui.minRangeSize to be greater than 7 (the number of rows you want to show in the chart). That would make your code for the control:

control = new google.visualization.ControlWrapper({
 'controlType': 'ChartRangeFilter',
 'containerId': 'control',
 'options': {
   'filterColumnIndex': 0,
   'ui': {
     'chartType': 'LineChart',
     'chartOptions': {
       'chartArea': {'width': '95%'},
       'hAxis': {'baselineColor': 'none'}
     },
     'chartView': {
       'columns': [0,1]
     },
     'minRangeSize': 604800000
   }
 }
});

This prevents the error from occurring by stopping the range of the selector to go below 7 days.

If that isn't good enough for your application, you will have to do something tricky like this jsfiddle by asgallant. This method is rather confusing, but basically what it is doing is drawing a hidden table off the screen, using a change event (when you filter the chart) to determine what data is in the table, and create a new datatable from that filtered data.

You can then use the new sub-dataset to filter in any way you'd like.

Another option would be to change your rows filter on the the table from rows: [0, 1, 2, 3, 4, 5, 6] to a variable number of rows depending on the number of rows in the underlying table. So you could check the state of the slider (see documentation here) and create a dynamic array of rows based on how many rows are in the table (max 7).

All things considered, it is probably easiest to limit the range of the slider, as that will cause the least headaches.

Upvotes: 0

Related Questions