Drill down functionality for column charts

[Raji]

Here is the format of my chart: http://jsfiddle.net/asgallant/zwPuU/

I would like to add drill down functionality - Clicking on USA should yield another chart with US states etc. All the charts would be clickable to a drill down version. 

I understand I have to use the selection event, could you give me an example of that?

Thanks in advance!

[asgallant]

Here's an example: http://jsfiddle.net/asgallant/x5Eth/

[Raji]

Thank you for the reply. 

In my case, the x axis labels would change depending on the column clicked. 

for eg. If "US" was clicked, the new chart should show x axis : East Coast, Pacific, West Coast

If Europe was clicked, the x axis would show European countries etc.

can this be done?

thank you! 

[asgallant]

Yes, it would just require changing the labels in the second column in that example.

[Raji]

Awesome, I got it, thank you very much for your help!!!! 

[Doug Elder]

Do you have an example using dynamic json data? I'm new to this and don't see how you'd dynamically set the view. Any help would be appreciated.

[asgallant]

The important view for the drilldown is this one:

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

    var sel = chart.getChart().getSelection();

    var country = data.getValue(sel[0].row, 0);

    var rows = data2.getFilteredRows([{column: 0, value: country}]);

    var view = chart2.getView();

    view.rows = rows;

    chart2.setView(view);

    chart2.draw();

});

and the only requirement here is that the drilldown and filtering happens on column 0 in both DataTables (and you can change that to be a different column, if necessary).  Whether the data is dynamic or not is irrelevant.

[Doug Elder]

Almost there. I have both tables showing. The second table shows what I put in as the default row but when I click on a bar it freezes and the second table never updates. The code below is what controls that correct? Only difference I can really see is I have now views set up on the first chart. Since I'm new to this not even sure how to debug this issue.

[asgallant]

Post your code (or even better, a jsfiddle example) that demonstrates the problem, and I'll take a look.

[Doug Elder]

Must be worse at this than I though. I can't get it to work in jsfiddle. Here it is : http://jsfiddle.net/delder818/N2WX2/6/
I commented out my json stuff and made up data. What is confusing to me is the view on data. I could have 5 sets of data or 25 in my chart. Here is what it looks like now (not based on the jsfiddle code)

[Doug Elder]

Here is the chart image.

--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/NqSGcrqOjYc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/d/optout.

[Doug Elder]

I actually think I have it working with the exception of how to set the default row to display and generate the title for chart 2 based on the store name. I can change the chart 2 name on the click of the graph but am having trouble setting it up coming in. If I new how to set the default row that would probably give me insight into that.

On Tuesday, March 18, 2014 8:44:20 AM UTC-4, Doug Elder wrote:

Here is the chart image.

To unsubscribe from this group and all its topics, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.

[asgallant]

Your fiddle was missing the google loader's jsapi script link in the "Resources" panel.  Fixed here: http://jsfiddle.net/asgallant/N2WX2/7/

As far as setting the default value, what criteria do you want to use to determine the default?  Should it be the first store in the DataTable, or the store with the highest value in "Value 1", or something else? 

To post to this group, send email to google-visua...@googlegroups.com.

[Doug Elder]

Yes it should be the first store in the data table and the Title should be Division Breakdown for 'store name here'. Thank you very much for your help. Your example has been extremely helpful. I actually found another example of yours that showed me how to change the name on the change. I just can't seem to figure out how to set the default. Thanks again.

[asgallant]

Pull the data you want from the DataTable via the #getValue method.  To get the store name in the first row of "data", it would be:

data.getValue(0, 0);

You can use this to set the chart title and to set the filter on the rows.  I updated the example with this: http://jsfiddle.net/asgallant/N2WX2/8/

[Doug Elder]

Perfection! This really gets my feet under me and the chart looks great. It will add just what my customers are looking for. Thank you again for all the help.

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[Doug Elder]

Please help, I'm trying to do the same thing from a pie chart to a bar graph and none of it transfers over between it. Here is my jsFiddle: http://jsfiddle.net/delder818/QXJ7Q/2/
I've tried changing it to various getData, getColumnId, GetColumnLabel. I can't seem to figure out the difference in the pie chart.

To unsubscribe from this group and all its topics, send an email to google-visualization-api+unsubscr...@googlegroups.com.

To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/NqSGcrqOjYc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-visualization-api+unsub...@googlegroups.com.

To post to this group, send email to google-visualization-api@googlegroups.com.

[asgallant]

Your code is quite close to correct, there were just a few things that needed fixing.  Try this: http://jsfiddle.net/asgallant/QXJ7Q/3/

[Doug Elder]

