My question is based on asp.net c# where i want to display data to google pie chart when i select an employee from the DROPDOWN list and for that i have used webmethod and i am able to do it for single employee , but now what i want to do is bring the record of multiple employees and i am using Listbox control with select mulitple functionality. problem is when i am passing multiple employee ids i am not able to fetch the pie chart. The code for the same is.
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Pie Chart</title>
<script src="Scripts/jquery-1.4.1.js"></script>
<script src="http://www.google.com/jsapi" type="text/javascript"></script>
</head>
<body>
<form id="form1" runat="server">
<div class="row">
<div class="col-md-12">
<div class="col-md-5">
<div class="form-group">
<label class="col-md-4 control-label" for="textinput">
From Date
</label>
<div class="col-md-8">
<div class="has-feedback">
<asp:TextBox ID="txtFromDate" class="form-control input-md datepicker-example8" placeholder="Date" runat="server"></asp:TextBox>
<label for="textinput" style="color: red">Kindly enter the date in format mm/dd/yy</label>
</div>
</div>
</div>
</div>
<br />
<div class="col-md-5">
<div class="form-group">
<label class="col-md-4 control-label" for="textinput">
To
</label>
<div class="col-md-8">
<div class="has-feedback">
<asp:TextBox ID="txtToDate" class="form-control input-md datepicker-example8" placeholder="Date" runat="server"></asp:TextBox>
<label for="textinput" style="color: red">Kindly enter the date in format mm/dd/yy</label>
</div>
</div>
</div>
</div>
</div>
</div>
<br />
<br />
<div>
<label for="textinput"><strong>Select Employee : </strong></label>
<asp:DropDownList ID="ddlEmployee" class="form-control" AutoPostBack="false" runat="server"></asp:DropDownList>
<br />
<br />
<asp:ListBox ID="lbData" runat="server" SelectionMode="Multiple"></asp:ListBox>
<br />
</div>
<div id="PieChartsDIV" style="width: 600px; height: 350px;">
</div>
</form>
</body>
<script type="text/javascript">
// Global variable to hold data
// Load the Visualization API and the piechart package.
google.load('visualization', '1', { packages: ['corechart'] });
</script>
<script type="text/javascript">
$('#lbData').focusout(function () {
debugger;
//var frmdate = $('#txtFromDate').val();
//var todate = $('#txtToDate').val();
$.ajax({
type: 'POST',
dataType: 'json',
contentType: 'application/json',
url: 'DemoChartsWithoutMaster.aspx/GetPieChartDetails',
data: "{emp_id:'" + $(this).val() + "' ,FromDate:'" + $('#txtFromDate').val() + "',ToDate:'" + $('#txtToDate').val() + "'}",
success: function (response) {
drawchart(response.d); // calling method
},
error: function () {
alert("Error loading data...........");
}
});
});
function drawchart(dataValues) {
// Callback that creates and populates a data table,
// instantiates the pie chart, passes in the data and
// draws it.
var data = new google.visualization.DataTable();
data.addColumn('string', 'ProjectName');
data.addColumn('number', 'WorkingUtilization');
for (var i = 0; i < dataValues.length; i++) {
data.addRow([dataValues[i].ProjectName, dataValues[i].WorkingUtilization]);
}
// Instantiate and draw our chart, passing in some options
var chart = new google.visualization.PieChart(document.getElementById('PieChartsDIV'));
var options = {
is3D: true,
};
chart.draw(data, options,
{
title: "Pie Chart of Google Chart in Asp.net",
position: "top",
fontsize: "14px",
chartArea: { width: '50%' },
});
}
</script>
</html>
The code file is as mentioned below
[WebMethod]
public static IList GetPieChartDetails(string emp_id ,string FromDate,string ToDate)
{
DataSet EmpData = LeaveUpdate.sp_GetDataForGraphByEmp(emp_id ,FromDate,ToDate);
List<ChartDetails> l = new List<ChartDetails>();
for (int i = 0; i < EmpData.Tables[0].Rows.Count; i++)
{
ChartDetails ll = new ChartDetails();
ll.ProjectName = EmpData.Tables[0].Rows[i]["ProjectName"].ToString();
ll.WorkingUtilization = Convert.ToDecimal(EmpData.Tables[0].Rows[i]["WorkingUtilization"].ToString());
l.Add(ll);
}
#region Commented Code
//SqlCommand cmd = new SqlCommand("sp_GetDataForGraphByEmp", con);
// cmd.CommandType = CommandType.StoredProcedure;
// SqlDataAdapter da = new SqlDataAdapter();
// da.SelectCommand = cmd;
// DataTable dt = new DataTable();
// da.Fill(dt);
// List<ChartDetails> dataList = new List<ChartDetails>();
// foreach (DataRow dtrow in dt.Rows)
// {
// ChartDetails details = new ChartDetails();
// details.ProjectName = dtrow[0].ToString();
// details.WorkingUtilization = Convert.ToInt32(dtrow[1]);
// dataList.Add(details);
// }
#endregion
return l;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GetProjectName();
}
}
public class ChartDetails
{
public string ProjectName { get; set; }
public decimal WorkingUtilization { get; set; }
}
public void GetProjectName()
{
try
{
DataSet ds = Dotcom.BOL.EmployeeMST.GetAllEmployee();
ddlEmployee.DataTextField = "Name";
ddlEmployee.DataValueField = "Emp_ID";
ddlEmployee.DataSource = ds;
ddlEmployee.DataBind();
ddlEmployee.Items.Insert(0, new ListItem("--All--", "0"));
DataSet ds1 = Dotcom.BOL.EmployeeMST.GetAllEmployee();
lbData.DataTextField = "Name";
lbData.DataValueField = "Emp_ID";
lbData.DataSource = ds1;
lbData.DataBind();
lbData.Items.Insert(0, new ListItem("--All--", "0"));
}
catch (Exception)
{ }
}
and the stored procedure which i am using is as follows
alter proc sp_GetDataForGraphByEmp
@Emp_ID nvarchar(150),
@Fromdate nvarchar(50),
@Todate nvarchar(50)
as
begin
select ProjectName,SUM(CAST(WorkingUtilization AS float)) AS WorkingUtilization
from TimesheetReport
where (CAST(Emp_ID AS nvarchar(150))) in (@Emp_ID) and DateSubmit Between @Fromdate and @Todate
group by ProjectName
end
In the above code when i am entering manually like "In(30,36)" then i am able to get data but when i am running the code it is giving me null value. even i am getting null in below code.
exec sp_GetDataForGraphByEmp '30,36'
How should i go about to get the pie chart populated with multiple employee Id's
