locked
How to bind HighChart Funnel from Sql Server RRS feed

  • Question

  • User194385433 posted

    Hi,

    I am using Asp.net 2.0 Version

    How to bind data to funnel highchart, below is my sql statement fetching data from sql query.

    How to pass data from code behind to the Series.

    select userName,Lds,Hlds,pects,plmt,suspt
     
    from test1 where userName='Test'
    
    userName  Lds    Hlds   pects  plmt   suspt
    Test	  32403	  4064	 129	64	8
    
    
    $(function () {
    
        $('#container').highcharts({
            chart: {
                type: 'funnel',
                marginRight: 100
            },
            title: {
                text: 'Sales funnel',
                x: -50
            },
            plotOptions: {
                series: {
                    dataLabels: {
                        enabled: true,
                        format: '<b>{point.name}</b> ({point.y:,.0f})',
                        color: (Highcharts.theme && Highcharts.theme.contrastTextColor) || 'black',
                        softConnector: true
                    },
                    neckWidth: '30%',
                    neckHeight: '25%'
    
                   
                }
            },
            legend: {
                enabled: false
            },
            series: [{
                name: 'user',
                data: [
                    ['Lds', 32403],
                    ['Hlds', 4064],
                    ['pects list', 129],
                    ['plmt', 64],
                    ['suspt', 8]
                ]
            }]
        });
    });
    
    
    <script src="https://code.highcharts.com/highcharts.js"></script>
    <script src="https://code.highcharts.com/modules/funnel.js"></script>
    <script src="https://code.highcharts.com/modules/exporting.js"></script>
    
    <div id="container" style="min>

    Thanks,

    Thursday, May 5, 2016 10:11 AM

