Stacked Column Chart in C#

Question

I am trying to set up my program so that the user can display a stacked column chart that displays the number of reject categories that appear in each department (for example, 5 appear in department 1, 3 in department 2, etc). I've had a look around online and taken a crack at it myself but I cannot seem to get it working. If anyone would be able to help that would be fantastic.

What the chart currently does when the user presses a button to switch to a stacked column chart:

Code:

private void btnStacked_Click(object sender, EventArgs e)
    {
        charRejections.Series["RFR"].Enabled = false;

        charRejections.Series["Department 1"].Points.Clear();
        charRejections.Series["Department 1"].Enabled = true;

        charRejections.Series["Department 2"].Points.Clear();
        charRejections.Series["Department 2"].Enabled = true;

        charRejections.Series["Department 3"].Points.Clear();
        charRejections.Series["Department 3"].Enabled = true;
        {
            string connectiontring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Database1.mdb";
            DataConnection = new OleDbConnection(connectiontring);

            try
            {

                DataConnection.Open();
                OleDbCommand command = new OleDbCommand();
                command.Connection = DataConnection;
                string query1 = "SELECT COUNT(reject_category) as reject, reject_category FROM tblReject_test GROUP BY reject_category";
                command.CommandText = query1;


                OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    charRejections.Series["Department 1"].Points.AddXY(reader["reject_category"].ToString(), reader["reject"].ToString());
                    charRejections.Series["Department 2"].Points.AddXY(reader["reject_category"].ToString(), reader["reject"].ToString());
                    charRejections.Series["Department 3"].Points.AddXY(reader["reject_category"].ToString(), reader["reject"].ToString());
                }

                DataConnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error " + ex);
            }
        }

        this.charRejections.Series["Department 1"].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.StackedColumn;
        this.charRejections.Series["Department 2"].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.StackedColumn;
        this.charRejections.Series["Department 3"].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.StackedColumn;
    }

Answer 1

Creating a stacked chart is easy. That is if you have the proper data.

Creating a stacked chart is hard. That is if you don't have the proper data.

There is one rule to follow: The data in all your series need to be aligned for the stacking to work!

This sounds simple but in fact is a lot trickier than one may expect.

Before we start with a few examples here are the details the simple rule breaks down to:

• Rule #1 You need to have a datapoint in **each series for each x-value you have in any series.**

• Rule #2 Your points need to be in the right order, that is with ascending x-values

• Rule #3 To let you control the range of points to display and other aspects of your chart, all x-values should be numeric

For a working example of a stacked chart do have a look at this post! There the question was 'How to avoid the gaps?' but it really boiled down to 'How to make the stacking work properly?'.

Note that the example uses data from a DataSource that was written in code. There is no real difference to reading it from a DataBase as you are doing.

The solution was to add dummy points to fill in the gaps.

This will be one of your problems as you can't expect the data from your query to be complete.

To solve this problem you can either change your query to some join that fills the gaps or ask the chart to help you along.

I won't go into the SQL option although it would seem to be the most natural one. Note however that in order to follow rules #2 you will need to add an order clause to the query to sort the records by the x-values in any case, i.e. by your rejection-categories.

Let's instead have a look at an interesting helper function called Chart.DataManipulator.InsertEmptyPoints :

This function has several overloads; we'll use the one with the string that holds all series names that we want to align. This will not just add the missing points but actually insert them at the spots where they are missing, so we should now be ok by rules #1 & 2!

Before going into more details (yes, even more details, sigh, but there simply is quite a bit to get right..) let's have a look at rule #3:

This is one that applies to all chart types and also the one rule users of the chart control break most often, usually without even noticing..:

All X-Values should be numeric!

If instead you add strings, those string will be stuffed into the axis labels and otherwise are thrown away. Most notably the x-values of the resulting data points all are 0!

Which is ok as long as you don't need them, but once you do you are in for a nasty surprise. Since they are gone you cannot use them to calculate anything from them, or to format the labels, or to show tooltips etc, or use them to set a range to display.

Note that even though the x-values are all 0 the datapoints are still spread along the x-axis; you just don't control that any more..

So you need to decide on some scheme to turn your x-values into numbers!

One is to set up a data structure where all your values are listed:

 List<string> catLookup = new List<string>() { "ARTEFACT", "et..cetc.."};

You can then find each value like this:

 int found = catLookup.FindIndex(x => x == someValueWeSearch);

This will work but if your application is real, it should be able to grow with the data; so you should read all possible values from the database. If it is desigend properly there is already a lookup table to use and using its key would be the most natural choice.

If it isn't, you still can read in the values with a simple query:

Select distinct reject_category from tblReject_test order by reject_category;

Now let's do the call that aligns all series we have:

  string seriesNames = String.Join(",", seriesLookup.Keys);
  chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, seriesNames);

Now back to your original code and what you need to do there:

For one thing all your values are strings. So you should change you loop to something like this:

while (reader.Read())
{
    string seriesName = reader[1].ToString();
    int seriesIndex   = seriesLookup.FindIndex(x => x == seriesName);
    string catName    = reader[2].ToString();
    int catIndex      = catLookup.FindIndex(x => x == catName);

    charRejections.Series[seriesName ].Points.AddXY(catIndex, 
                                              Convert.ToInt16((reader["reject"]));
}

You will notice that I not only have inserted helper variable that make debugging so much easier, but also a second lookup to hold the departments you need to create the series and add the points to their respective series..

I leave creating this to you; also adding the necessary checks if a category or department is not found..