Google visualization dashboard Pie display

[sahKa]

PIN		TYPES		MINUse
00123		Sleep (21.51%)		40274
00123		Unknown (19.75%)		36993
00123		Exercise (13.89%)		26012
00123		Email & messaging (6.91%)		12943
45000		Sleep (0.47%)		882
45000		Unknown (0.77%)		1433
45000		Exercise (0.04%)		76
45000		Email & messaging (2.94%)		5503

Hi,

I have a Google visualization dashboard with a table (as a chart wrapper) a categoryfilter (as a controlwrapper).

The dashboard works fine.

My Table looks like this.

I have created pieChart using TYPES and MINUse values from the table and the PIN as Category picker.

As you can see for every PIN the TYPES are repeating . And in my pie chart I see repeating TYPEs. But what  I want is when the Category picker value is ‘Choose a Value’ the MINUse will be sum of all MINUse for that perticular TYPEs, for example, for ‘sleep’ Type ,instead of displaying  2 chart sections in the pie chart for  

00123		Sleep (21.51%)

 and

45000		Sleep (0.47%)

There would be only one  pie section for sleep (40274+882=41156).

 

And when I will select individual PIN I will see the TYPES related to this. (Which is working now.)

Hope I explained it properly.

I Really need help on this.

[Andrew Gallant]

I wrote an example that does this: http://jsfiddle.net/asgallant/R9Fz4/

In the example, I use a dummy chart as a intermediate between the control and the chart, but you won't need to since you already have a Table filling that role.  The important part is at the end:

// create event handler for updating the chart

google.visualization.events.addListener(dummy, 'ready', function () {

    // get the filtered DataTable

    var newData = dummy.getDataTable();

    // group Y by X

    var grouped = google.visualization.data.group(newData, [1], [{

        column:2,

        aggregation: google.visualization.data.sum,

        type:'number'

    }]);

    // set the DataTable for the chart

    chart.setDataTable(grouped);

    // draw the chart

    chart.draw();

});

// Create a dashboard

new google.visualization.Dashboard(document.getElementById('dashboard')).

// bin the control to the dummy chart

bind([control], [dummy]).

// Draw the dashboard

draw(data);