Answers

  • User61956409 posted

    Hi sureshtalla,

    You could create a web method to retrieve data from database, then you could call web method to fetch data using jQuery AJAX, and you could specify data option with AJAX response data on AJAX success callback function.

    series: [{
        name: 'user',
        data: response_data
    }]
    

    You could check “Highcharts data from JSON Response” section from this link.

    http://www.knowstack.com/different-ways-of-loading-highcharts-data/

    Best Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 6, 2016 2:16 AM
  • User194385433 posted

    Hi Fei Han,

    Below is the Code is working.

     
    
    <script src="javascript/highcharts.js" type="text/javascript"></script>
     <script src="javascript/Funnel.js" type="text/javascript"></script>  
    
    function ShowSalesFunnelGraph() {
    
                //  debugger;
    
                var funseries = $("#" + '<%= hdnYaxis_SalesFunnel.ClientID %>').val();
    
                // var dataEx = [["Lds", 832], ["Hlds", 173], ["pects list", 47], ["plmt", 0], ["active_deals", 0], ["suspt", 6]];
    
                var dataEx = funseries;
                var arr = $.parseJSON(funseries);
    
    
                len = arr.length;
                sum = 0;
                minHeight = 0.05;
                data = [];
    
                for (var i = 0; i < len; i++) {
                    sum += arr[i][1];
                }
                //debugger;
                for (var i = 0; i < len; i++) {
                    var t = arr[i],
                    r = t[1] / sum;
                    data[i] = { name: t[0], y: (r > minHeight ? t[1] : sum * minHeight), label: t[1] }
                }
    
    
                $('#divSalesFunnel').highcharts({
                    chart: {
                        type: 'funnel',
                        marginRight: 100
                    },
                    title: {
                        text: 'Sales funnel',
                        x: -50
                    },
                    plotOptions: {
                        series: {
                            dataLabels: {
                                enabled: true,
                                format: '<span style="color:{point.color};">{point.name} ({point.label:,.0f})</span>',
                                color: (Highcharts.theme && Highcharts.theme.contrastTextColor) || 'black',
                                softConnector: true
                            },
                            tooltip: {
                                crosshairs: true,
                                pointFormat: '<br /><b> {point.key}</b><br/> {point.label}'
                            },
                            point: {
                                events: {
                                    click: function () {
                                       
    
                                        }
                                    }
                                }
    
                            },
                            neckWidth: '25%',
                            neckHeight: '25%',
                            //-- Other available options
                            minHeight: '400'
    
    
    
                            //-- Other available options
                            // height: pixels or percent
                            // width: pixels or percent
                        }
                    },
                    legend: {
                        enabled: false
                    },
                    series: [{
                        name: 'Sales',
                        data: data
                    }]
                });
    
    
    
            }
    
    
     <div class="layer1">
                                                <p class="heading" id="P13">
                                                    Sales <strong id="Strong13" style="float: right">-</strong></p>
                                                <div class="content">
                                                    <div id="divSalesFunnel" style="width: 300px;">
                                                    </div>
                                                </div>
                                            </div>
    
    On Page Load need to Call This Method:ShowSalesFunnel(string UserName);
    
     public void ShowSalesFunnel(string UserName)
        {
    
            List<Chart> lstSalesFunnel_Series = new List<Chart>();
    
    
    
            DataTable dtweek = getSalesFunnel(UserName);
            string p = DataTableToJSONWithStringBuilder(dtweek);
    
    
           
            hdnYaxis_SalesFunnel.Value = p;
    
            
        }
    
      public string DataTableToJSONWithStringBuilder(DataTable table)
        {
            StringBuilder JSONString = new StringBuilder();
            if (table.Rows.Count > 0)
            {
    
    
    
                // JSONString.Append("[");
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    // JSONString.Append("{");
                    //JSONString.Append("\"" + "name" + "\":" + "\"" + "Sales" + "\",");
                    JSONString.Append("[");
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        if (j < table.Columns.Count - 1)
                        {
                            if (table.Columns[j].ColumnName.ToString() == "Lds")
                            {
                                JSONString.Append("[" + "\"" + "Lds" + "\"," + table.Rows[i][j].ToString() + "]" + ",");
                            }
                            if (table.Columns[j].ColumnName.ToString() == "Hlds")
                            {
                                JSONString.Append("[" + "\"" + "Hlds" + "\"," + table.Rows[i][j].ToString() + "]" + ",");
                            }
                            if (table.Columns[j].ColumnName.ToString() == "pects list")
                            {
                                JSONString.Append("[" + "\"" + "pects list " + "\"," + table.Rows[i][j].ToString() + "]" + ",");
                            }
                            if (table.Columns[j].ColumnName.ToString() == "plmt")
                            {
                                JSONString.Append("[" + "\"" + "plmt" + "\"," + table.Rows[i][j].ToString() + "]" + ",");
                            }
                            
                        
    
                        }
                        else if (j == table.Columns.Count - 1)
                        {
                            JSONString.Append("[" + "\"" + "suspt" + "\"," + table.Rows[i][j].ToString() + "]]");
                        }
                    }
                    if (i == table.Rows.Count - 1)
                    {
                        //JSONString.Append("}");
                    }
                    else
                    {
                        // JSONString.Append("},");
                    }
                }
                // JSONString.Append("]");
    
            }
            return JSONString.ToString();
        }
    
    public class Chart
    {
        public string name;
        public List<int> data;
    }

    Thanks,

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 18, 2016 11:01 PM

