Filter Google Bar Chart based on multiple ASP.Net DropDownList value using jQuery

Last Reply one year ago By dharmendr

Posted one year ago

i want to populate the data from the multiple dropdown to the charts, based upon the selection of the values in the multiple dropdown lists the charts should display. can anyone help me on this?

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="http://cdnjs.cloudflare.com/ajax/libs/json2/20130526/json2.min.js"></script>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
    $(document).ready(function () {        
        google.charts.load("current", { "packages": ["corechart"] });        
        $('btnDisplay').on('click', function () {
            var ddlregion = $("#ddlregion").find("option:selected").value();
            var ddlbu = $("#ddlbu").find("option:selected").value();
            var ddltech = $("#ddltech").find("option:selected").text();
            var ddlyear = $("#ddlyear").find("option:selected").text();
            var ddlmonth = $("#ddlmonth").find("option:selected").text();
 
            //var ddlCountry2 = $("#ddlCountry2").find("option:selected").text();
            drawChart();
            return false;
        }); 
        
        function drawChart() {
            var obj = {};
            obj.ddlregion = ddlregion;
            obj.ddlbu = ddlbu;
            obj.ddltech = ddltech;
            obj.ddlyear = ddlyear;
            obj.ddlmonth=ddlmonth
            $.ajax({
                url: "repeatrate.aspx/GetChartData",
                data: JSON.stringify(obj),
                type: "POST",
                contentType: "application/json; charset=utf-8",
                dataType: "json"
            }).done(function (data) {
                var info = new google.visualization.DataTable();
                info.addColumn('string', 'DATE_ID');
                info.addColumn('number', 'KPI_Value');
                var json = data.d;
                json.forEach(function (row) {
                    info.addRow([row[0],row[1]]);
                    
                });
                var options = {
                    'title': Repeat_Rate,
                    'width': 400,
                    'height': 300
                };
                var chart = new google.visualization.BarChart(document.getElementById('chart_div'));
                chart.draw(info, options);
            }).fail(function (err) {
                console.log(err);
            });
        }
    });
</script>

 

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> GetChartData(string ddlregion, string ddlbu, string ddltech,string ddlyear)
        {
            List<object> chartData = new List<object>();
            string query = "SELECT DATE_ID,KPI_Value,TARGET_YEAR1,BASELINE,WORST_VALUE from rr_result";
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    cmd.Parameters.AddWithValue("@ddlregion", ddlregion);
                    cmd.Parameters.AddWithValue("@ddlbu", ddlbu);
                    cmd.Parameters.AddWithValue("@ddltech",ddltech);
                    cmd.Parameters.AddWithValue("@ddlyear", ddlyear);
                    //cmd.Parameters.AddWithValue("@ddlmonth", ddlmonth);
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            chartData.Add(new object[] { sdr["DATE_ID"], sdr["KPI_Value"] });
                        }
                    }
                    con.Close();
                    return chartData;
                }
            }
        }

 

Posted one year ago

Hi gsmm,

Since you are using Bar chart you need some modification in your code.

Also there are lots of mistake in your code.

gsmm says:
 $('btnDisplay').on('click', function () {

Here you jquery selector for button click event is wrong.

gsmm says:
drawChart();

 In your function you are not passing any dropdown value.

gsmm says:
string query = "SELECT DATE_ID,KPI_Value,TARGET_YEAR1,BASELINE,WORST_VALUE from rr_result";

Your select query doesnot have where condition.

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" />
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="http://cdnjs.cloudflare.com/ajax/libs/json2/20130526/json2.min.js"></script>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        google.charts.load('current', { 'packages': ['corechart'] });
        $('#btnDisplay').on('click', function () {
            var ddlCountry1 = $("#ddlCountry1").find("option:selected").text();
            var ddlCountry2 = $("#ddlCountry2").find("option:selected").text();
            drawChart(ddlCountry1, ddlCountry2);
            return false;
        });
        function drawChart(country1, country2) {
            var obj = {};
            obj.country1 = country1;
            obj.country2 = country2;
            $.ajax({
                url: "Default.aspx/GetChartData",
                data: JSON.stringify(obj),
                type: "POST",
                contentType: "application/json; charset=utf-8",
                dataType: "json"
            }).done(function (data) {
                var options = {
                    'title': $('#ddlCountry1').find("option:selected").val() + "  And  " + $('#ddlCountry2').find("option:selected").val() + ' City Distribution',
                    'width': 400,
                    'height': 300,
                    bar: { groupWidth: "50%" },
                    legend: { position: "none" },
                    isStacked: true
                };
                var data = google.visualization.arrayToDataTable(data.d);
                var chart = new google.visualization.BarChart(document.getElementById('divChart'));
                chart.draw(data, options);
            }).fail(function (err) {
                console.log(err);
            }).error(function (err) {
                console.log(err);
            });
        }
    });