You need to replace instances of "dummy" with your Table's ChartWrapper and "chart" with your PieChart's ChartWrapper.  Bind only the Table (and any other visualizations that you don't need to group data for) to the Dashboard; the PieChart will get its data from the Table.

[saKw]

 

Hi Andrew,

 

Thanks for your reply.

 

 

I tried doing as per your suggestion but not getting the expected result. I am still getting TYPES repeated for default category picker (in my code it is All MDNs). I want it to be sum of MINUse group by TYPES.

 

I am posting my javascript, can you pls take a look and let me know where I am doing wrong?

 

In my table PIN ->mdn, TYPES->category and MINUse->kbUsage.

 

I have attached my pie chart pic. As you can see in the legend the 'Email & messaging' coming multiple times so is the 'Unknown'.

 

 

 

function drawChart() {

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

                          

                           data.addColumn('string', 'mdn');

                data.addColumn('string', 'category');

                data.addColumn('number', 'kbUsage');

                          

                           //alert("inside drawChart");

                for(var i=0;i<queryObjectLen;i++)

                {                              

                    var category = queryObject.empdetails[i].category;

                                  //alert(category);

                    var kbUsage = queryObject.empdetails[i].kbUsage;

                                  //alert(kbUsage);

                                  var mdn = queryObject.empdetails[i].mdn;

                                  //alert(mdn);                           

                    data.addRows([

                        [mdn,category,parseInt(kbUsage)]

                    ]);

                }

                var options = {

                    title: 'Category Information',

                                  legend: {position: 'right', textStyle: {color: 'black', fontSize: 9}},

                                  is3D: true,

                                  pieSliceText: 'percentage',

                                  sliceVisibilityThreshold:0,

                                  vAxis: {maxValue: 70},

                                  chartArea: {height: '85%', top: '13%'},

                                  tooltip: { text: 'percentage'},

                                  left:30,top:20,width:"100%",height:"100%"

                                  /*backgroundColor: {

                                                stroke: 'gray',

                                                strokeWidth: 1

                                         }*/

                };                      

                           var total = 0;

                           for (var i = 0; i < data.getNumberOfRows(); i++) {

                                  total += data.getValue(i, 2);

                           }

                           //alert(total);

                            var legend = document.getElementById("legend");

                           var legItem = [];

                           var colors = ['#e0440e', '#e6693e', '#ec8f6e', '#f3b49f', '#f6c7b6', '#f3aaaa', '#f6cccc'];

                           for (var i = 0; i < data.getNumberOfRows(); i++) {

                                  var label = data.getValue(i, 1);                             

                                  var value = data.getValue(i, 2);

                                  //alert(value);           

                                  var percent = Number(100 * value / total).toFixed(2);

                                  data.setFormattedValue(i, 1, label + ' (' + percent + '%)');                             

                           }

                          

                           var categoryPicker = new google.visualization.ControlWrapper({

                           controlType: 'CategoryFilter',

                           containerId: 'control3',

                           options: {

                                  filterColumnLabel: 'mdn',

                                  ui: {

                                         labelStacking: 'vertical',

                                         allowTyping: false,

                                         allowMultiple: false,

                                         caption : 'All MDNs'

                                  }

                           }

                       });

                      

                       // Define a table

                           var table = new google.visualization.ChartWrapper({

                                  chartType: 'Table',

                                  containerId: 'chart2',

                                  options: {

                                         width: '500px'

                                  }

                           });

                      

                        // Define a Pie chart

                     var pie = new google.visualization.ChartWrapper({

                           chartType: 'PieChart',

                           containerId: 'chart1',

                           options: options,

                           // Instruct the piechart to use colums 1 (Category) and 2 (KB)

                           // from the 'data' DataTable.

                           view: {

                                  columns: [1, 2]

                           }

                     });

                    

                     // get average values

                     google.visualization.events.addListener(table, 'ready', function () {

                           var group = google.visualization.data.group(table.getDataTable(), [1] [{

                                  // we need a key column to group on, but since we want all rows grouped into 1,

                                  // then it needs a constant value

                                  column: 2,

                                  aggregation: google.visualization.data.sum,

                                  type: 'number',

                                  modifier: function () {

                                         return 1;

                                  }

                           }]);

                           document.getElementById('avg').innerHTML = group.getValue(0, 1);

                           //document.getElementById('geomean').innerHTML = group.getValue(0, 2);

                     });

                           //var chart = new google.visualization.PieChart(document.getElementById('chart_div'));

                           var chart = new google.visualization.Dashboard(document.getElementById('dashboard')). bind([categoryPicker], [table,pie]);

                     google.visualization.events.addListener(chart, 'error', function (err) {

                       google.visualization.errors.removeError(err.id);

                       });

                           chart.draw(data);                       

               }

             

             

             

                      var queryObject="";

                      var queryObjectLen="";

                     // google.load("visualization", "1", {packages:["corechart"]});     

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

 

                      

                      $(document).ready(function(){

                                                 

                            $("#ajaxform").on('submit', function(e)

                                  {

                                  spinner.spin(target);

                                  $("#contact-submit").prop('disabled', true);

                                  $("#reset").prop('disabled', true);

                                  $("#print").prop('disabled', true);

                                  $("#saveAs").prop('disabled', true);

                                  e.preventDefault();

                                          // getting the values of both firstname and lastname       

                                          var beginDate = $('input[name="txtBeginDate"]').val();       

                                          var endDate = $('input[name="txtEndDate"]').val();  

                                         // var mdnVal = $('input[name="txtMsid"]').val(); 

                                          var val3gOr4g =  $('input:radio[name="3gOr4g"]:checked').val();

                                          var midVal= jQuery("textarea#txtMIDID").val();

                                          var DataMid = JSON.stringify({midVal: midVal});

                                          

                                          // posting the values       

                                          var dataString = 'beginDate=' + beginDate + '&endDate=' + endDate+ '&val3gOr4g=' + val3gOr4g+'&DataMid=' + DataMid;

                                         //alert(dataString)

                                          var formURL = $(this).attr("action");  

                                          

                                         //alert(formURL);                                     

                                         $.ajax(

                                         {

                                                url : formURL,                                               

                                                dataType:'json',

                                                data: dataString,

                                                success:function(data){

                                                       queryObject = eval('(' + JSON.stringify(data) + ')');

                                                       queryObjectLen = queryObject.empdetails.length;

                                                       //alert("here");

                                                       drawChart();

                                                       //ajaxindicatorstop();

                                                       spinner.stop();

                                                       document.getElementById("label").style.display = "inline";

                                                       $("#contact-submit").prop('disabled', false);

                                                       $("#reset").prop('disabled', false);

                                                       $("#print").prop('disabled', false);

                                                       $("#saveAs").prop('disabled', false);

                                                },

                                                error : function(data, xhr, type) {

                                                       var msg = data.responseText;

                                                       //alert($.trim(msg))

                                                       //ajaxindicatorstop();

                                                       spinner.stop();

                                                       $("#contact-submit").prop('disabled', false);

                                                       $("#reset").prop('disabled', false);

                                                       $("#print").prop('disabled', false);

                                                       $("#saveAs").prop('disabled', false);

                                            }         

                                         });

                                    e.preventDefault(); //STOP default action

                                    e.unbind(); //unbind. to stop multiple form submit.

                                  });          

                      });

                      

                     function getImgData(chartContainer) {

                           var chartArea = chartContainer.getElementsByTagName('svg')[0].parentNode;

                          

                           var svg = chartArea.innerHTML;

                           var doc = chartContainer.ownerDocument;

                           var canvas = doc.createElement('canvas');

                           canvas.setAttribute('width', chartArea.offsetWidth);

                           canvas.setAttribute('height', chartArea.offsetHeight);

                          

                          

                           canvas.setAttribute(

                                  'style',

                                  'position: absolute; ' +

                                  'top: ' + (-chartArea.offsetHeight * 2) + 'px;' +

                                  'left: ' + (-chartArea.offsetWidth * 2) + 'px;');

                           doc.body.appendChild(canvas);

                           canvg(canvas, svg);

                           var imgData = canvas.toDataURL("image/png");

                           canvas.parentNode.removeChild(canvas);

                           return imgData;

                       }

                      

                       function saveAsImg(chartContainer) {

                           var imgData = getImgData(chartContainer);

                           // triggering download doesn't work in IE 9, so we open the img in a <img> tag instead

                           // uses jQuery for browser detection

                          

                           jQuery.browser={};

                           (function(){

                                  jQuery.browser.msie=false;

                                  jQuery.browser.version=0;

                                  if(navigator.userAgent.match(/MSIE ([0-9]+)\./)){

                                         jQuery.browser.msie=true;

                                         jQuery.browser.version=RegExp.$1;}

                           })();

                          

                           if ($.browser.msie) {

                                  if ($.browser.version == 9) {

                                         var w = open();

                                         var img = w.document.createElement('img');

                                         img.src = imgData;

                                         w.document.body.appendChild(img);

                                         setTimeout(function() {

                                                w.alert('Right-click image and select "Save As"');

                                         }, 500);

                                  }

                                   else if ($.browser.version < 9) {

                                         alert('Saving charts is not possible in versions of IE older than 9');

                                  }

                           }

                           else {

                                  // Replacing the mime-type will force the browser to trigger a download

                                  // rather than displaying the image in the browser window.

                                  window.location = imgData.replace('image/png', 'image/octet-stream');

                           }

                     }

 

             

 

    </script>

 

[Andrew Gallant]

You need to calculate the data for your PieChart in the "ready" event handler for your Table:

google.visualization.events.addListener(table, 'ready', function () {

    var newData = table.getDataTable();

    var group = google.visualization.data.group(newData, [1] [{

        // we need a key column to group on, but since we want all rows grouped into 1,

        // then it needs a constant value

        column: 2,

        aggregation: google.visualization.data.sum,

        type: 'number',

        modifier: function () {

            return 1;

        }

    }]);

    document.getElementById('avg').innerHTML = group.getValue(0, 1);

    var pieData = google.visualization.data.group(newData, [1], [{

        column:2,

        aggregation: google.visualization.data.sum,

        type:'number'

    }]);

    pie.setDataTable(pieData);

    pie.draw();

});

and you need to remove the PieChart from the Dashboard.bind call:

var chart = new google.visualization.Dashboard(document.getElementById('dashboard')). bind([categoryPicker], [table]);

[saKw]

Hi Andrew,

 

When I remove the PieChart from the Dashboard.bind call, i dont see pie chart image in my page. and also i get an error that says "

Invalid column index 2. Should be an integer in the range [0-1].

"

Thanks

                                  containerId: 'chart2',</font

...

[Andrew Gallant]

Can you post the current version of the code you are using, along with a sample queryObject I can use to test this?

                      &nbs

...

[saKw]

Code--

 

 

     containerId: 'chart2',
     options: {
      width: '500px'
     }
    });
    
      // Define a Pie chart
   var pie = new google.visualization.ChartWrapper({
    chartType: 'PieChart',
    containerId: 'chart1',
    options: options,
    // Instruct the piechart to use colums 1 (Category) and 2 (KB)
    // from the 'data' DataTable.
    view: {
     columns: [1, 2]
    }
   });
   
   // get average values

   google.visualization.events.addListener( table, 'ready', function () {
    var newData = table.getDataTable();
    var group = google.visualization.data.group(newData , [1],[{

     // we need a key column to group on, but since we want all rows grouped into 1,
     // then it needs a constant value
     column: 2,
     aggregation: google.visualization.data.sum,
     type: 'number',
     modifier: function () {
      return 1;
     }
    }]);

    //document.getElementById('avg').innerHTML = group.getValue(0, 1);

    var pieData = google.visualization.data.group(newData, [1], [{
     column:2,
     aggregation: google.visualization.data.sum,
     type:'number'
    }]);
    pie.setDataTable(pieData);
    pie.draw();

    //document.getElementById('geomean').innerHTML = group.getValue(0, 2);
   });
   
   
   
   
    //var chart = new google.visualization.PieChart(document.getElementById('chart_div'));

    var chart = new google.visualization.Dashboard(document.getElementById('dashboard')). bind([categoryPicker], [table]);