All replies

  • User61956409 posted

    Hi sureshtalla,

    You could create a web method to retrieve data from database, then you could call web method to fetch data using jQuery AJAX, and you could specify data option with AJAX response data on AJAX success callback function.

    series: [{
        name: 'user',
        data: response_data
    }]
    

    You could check “Highcharts data from JSON Response” section from this link.

    http://www.knowstack.com/different-ways-of-loading-highcharts-data/

    Best Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 6, 2016 2:16 AM
  • User194385433 posted

    Thanks Fei Han,

    I did it using string builder.

    Thanks

    Friday, May 6, 2016 3:01 AM
  • User61956409 posted

    Hi sureshtalla,

    If you are calling web method using jQuery AJAX, please make sure the web method could fire, and please check the returned data in AJAX request response.

    Best Regards,

    Fei Han

    Tuesday, May 10, 2016 9:34 AM
  • User194385433 posted

    Hi Fei Han,

    Below is the Code is working.

     
    
    <script src="javascript/highcharts.js" type="text/javascript"></script>
     <script src="javascript/Funnel.js" type="text/javascript"></script>  
    
    function ShowSalesFunnelGraph() {
    
                //  debugger;
    
                var funseries = $("#" + '<%= hdnYaxis_SalesFunnel.ClientID %>').val();
    
                // var dataEx = [["Lds", 832], ["Hlds", 173], ["pects list", 47], ["plmt", 0], ["active_deals", 0], ["suspt", 6]];
    
                var dataEx = funseries;
                var arr = $.parseJSON(funseries);
    
    
                len = arr.length;
                sum = 0;
                minHeight = 0.05;
                data = [];
    
                for (var i = 0; i < len; i++) {
                    sum += arr[i][1];
                }
                //debugger;
                for (var i = 0; i < len; i++) {
                    var t = arr[i],
                    r = t[1] / sum;
                    data[i] = { name: t[0], y: (r > minHeight ? t[1] : sum * minHeight), label: t[1] }
                }
    
    
                $('#divSalesFunnel').highcharts({
                    chart: {
                        type: 'funnel',
                        marginRight: 100
                    },
                    title: {
                        text: 'Sales funnel',
                        x: -50
                    },
                    plotOptions: {
                        series: {
                            dataLabels: {
                                enabled: true,
                                format: '<span style="color:{point.color};">{point.name} ({point.label:,.0f})</span>',
                                color: (Highcharts.theme && Highcharts.theme.contrastTextColor) || 'black',
                                softConnector: true
                            },
                            tooltip: {
                                crosshairs: true,
                                pointFormat: '<br /><b> {point.key}</b><br/> {point.label}'
                            },
                            point: {
                                events: {
                                    click: function () {
                                       
    
                                        }
                                    }
                                }
    
                            },
                            neckWidth: '25%',
                            neckHeight: '25%',
                            //-- Other available options
                            minHeight: '400'
    
    
    
                            //-- Other available options
                            // height: pixels or percent
                            // width: pixels or percent
                        }
                    },
                    legend: {
                        enabled: false
                    },
                    series: [{
                        name: 'Sales',
                        data: data
                    }]
                });
    
    
    
            }
    
    
     <div class="layer1">
                                                <p class="heading" id="P13">
                                                    Sales <strong id="Strong13" style="float: right">-</strong></p>
                                                <div class="content">
                                                    <div id="divSalesFunnel" style="width: 300px;">
                                                    </div>
                                                </div>
                                            </div>
    
    On Page Load need to Call This Method:ShowSalesFunnel(string UserName);
    
     public void ShowSalesFunnel(string UserName)
        {
    
            List<Chart> lstSalesFunnel_Series = new List<Chart>();
    
    
    
            DataTable dtweek = getSalesFunnel(UserName);
            string p = DataTableToJSONWithStringBuilder(dtweek);
    
    
           
            hdnYaxis_SalesFunnel.Value = p;
    
            
        }
    
      public string DataTableToJSONWithStringBuilder(DataTable table)
        {
            StringBuilder JSONString = new StringBuilder();
            if (table.Rows.Count > 0)
            {
    
    
    
                // JSONString.Append("[");
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    // JSONString.Append("{");
                    //JSONString.Append("\"" + "name" + "\":" + "\"" + "Sales" + "\",");
                    JSONString.Append("[");
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        if (j < table.Columns.Count - 1)
                        {
                            if (table.Columns[j].ColumnName.ToString() == "Lds")
                            {
                                JSONString.Append("[" + "\"" + "Lds" + "\"," + table.Rows[i][j].ToString() + "]" + ",");
                            }
                            if (table.Columns[j].ColumnName.ToString() == "Hlds")
                            {
                                JSONString.Append("[" + "\"" + "Hlds" + "\"," + table.Rows[i][j].ToString() + "]" + ",");
                            }
                            if (table.Columns[j].ColumnName.ToString() == "pects list")
                            {
                                JSONString.Append("[" + "\"" + "pects list " + "\"," + table.Rows[i][j].ToString() + "]" + ",");
                            }
                            if (table.Columns[j].ColumnName.ToString() == "plmt")
                            {
                                JSONString.Append("[" + "\"" + "plmt" + "\"," + table.Rows[i][j].ToString() + "]" + ",");
                            }
                            
                        
    
                        }
                        else if (j == table.Columns.Count - 1)
                        {
                            JSONString.Append("[" + "\"" + "suspt" + "\"," + table.Rows[i][j].ToString() + "]]");
                        }
                    }
                    if (i == table.Rows.Count - 1)
                    {
                        //JSONString.Append("}");
                    }
                    else
                    {
                        // JSONString.Append("},");
                    }
                }
                // JSONString.Append("]");
    
            }
            return JSONString.ToString();
        }
    
    public class Chart
    {
        public string name;
        public List<int> data;
    }

    Thanks,

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 18, 2016 11:01 PM