Linked Questions

Popular Questions

Sort Chart by Axis Without Defining Each Series

Asked by At

I'm creating a bar chart from a SQL Query to display clients that are being managed out of an office in a different time zone than the client is in. My SQL results appear as the following:

+-----------------+---------+----------+
|      City       | Clients | TimeZone |
+-----------------+---------+----------+
| Albuquerque, NM |      68 | AZ       |
| Baltimore, MD   |      52 | CT       |
| Birmingham, AL  |      80 | ET       |
| Charleston, SC  |       3 | AZ       |
| Phoenix, AZ     |      36 | PT       |
| Phoenix, AZ     |      15 | ET       |
+-----------------+---------+----------+

Using the code below, my chart is showing correctly except it's sorting based on TimeZone, then City (even though my SQL Query is Sorted by City only).

ReportsChart.DataBindCrossTable(SqlDS1.Select(DataSourceSelectArguments.Empty), "TimeZone", "City", "Clients", "");
ReportsChart.AlignDataPointsByAxisLabel();
ChartType("bar");

INCORRECT ORDER

I know I can get around this by changing my SQL query to return like the following:

+-----------------+----+----+----+----+
|      City       | AZ | CT | ET | PT |
+-----------------+----+----+----+----+
| Albuquerque, NM | 68 |  0 |  0 |  0 |
| Baltimore, MD   |  0 | 52 |  0 |  0 |
| Birmingham, AL  |  0 |  0 | 80 |  0 |
| Charleston, SC  |  3 |  0 |  0 |  0 |
| Phoenix, AZ     |  0 |  0 | 15 | 36 |
+-----------------+----+----+----+----+

Then changing my C# like the following:

ReportsChart.Series.Add("AZ");
ReportsChart.Series[0].Points.DataBind(SqlDS1.Select(DataSourceSelectArguments.Empty), "City", "AZ", "");
ReportsChart.Series[0].IsXValueIndexed = true;
ReportsChart.Series.Add("CT");
ReportsChart.Series[1].Points.DataBind(SqlDS1.Select(DataSourceSelectArguments.Empty), "City", "CT", "");
ReportsChart.Series[1].IsXValueIndexed = true;
ReportsChart.Series.Add("ET");
ReportsChart.Series[2].Points.DataBind(SqlDS1.Select(DataSourceSelectArguments.Empty), "City", "ET", "");
ReportsChart.Series[2].IsXValueIndexed = true;
ReportsChart.Series.Add("PT");
ReportsChart.Series[3].Points.DataBind(SqlDS1.Select(DataSourceSelectArguments.Empty), "City", "PT", "");
ReportsChart.Series[3].IsXValueIndexed = true;
ChartType("bar");

CORRECT ORDER

I'd rather not have to hard code each time zone like the above as there could be additions at any time. It seems I must be over thinking this and there must be a much more simplified way of sorting without creating separate series like above. However, all solutions I have found all required creating each series individually.

Related Questions