</script>
<asp:DropDownList ID="ddlCountry1" runat="server" AutoPostBack="true">
</asp:DropDownList>
<asp:DropDownList ID="ddlCountry2" runat="server" AutoPostBack="true">
</asp:DropDownList>
<asp:Button Text="Submit" runat="server" ID="btnDisplay" CssClass="btn btn-link" />
<hr />
<div class="row">
    <div class="col-lg-6">
        <div id="divChart" style="width: 900px; height: 500px;">
        </div>
    </div>
</div>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = new DataTable();
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT DISTINCT ShipCountry FROM Orders", con))
            {
                cmd.CommandType = CommandType.Text;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
        }
        ddlCountry1.Items.Clear();
        ddlCountry1.Items.Insert(0, new ListItem("--Select--", "0"));
        ddlCountry1.AppendDataBoundItems = true;
        ddlCountry1.DataTextField = "ShipCountry";
        ddlCountry1.DataValueField = "ShipCountry";
        ddlCountry1.DataSource = dt;
        ddlCountry1.DataBind();

        ddlCountry2.Items.Clear();
        ddlCountry2.Items.Insert(0, new ListItem("--Select--", "0"));
        ddlCountry2.AppendDataBoundItems = true;
        ddlCountry2.DataTextField = "ShipCountry";
        ddlCountry2.DataValueField = "ShipCountry";
        ddlCountry2.DataSource = dt;
        ddlCountry2.DataBind();
    }
}

[WebMethod]
public static List<object> GetChartData(string country1, string country2)
{
    List<object> chartData = new List<object>();
    chartData.Add(new object[] { "ShipCity", "TotalOrders" });
    string query = "SELECT TOP 5 ShipCity, COUNT(orderid) TotalOrders FROM Orders WHERE ShipCountry BETWEEN @Country1 AND @Country2 GROUP BY ShipCity";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("@Country1", country1);
            cmd.Parameters.AddWithValue("@Country2", country2);
            con.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    chartData.Add(new object[] { sdr["ShipCity"], sdr["TotalOrders"] });
                }
            }
            con.Close();
            return chartData;
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim dt As DataTable = New DataTable()
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(constr)
            Using cmd As SqlCommand = New SqlCommand("SELECT DISTINCT ShipCountry FROM Orders", con)
                cmd.CommandType = CommandType.Text
                Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                da.Fill(dt)
            End Using
        End Using

        ddlCountry1.Items.Clear()
        ddlCountry1.Items.Insert(0, New ListItem("--Select--", "0"))
        ddlCountry1.AppendDataBoundItems = True
        ddlCountry1.DataTextField = "ShipCountry"
        ddlCountry1.DataValueField = "ShipCountry"
        ddlCountry1.DataSource = dt
        ddlCountry1.DataBind()

        ddlCountry2.Items.Clear()
        ddlCountry2.Items.Insert(0, New ListItem("--Select--", "0"))
        ddlCountry2.AppendDataBoundItems = True
        ddlCountry2.DataTextField = "ShipCountry"
        ddlCountry2.DataValueField = "ShipCountry"
        ddlCountry2.DataSource = dt
        ddlCountry2.DataBind()
    End If
End Sub

<WebMethod()>
Public Shared Function GetChartData(ByVal country1 As String, ByVal country2 As String) As List(Of Object)
    Dim chartData As List(Of Object) = New List(Of Object)()
    chartData.Add(New Object() {"ShipCity", "TotalOrders"})
    Dim query As String = "SELECT TOP 5 ShipCity, COUNT(orderid) TotalOrders FROM Orders WHERE ShipCountry BETWEEN @Country1 AND @Country2 GROUP BY ShipCity"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query)
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@Country1", country1)
            cmd.Parameters.AddWithValue("@Country2", country2)
            con.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader()
                While sdr.Read()
                    chartData.Add(New Object() {sdr("ShipCity"), sdr("TotalOrders")})
                End While
            End Using
            con.Close()

            Return chartData
        End Using
    End Using
End Function

Screenshot