The problem that I currently have is that I'm trying to populate a stacked bar chart with data from SQL server. Following is the database and code.
public void LoadHospitalCat()
{
try
{
string connString = ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;
using (SqlConnection sqlConn = new SqlConnection(connString))
{
//Fetch the Statistical data from database.
string sqlQuery = @" SELECT [HospitalCodesOrdered2].Hospital AS Hospital, Count([PatientInformation].PatientCategory) as PatientCategory,[PatientInformation].PatientCategory as PatientCategory2
from [HospitalCodesOrdered2]
INNER JOIN [PatientInformation] ON [HospitalCodesOrdered2].[Code]=[PatientInformation].[HospitalCode] group by PatientCategory, Hospital order by PatientCategory;";
SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn);
//DataTable dt = GetData(query);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable table = new DataTable();
da.Fill(table);
//Get the DISTINCT Countries.
List<string> PatientCategories2 = (from p in table.AsEnumerable()
select p.Field<string>("PatientCategory2")).Distinct().ToList();
//Remove the Default Series.
if (chart10.Series.Count() == 1)
{
chart10.Series.Remove(chart10.Series[0]);
}
//Loop through the Countries.
foreach (string PatientCategory in PatientCategories2)
{
//Get the Year for each Country.
string[] x = (from p in table.AsEnumerable()
where p.Field<string>("PatientCategory2") == PatientCategory
orderby p.Field<string>("Hospital") ascending
select p.Field<string>("Hospital")).ToArray();
////Get the Total of Orders for each Country.
int[] y = (from p in table.AsEnumerable()
where p.Field<string>("PatientCategory2") == PatientCategory
orderby p.Field<string>("Hospital") ascending
select p.Field<int>("PatientCategory")).ToArray();
//Add Series to the Chart.
chart10.Series.Add(new Series(PatientCategory));
chart10.Series[PatientCategory].IsValueShownAsLabel = true;
chart10.Series[PatientCategory].ChartType = SeriesChartType.StackedColumn;
chart10.Series[PatientCategory].Points.DataBindXY(x, y);
}
chart10.Legends[0].Enabled = true;
chart10.ChartAreas.FirstOrDefault().AxisX.Interval = 1;
}
}
catch (SqlException ex)
{
if (ex.Number == 11001)
{
MessageBox.Show("You Are Offline. Please Check your Internet Connection");
}
else throw;
}
}
The following is the graph first in per hospital basis and then all hospitals.
the first image has hospital Horana 1184 in yellow but in single it is 31. All the graphs are like that. Please help if you can.