---------------------------------------------------------------------------------------------------

HTML:

 

<div id="dashboard">
    <table>
        <tbody>
            <tr style="vertical-align: top;">
                <td style="width: 300px; font-size: 0.9em;">
                    <div id="control1"></div>
                    <div id="control2"></div>
                    <div id="control3"></div>
                </td>
                <td style="width: 600px;">
                    <div style="float: left;display: none;" id="chart2"></div>
                    <div style="float: left;" id="chart1"></div>
                </td>
            </tr>
        </tbody>
    </table>
    <span id="label">Total KB: </span><span id="avg"></span><br/>
</div>

-----------------------------------

I am using Json, i guess queryObject maens the json output. Please let me know if I am wrong.

 

{"empdetails":[{"category":"Unknown","kbUsage":22639,"mdn":"7705005996"},{"category":"Email & messaging","kbUsage":19371,"mdn":"7705005996"},{"category":"Social","kbUsage":8196,"mdn":"7705005996"},{"category":"Cloud/file sharing","kbUsage":3961,"mdn":"7705005996"},{"category":"Uncategorized","kbUsage":2705,"mdn":"7705005996"},{"category":"Downloads & Marketplaces","kbUsage":2525,"mdn":"7705005996"},{"category":"Web & Apps","kbUsage":512,"mdn":"7705005996"},{"category":"Maps & Navigation","kbUsage":64,"mdn":"7705005996"},{"category":"Communication and Device Services","kbUsage":10,"mdn":"7705005996"}]}

 

 

 