Thanks once again. Is there any way to tell if you have clicked on the 1st, 2nd, 3rd etc slice?

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[asgallant]

The row returned by sel[0].row tells you which slice was clicked on.

[Doug Elder]

I can get the "name" of the pie slice from that yes, but since this is all dynamic I'm after the color or the slice. Slice one is blue, slice two is red... Can you pull that out of there somehow?

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[asgallant]

You can get the name of the slice by referencing the appropriate column in the DataTable (usually column 0).  Getting the slice color is a bit more difficult.  Are you setting the colors or slices.<slice index>.color options?

[Doug Elder]

Yes my rough code was as follows, but row didn't return what I thought.
if (sel[0].row == 1) {
   chart5.setOption('colors', ["#0066FF", "#6495ED", "#87CEFA"]);
}else if (sel[0].row == 2){
    chart5.setOption('colors', ["#D52D12", "#B22222", "#FF6A6A" ]);
}else if (sel[0].column == 3){
    chart5.setOption('colors', ["#FF9700"]);
}else if (sel[0].column == 4){
    chart5.setOption('colors', ["#26931C"]);
} else if (sel[0].column == 5){
    chart5.setOption('colors', ["#6600CC"]);
}

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[asgallant]

What do you want to do with the color?

The colors option sets the colors for the pie slices to use, where the first slice uses the first color in the array, the second slice uses the second color, etc.

[Doug Elder]

I'm going to set the bar colors either shades of the pie slice or draw put a border the color of the pie slice selected. I actually just got the shades working now. I have a feeling the border is going to be trickier.

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[Doug Elder]

Quick question. The pie chart click works on my laptop across all the browsers. When I deploy it to our server Microsoft Server 2003 it will not work on IE 9. Is this a limitation of the server? It works on Firefox, Chrome and Safari. Someone mentioned that I needed to be running IE 9 on the server for it to work on IE. Thoughts?

[asgallant]

The version of IE on your server is irrelevant.  What part doesn't work?  Do the charts not draw, or is it just the interaction between the charts that doesn't work?  Are there any error messages in IE9's developer console (open the page, hit F12 to open the developer tools, go to the console tab, and refresh the page to see any error messages)?

[Doug Elder]

This is the error it is giving me. I don't get the error from my pc only when it it running on the server.

SCRIPT5007: Unable to get value of the property 'row': object is null or undefined

Here is the line of code in question.
 var division = data2.getValue(sel[0].row, 0);

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[Doug Elder]

Based off of your help with me I have two listeners on the page. The first is a line cart that displays a bar chart the second is a pie chart that displays another bar chart. How can it work on my pc and not the server for only IE? It works on Firefox and Chrome.

google.visualization.events.addListener(chart3, 'select', function () {
          var sel = chart3.getChart().getSelection();
          var customer = data3.getColumnLabel(sel[0].column);
          var rows = data4.getFilteredRows([{column: 0, value: customer}]);
          var view = chart4.getView();
          var title = 'Division Breakdown for ' + customer;
          view.rows = rows;
            if (sel[0].column == 1) {
                chart4.setOption('colors', ["#0066FF"]);
            }else if (sel[0].column == 2){
                chart4.setOption('colors', ["#D52D12"]);

            }else if (sel[0].column == 3){

                  chart4.setOption('colors', ["#FF9700"]);

            }else if (sel[0].column == 4){

                  chart4.setOption('colors', ["#26931C"]);

            } else if (sel[0].column == 5){

                chart4.setOption('colors', ["#6600CC"]);
            }
          chart4.setOption('title', title);
          chart4.setView(view);
          chart4.draw();
        });

      google.visualization.events.addListener(chart2, 'select', function () {
          var sel2 = chart2.getChart().getSelection();
              var division = data2.getValue(sel2[0].row, 0);
              var rows = data5.getFilteredRows([{
                  column: 0,
                  value: division
              }]);
              var view = chart5.getView();
              var title = 'Division Breakdown for ' + division;
              view.rows = rows;
             
              chart5.setOption('title', title);
              chart5.setView(view);
              chart5.draw();
      });

[asgallant]

This likely doesn't have anything to do with the location the page is run from.  You are trying to access the first element in the array of selected elements, but the array would be empty if the user deselects a point, which would throw that error.  You need to test the length of the array before you try to access its contents:

