In this article I’m going
to explain how to create google stacked column chart from database in ASP.NET.
The stacked column chart
is used when data sets have to be broken down into their constituents, and then
the data sets as a whole also need to be compared against one another. For
example, if you wanted to show the total yearly sales as a column charts, but
within each column indicate what % of sales came from product and what % came
from services in the respective year, you would use the stacked charts. The
stacked charts help in comparison of sum, but is not suited for comparison of
the constituents of each column against each other.
Here I’ll show you how to create stacked
column chart from SQL Server database. First we have to create table. Table
design and sample data given below.
Table
design:
Column
|
datatype
|
id
|
int
|
year
|
varchar(50)
|
sales
|
varchar(10)
|
expences
|
varchar(10)
|
Sample
data:
id
|
year
|
sales
|
expences
|
1
|
2000
|
150000
|
50000
|
2
|
2001
|
100000
|
30000
|
3
|
2002
|
200000
|
100000
|
4
|
2003
|
180000
|
80000
|
5
|
2004
|
200000
|
90000
|
6
|
2005
|
250000
|
200000
|
Here
we’ve to use following code to load google JSAPI library
<script type="text/javascript"
src="https://www.google.com/jsapi"></script>
Designer
source code:
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Stacked column chart using Google Visualization</title>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Literal ID="lt" runat="server"></asp:Literal>
<div id="chart_div"></div>
</form>
</div>
</body>
</html>
Note:
Here we have to use following
namespace for StringBuilder
class.
using System.Text;
C#
code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Configuration;
public partial class _Default :
System.Web.UI.Page
{
StringBuilder
str = new StringBuilder();
//Get connection
string from web.config
SqlConnection
conn = new SqlConnection("Data source=localhost; Initial catalog=Demo;
Integrated security=true");
protected void Page_Load(object
sender, EventArgs e)
{
if
(Page.IsPostBack == false)
{
BindChart();
}
}
private DataTable GetData()
{
DataTable
dt = new DataTable();
string
cmd = "select * from Sales";
SqlDataAdapter
adp = new SqlDataAdapter(cmd,
conn);
adp.Fill(dt);
return
dt;
}
private void BindChart()
{
DataTable
dt = new DataTable();
try
{
dt = GetData();
str.Append(@"<script
type=*text/javascript*> google.load( *visualization*, *1*,
{packages:[*corechart*]});
google.setOnLoadCallback(drawChart);
function
drawChart() {
var data = new
google.visualization.DataTable();
data.addColumn('string', 'Year');
data.addColumn('number', 'Sales');
data.addColumn('number', 'Expences');
data.addRows(" + dt.Rows.Count + ");");
for
(int i = 0; i <= dt.Rows.Count - 1; i++)
{
str.Append("data.setValue( " + i + "," + 0 + ","
+ "'" + dt.Rows[i]["year"].ToString() + "');");
str.Append("data.setValue(" + i + "," + 1 + ","
+ dt.Rows[i]["Sales"].ToString() +
") ;");
str.Append("data.setValue("
+ i + "," + 2 + "," + dt.Rows[i]["Expences"].ToString() + ")
;");
}
str.Append("
var chart = new
google.visualization.ColumnChart(document.getElementById('chart_div'));");
str.Append("
chart.draw(data,{isStacked:true, width:600, height:350, hAxis:
{showTextEvery:1, slantedText:true}});}");
str.Append("</script>");
lt.Text = str.ToString().Replace('*',
'"');
}
catch
{ }
}
}
For more details please
refer following link.
https://developers.google.com/chart/interactive/docs/gallery/columnchart