Thanks

[Andrew Gallant]

First, you need to remove the "view" parameter from the PieChart's ChartWrapper - it is not needed since you are constructing a new DataTable for the PieChart to draw from.

var pie = new google.visualization.ChartWrapper({

    chartType: 'PieChart',

    containerId: 'chart1',

    options: options

});

Second, I see that you are using a document ready event handler to set up your AJAX request, which then calls the drawChart function.  The problem with this approach is that it is possible for document ready to fire, the user to trigger the AJAX request, and the AJAX request to return all before the Visualization API finishes loading; if this happens, your code will throw errors trying to render the charts, or the chart will render improperly.  It seems unlikely, but I've dealt with other users who have run into this exact problem before.  If you change from a document ready event handler to a callback from the google loader, you can avoid this problem:

google.load('visualization', '1', { 'packages': ['corechart', 'controls'], callback: function () {

     // code from your document ready handler

}});

[saKw]

Hi Andrew,

 

Thank you so much. I changed my code and it worked perfectly. But still I am seeing another problem.

I have code to calculate total kbUsage for perticular MDN. That was working before. But now it just gives total no of rows returned from the DB, instead of total of all kbUsage.

 

var group = google.visualization.data.group(newData , [{

     // we need a key column to group on, but since we want all rows grouped into 1,
     // then it needs a constant value
     column: 2,
     aggregation: google.visualization.data.sum,
     type: 'number',
     modifier: function () {
      return 1;
     }

    }],
     [{
     // get the total of all KBUsage
     column: 2, //instead of column 2, if i pass 0 (MDN) or (1) category, i get the actual values of that column in my next alert.
     label: 'Total Byte',
     type: 'number',
     aggregation: function (values) {
      var product = 0;
      var n = values.length;
      for (i = 0; i < n; i++) {
       //alert(values[i]); //This alert returns 1 everytime for  column: 2, instead of actual value
       product = product + values[i];
      }
      return product;

     }
    }]);
    document.getElementById('avg').innerHTML = group.getValue(0, 1);

 

Thanks

[Andrew Gallant]

That is a strange effect; I wonder if it is using the modified value from the grouping column?  Try changing the column index of the grouping column (since it doesn't really matter which one you use, in this case):

var group = google.visualization.data.group(newData , [{

    // we need a key column to group on, but since we want all rows grouped into 1, 

    // then it needs a constant value

    column: 0,

    aggregation: google.visualization.data.sum,

    type: 'number',

    modifier: function () {

        return 1;

    }

}], [{

    // get the total of all KBUsage 

    column: 2, //instead of column 2, if i pass 0 (MDN) or (1) category, i get the actual values of that column in my next alert.

    label: 'Total Byte',

    type: 'number',

    aggregation: function (values) {

        var product = 0;

        var n = values.length;

        for (i = 0; i < n; i++) {

            //alert(values[i]); //This alert returns 1 everytime for  column: 2, instead of actual value

            product = product + values[i];

        }

        return product;

    }

}]);