google.visualization.events.addListener(chart3, 'select', function () {

    var sel = chart3.getChart().getSelection();

    if (sel.length) {

        var customer = data3.getColumnLabel(sel[0].column);

        var rows = data4.getFilteredRows([{column: 0, value: customer}]);

        var view = chart4.getView();

        var title = 'Division Breakdown for ' + customer;

        view.rows = rows;

        if (sel[0].column == 1) {

            chart4.setOption('colors', ["#0066FF"]);

        }else if (sel[0].column == 2){

            chart4.setOption('colors', ["#D52D12"]);

            

        }else if (sel[0].column == 3){

            chart4.setOption('colors', ["#FF9700"]);

            

        }else if (sel[0].column == 4){

            chart4.setOption('colors', ["#26931C"]);

            

        } else if (sel[0].column == 5){

            chart4.setOption('colors', ["#6600CC"]);

        }

        chart4.setOption('title', title);

        chart4.setView(view);

        chart4.draw();

    }

});

google.visualization.events.addListener(chart2, 'select', function () {

    var sel2 = chart2.getChart().getSelection();

    if (sel2.length) {

        var division = data2.getValue(sel2[0].row, 0);

        var rows = data5.getFilteredRows([{

            column: 0,

            value: division

        }]);

        var view = chart5.getView();

        var title = 'Division Breakdown for ' + division;

        view.rows = rows;

        

        chart5.setOption('title', title);

        chart5.setView(view);

        chart5.draw();

    }

});

[Doug Elder]

I purposely removed the test for the length. Until I did that I couldn't get it to throw any error. Also it's the listener on Chart2 that isn't working.(The pie chart) The line chart listener is fine. It comes back with that error when I click on the pie slice. If I have that check in there I get no error messages. There are no error messages in Firefox or Chrome. I'm completely baffled. Like I said any browser from my PC and I'm good. IE (which is our company standard) on the server is the only one that doesn't work.

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[asgallant]

What happens (or does not happen) in IE?  Is the page publicly accessible?  If so, I'll give it a test run to see if I can figure it out.  If not, attach the full HTML/javascript code and some sample data and I'll try testing that.

[Doug Elder]

It is not the chart stuff it's the jQuery. Below it the mock for the html. When I click on the piechart it never hits my alert. Why there is a difference between my pc and the server I don't know. I'm trying to find a solution. Thanks again for your help. You have been amazing and I'm learning tons. You have been an invaluable resource.

<html:html>

<html:form>

<html:body>

<div id="content" align="center">
          <table border="1" id="mainTable" cellspacing="1" cellpadding="1" style="border-collapse: collapse;"  align="center" bordercolor="black" >
                        <tr>
                            <td>
                                test area for main table.
                            </td>
                        </tr>
                    </table>
                </div>
                    <table>
                        <tr>
                            <td ><div id="piechart" style="width: 600px; height: 400px;" ></div></td>
                            <td><div id="linechart" style="width: 600px; height: 400px;"></div></td>
                        </tr>
                        <tr>
                            <td>
                                <div id="columnchart3" style="width: 600px; height: 400px;display: none;"></div>
                                <div id="columnchart" style="width: 600px; height: 400px;"></div>
                            </td>
                            <td><div id="columnchart2" style="width: 600px; height: 400px;"></div></td>
                        </tr>
                        <tr>
                            <td>
                               
                            </td>
                        </tr>
                    </table>

</html:form>

<script type="text/javascript">
$(document).ready(function() {
    alert('test1');
    $("#columnchart").show();
    $("#columnchart3").hide();
});

$("#piechart").click(function(){
    alert('test2');
    $("#columnchart").hide();
    $("#columnchart3").show();
});

$("#mainTable").click(function(){
    alert('test3');
    $("#columnchart").show();
    $("#columnchart3").hide();
});

</script>

</body>
</html:html>

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[asgallant]

Ahh, I see.  I think the problem here is that the Visualization API captures all click events in the chart and prevents them from bubbling up to the container div, so the click event is never registered there.  I don't know why that would be different between your client and server (seems like it should be broken in both places).  Try using "select" event handlers for your charts instead:

google.visualization.events.addListener(myChart, 'select', function () {

    alert('test2');

    $("#columnchart").hide();

    $("#columnchart3").show();

});

[Doug Elder]

It was actually more insidious than that. IE 9 has a check box that intranet sites run in compatibility mode which in turn breaks jQuery. All five charts worked fine until I added the code to display hide a couple of the charts. I went in unclicked the check box and it worked fine. Thanks you again for your help. Really appreciate it.

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[Rk MooRthy Rajendran]

Thanks, Its working fine

-Rk,MooRthy

[Rk MooRthy Rajendran]

Hi,

It was very useful to me, very thank to u guys.

Now i have to add stacked bar chart for my requirement.

Can u any one help me regarding that..???

-Rk.MooRthy

[Andrew Gallant]

Set the "isStacked" option to true.

[Rk MooRthy Rajendran]

Hi Andrew,

Thanks for your valuable reply.

Now i have to put drill down stacked bar chart as percentage wise.

Do you have any idea or sample code for regarding this..???

Thanks in Advance..

-Rk.MooRthy

[Andrew Gallant]

You can use a DataView to calculate each data series as a percent of the total:

var columns = [0];

for (var i = 1; i < data.getNumberOfColumns(); i++) {

    columns.push({

        type: 'number',

        label: data.getColumnLabel(i),

        calc: (function (x) {

            return function (dt, row) {

                var val = dt.getValue(row, x);

                var total = 0;

                for (var i = 1; i < dt.getNumberOfColumns(); i++) {

                    total += dt.getValue(row, i);

                }

                var pct = val / total;

                var formattedValue = dt.getFormattedValue(row, x) + ' (' + (pct * 100).toFixed(2) + '%)';

                return {v: pct, f: formattedValue};

            };

        })(i)

    });

}

var view = new google.visualization.DataView(data);

view.setColumns(columns);

[Rk MooRthy Rajendran]

Hi Andrew,

Thanks for your valuable reply..

-Rk.Moorthy

[Rk MooRthy Rajendran]

Hi Andrew,

Do you have any idea to plot the above chart which i have attached..(PFA)

The chart contains "Drill down functionality" and "Dual Y-Axis".

Thanks in advance..

-Rk.MooRthy

[Andrew Gallant]

You should be able to replicate that chart.  Use a ComboChart to combine both columns and lines.  Use the series.<series index>.targetAxisIndex option to assign each data series to either the left (axis index 0) or right (axis index 1) y-axes.  Use the vAxes.<axis index> option to set options for the axes (which work the same as the vAxis options).  The drilldown stuff should work exactly the same.

[Rk MooRthy Rajendran]

Hi Andrew,

Its working fine. Thank you very much for your valuable reply...

-Rk.MooRthy

[Rk MooRthy Rajendran]

Hi Andrew,

I have to plot charts using Google API with web service data, The web service result is in Json format.

So need to convert the Json format into Multi dimensional array for render the chart.

This is my web service data : 

[{"categories1": ["TOTAL","GROUND FLOOR-GOLD","FIRST FLOOR-GOLD","FIRST FLOOR-DIAMOND","FIRST FLOOR-PLATINUM","SECOND FLOOR-GOLD","YOUNGONES-GOLD","YOUNGONES-DIAMOND","YOUNGONES-PLATINUM","YOUNGONES-SILVER","SILVERMINE-GF","SILVERMINE-FF","SILVER JEWELLERY","HANDICRAFT"], "convToSales1":[77131,22556,3998,1187,315,5626,6683,151,48,3427,19984,4972,7042,1142],"NotConvSales1":[10073,2751,630,266,67,1001,955,68,15,221,2629,1094,321,55]}]

Need to convert the above json into following format :

[['Categories','ConvToSales','NotConvSales'],

 ['YOUNGONES-DIAMOND',126,514],['YOUNGONES-PLATINUM',555,552],

 ['YOUNGONES-SILVER',645,154],['SILVERMINE-GF',5029,687],

 ['SILVER JEWELLERY',164,179],['HANDICRAFT',335,212]]

Do you have any idea to converting the data ????

Thanks in Advance..

-Rk.MooRthy

[Andrew Gallant]

Try this:

var rawData = [{...}]; // your web service data

var myData = [['Categories','ConvToSales','NotConvSales']];

for (var i = 0; i < rawData[0].categories1.length; i++) {

    myData.push([

        rawData[0].categories1[i],
        rawData[0].convToSales1[i],
        rawData[0].NotConvToSales1[i]

    ]);

}

var data = google.visualization.arrayToDataTable(myData);

[Rk MooRthy Rajendran]

Hi Andrew,

Thanks for your reply.

-Rk.MooRthy

[Rk MooRthy Rajendran]

Hi Andrew, 

Can we have zoom options in Google charts... How to set it..

-Rk.MooRthy

[Jon Orwant]

Some charts support an "explorer" option, which lets you enable zoom; search for it on the doc page for the appropriate chart. (And the Map Chart has standard Google Maps zooming.)

Jon

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualizati...@googlegroups.com.

[sourav mondal]

Here I have tried a little, may this help you friends http://dotnetawesome.com/mvc/how-to-implement-drill-down-google-chart-in-mvc4

[rajan...@knowarth.com]

Hi is this a free chart. actually i am new to this field so want to know that should i use it commercially for free or its chargeable?

On Tuesday, January 15, 2013 at 5:41:13 AM UTC+5:30, asgallant wrote:

Here's an example: http://jsfiddle.net/asgallant/x5Eth/