Is it possible to match two series of data in a ch

Question

I am working on a application to chart boards inspected and board with defects based on work order number. I realize after looking at the chart and comparing the actual data, that the series do not match up for work order number. I am at a loss on how to match both series to the work order number to get a correct chart.

Here is my code for the data pull Boards_Inspected pulled first

public DataSet Get_Boards_Inspected(string startDate, string endDate, int location)
    {
        DataSet ds = new DataSet();

        NpgsqlConnection conn = DatabaseConnection.getConnectionString();
        try
        {
            conn.Open();
            NpgsqlDataAdapter da = new NpgsqlDataAdapter("select new_table.top_or_bottom, new_table.board_wo_number, count(new_table.board_serial_number) from" +
            " (select distinct master_board.board_serial_number, board_wo.board_wo_number,board_wo.board_part_number, board_time.top_or_bottom from master_board" +
            " inner join board_time on board_time.board_time_id = master_board.id" +
            " inner join board_wo on board_wo.board_wo_number = master_board.board_wo_number" +
            " where time_in between '" + startDate + "' and '" + endDate + "'" +
            " and board_time.location_id = '" + location + "')" +
            " as new_table" +
            " group by new_table.top_or_bottom, new_table.board_wo_number" +
            " order by top_or_bottom;", conn);
            ds.Reset();
            da.Fill(ds);
            da.Dispose();
        }
        catch (Exception ex)
        {
            MessageBox.Show("An unexpected error has occured with the application. \n" +
            "An email has been sent to the software developer for analysis. \n" +
            "this program will now close.", "Unexpected Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

            eMessageBody = ex.ToString();
            Mail sendMessage = new Mail();
            sendMessage.SendSMTP(eMessageBody, eMessageSubject);
            Application.Exit();
        }
        finally
        {
            conn.Close();
        }
        return ds;
    }

Board with defects

 public DataSet Get_Boards_With_Issue(string startDate, string endDate, int location)
    {
        DataSet ds = new DataSet();
        NpgsqlConnection conn = DatabaseConnection.getConnectionString();
        try
        {
            conn.Open();
            NpgsqlDataAdapter da = new NpgsqlDataAdapter("select new_table.top_or_bottom, new_table.board_wo_number, count(new_table.defect_id) from" +
            " (select distinct defect_id, top_or_bottom, board_wo_number from defect" +
            " inner join master_board on defect.defect_id = master_board.id" +
            " where defect_time between '" + startDate + "' and '" + endDate + " 23:59:59'" +
            " and location_id = '" + location + "')" +
            " as new_table" +
            " group by new_table.top_or_bottom, new_table.board_wo_number" +
            " order by top_or_bottom;", conn);

            ds.Reset();
            da.Fill(ds);
            da.Dispose();
        }
        catch(Exception ex)
        {
            MessageBox.Show("An unexpected error has occured with the application. \n" +
            "An email has been sent to the software developer for analysis. \n" +
            "this program will now close.", "Unexpected Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

            eMessageBody = ex.ToString();
            Mail sendMessage = new Mail();
            sendMessage.SendSMTP(eMessageBody, eMessageSubject);
            Application.Exit();
        }
        finally
        {
            conn.Close();
        }
        return ds;
    }

Here is my chart series assignment code

private void Boards_Without_Issue_Chart(string starDate, string endDate, int location)
    {
        try
        {
            #region Chart Setup
            chart1.Series.Clear();
            chart1.Series.Add("Boards Inspected");
            chart1.Series.Add("Boards Without Issue");
            chart1.Series.Add("Boards With Issue");

            chart1.Series["Boards Inspected"].Points.Clear();
            chart1.Series["Boards Without Issue"].Points.Clear();
            chart1.Series["Boards With Issue"].Points.Clear();

            chart1.Series["Boards Inspected"]["LabelStyle"] = "Top";
            chart1.Series["Boards Without Issue"]["LabelStyle"] = "Top";
            chart1.Series["Boards With Issue"]["LabelStyle"] = "Top";

            chart1.ChartAreas[0].AxisX.Interval = 1;
            chart1.Series["Boards Inspected"].ChartType = SeriesChartType.Column;
            chart1.Series["Boards Without Issue"].ChartType = SeriesChartType.Column;
            chart1.Series["Boards With Issue"].ChartType = SeriesChartType.Column;

            chart1.Series["Boards Inspected"]["DrawingStyle"] = "LightToDark";
            chart1.Series["Boards Without Issue"]["DrawingStyle"] = "LightToDark";
            chart1.Series["Boards With Issue"]["DrawingStyle"] = "LightToDark";


            if (chart1.Titles.Contains(t1))
            {
                t1.Font = new System.Drawing.Font("Microsoft Sans serif", 14, FontStyle.Bold);
                t1.Text = titleText + " - Boards Without Issue - (" + startDate + " - " +endDate+ ")";
            }
            else
            {
                t1.Name = "tTitle1";
                t1.Font = new System.Drawing.Font("Microsoft Sans serif", 14, FontStyle.Bold);
                t1.Text = titleText + " - Boards Without Issue - (" + startDate + " - " + endDate + ")";
                chart1.Titles.Add(t1);
            }

            chart1.ChartAreas[0].BorderDashStyle = ChartDashStyle.Solid;
            chart1.ChartAreas[0].AxisX.MajorGrid.LineWidth = 0;
            chart1.ChartAreas[0].AxisY.TitleFont = new System.Drawing.Font("Microsoft Sans serif", 14, FontStyle.Bold);
            chart1.ChartAreas[0].AxisY.Title = "Amount of Boards";
            chart1.ChartAreas[0].AxisX.TitleFont = new System.Drawing.Font("Microsoft Sans serif", 14, FontStyle.Bold);
            chart1.ChartAreas[0].AxisX.Title = "Work Order";
            chart1.ChartAreas[0].AxisX.LabelStyle.Angle = -45;
            chart1.ChartAreas[0].AxisX.LabelStyle.Font = new System.Drawing.Font("Microsoft Sans serif", 12, FontStyle.Regular);
            chart1.Series["Boards Inspected"].IsValueShownAsLabel = true;
            chart1.Series["Boards With Issue"].IsValueShownAsLabel = true;

            #endregion

            #region Chart Data Assignment
            DataAccess DA = new DataAccess();
            DataAccess DA2 = new DataAccess();
            DataSet mda = new DataSet();
            if ((DA.Get_Boards_Inspected(startDate, endDate, location).Tables[0].Rows.Equals(0)) && 
                (DA2.Get_Boards_With_Issue(startDate, endDate, location).Tables[0].Rows.Equals(0)))
                lblError.Text = "No information Availiable";
            else
            {
                foreach (DataTable tb in DA.Get_Boards_Inspected(startDate, endDate, location).Tables)
                {
                    foreach (DataRow dr in tb.Rows)
                    {
                        object tpn = dr["top_or_bottom"];
                        var ct = (dr["count"].ToString());
                        var wo = (dr["board_wo_number"].ToString());

                        if (tpn == DBNull.Value)
                            chart1.Series["Boards Inspected"].Points.AddXY(wo, ct);
                        else
                            chart1.Series["Boards Inspected"].Points.AddXY(wo + " - " + tpn, ct);
                    }
                }
                DA.Get_Boards_Inspected(startDate, endDate, location).Clear();
                DA.Get_Boards_Inspected(startDate, endDate, location).Dispose();

                foreach (DataTable tb2 in DA2.Get_Boards_With_Issue(startDate, endDate, location).Tables)
                {
                    foreach (DataRow dr2 in tb2.Rows)
                    {
                        object tpn2 = dr2["top_or_bottom"];
                        var ct = (dr2["count"].ToString());
                        var wo = (dr2["board_wo_number"].ToString());

                        if (tpn2 == DBNull.Value)
                            chart1.Series["Boards With Issue"].Points.AddXY(wo, ct);
                        else
                            chart1.Series["Boards With Issue"].Points.AddXY(wo + " - " + tpn2, ct);
                    }
                }
                DA2.Get_Boards_With_Issue(startDate, endDate, location).Clear();
                DA2.Get_Boards_With_Issue(startDate, endDate, location).Dispose();
            }

            #endregion
        }
        catch(Exception ex)
        {
            MessageBox.Show("An unexpected error has occured with the application. \n" +
            "An email has been sent to the software developer for analysis. \n" +
            "this program will now close.", "Unexpected Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

            eMessageBody = ex.ToString();
            Mail sendMessage = new Mail();
            sendMessage.SendSMTP(eMessageBody, eMessageSubject);
            Application.Exit();
            MessageBox.Show(ex.ToString());
        }
    }

EDIT: Here is the code for the chart data

 DataAccess DA = new DataAccess();
            DataAccess DA2 = new DataAccess();
            DataSet mda = new DataSet();
            if ((DA.Get_Boards_Inspected(startDate, endDate, location).Tables[0].Rows.Equals(0)) && 
                (DA2.Get_Boards_With_Issue(startDate, endDate, location).Tables[0].Rows.Equals(0)))
                lblError.Text = "No information Availiable";
            else
            {
                foreach (DataTable tb in DA.Get_Boards_Inspected(startDate, endDate, location).Tables)
                {
                    foreach (DataRow dr in tb.Rows)
                    {
                        object tpn = dr["top_or_bottom"];
                        var ct = (dr["count"].ToString());
                        var wo = (dr["board_wo_number"].ToString());

                        if (tpn == DBNull.Value)
                            chart1.Series["Boards Inspected"].Points.AddXY(wo, ct);
                        else
                            chart1.Series["Boards Inspected"].Points.AddXY(wo + " - " + tpn, ct);
                    }
                }
                DA.Get_Boards_Inspected(startDate, endDate, location).Clear();
                DA.Get_Boards_Inspected(startDate, endDate, location).Dispose();

                foreach (DataTable tb2 in DA2.Get_Boards_With_Issue(startDate, endDate, location).Tables)
                {
                    foreach (DataRow dr2 in tb2.Rows)
                    {
                        object tpn2 = dr2["top_or_bottom"];
                        var ct = (dr2["count"].ToString());
                        var wo = (dr2["board_wo_number"].ToString());

                        if (tpn2 == DBNull.Value)
                            chart1.Series["Boards With Issue"].Points.AddXY(wo, ct);
                        else
                            chart1.Series["Boards With Issue"].Points.AddXY(wo + " - " + tpn2, ct);
                    }
                }
                DA2.Get_Boards_With_Issue(startDate, endDate, location).Clear();
                DA2.Get_Boards_With_Issue(startDate, endDate, location).Dispose();

EDIT: Links

Both data sets, trying to line them up in a chart

Recent chart

Answer 1

I figured it out, I first changed my DataSets into DataTables. I then created a new column in DataTable 1 that will hold the count field in DataTable 2. I then looped through all the row in DataTable 1 and looped through DataTable 2 and put a select condition to match the top_or_bottom and board_wo_fields and pulled the count value for each match out of DataTable 2 and put them in DataTable 1.

DataTable dt1 = DA.Get_Boards_Inspected(startDate, endDate, location);
                DataTable dt2 = DA2.Get_Boards_With_Issue(startDate, endDate, location);


                DataColumn newCol = new DataColumn("dcount", typeof(System.Object));
                newCol.AllowDBNull = true;
                dt1.Columns.Add(newCol);
                foreach(DataRow r in dt1.Rows)
                {
                    object wo = (r["board_wo_number"]).ToString();
                    object tp = (r["top_or_bottom"]).ToString();

                    if (tp == "")
                    {
                        foreach (DataRow r1 in dt2.Select("board_wo_number = '" + wo + "'"))
                        {
                            if (r1["count"] == DBNull.Value)
                                r["dcount"] = 0;
                            else
                                r["dcount"] = (r1["count"]);
                        }
                    }
                    else
                    {
                        foreach (DataRow r1 in dt2.Select("board_wo_number = '" + wo + "' and top_or_bottom = '" + tp + "'"))
                        {
                            if (r1["count"] == DBNull.Value)
                                r["dcount"] = 0;
                            else
                                r["dcount"] = (r1["count"]);
                        }
                    }
                }

                foreach (DataRow dr in dt1.Rows)
                {
                    object tpn = (dr["top_or_bottom"]);
                    object ct = (dr["count"]).ToString();
                    object wo = (dr["board_wo_number"]).ToString();
                    object ct2 = (dr["dcount"]).ToString();

                    if (tpn == DBNull.Value)
                    {
                        chart1.Series["Boards Inspected"].Points.AddXY(wo, ct);
                        chart1.Series["Boards With Issue"].Points.AddXY(wo, ct2);
                    }
                    else
                    {
                        chart1.Series["Boards Inspected"].Points.AddXY(wo + " - " + tpn, ct);
                        chart1.Series["Boards With Issue"].Points.AddXY(wo + " - " + tpn, ct2);
                    }
                }      

Answer 2

This is a classic mistake when working with Chart controls.

They seem to do everything automagically right until you get a little closer and hit upon problems.

Here is the rule:

All Values, both X-Values and all Y-Values are internally stored as doubles.

If you feed in numbers all works fine. If you feed in any other data type you are heading for trouble somewhere down the line.

At first all seems to work and the stuff you feed into shows up nicely in the labels.

But when you want to use any advanced ability of the Chart, chances are it work work..

This can be something as simple as formatting the labels, which works with string formats only. If you want to use a number format you need to feed in numbers!

You are hitting upon the problem of matching up points in different series.

You have fed in the X-Values as strings and so the trouble starts as soon as the data points need matching beyond the order in which you add them.

You could feed them in precise order but you need to understand what happens..

Lets have a look behind the scenes..: If you use the debugger to look into the x-Values of your series' datapoints, you will be amazed to see that they all are 0! The strings you fed in, have gone into the labels, but the X-Values are all the result of the failed conversion to double, resulting in 0. This means all datapoint have the same X-Value!

You have two options:

• Either add them all in synch, all in the same order and the same number of points in all series.

• Use numbers for X-Values.

Your workOrder looks like a number; if it is you can use it, if you want to, but it will then spread out the data according to those numbers. Probably not a good idea. Instead you can assign each workorder an index and use this instead.

To create nice labels use the AxisLabel property for each data point!

It is easiest, imo to create a DataPoint first with values and axislabel and maybe tooltips and colors and whatnot and then add it to the points collection.

---

Now to let this sink in, have a look at this chart:

This is the code to create it:

private void Form1_Load(object sender, EventArgs e)
{
    Random R = new Random(1);
    List<Tuple<Series, int>> misses = new List<Tuple<Series, int>>();
    chart1.Series.Clear();

    for (int i = 0; i < 3; i++ )
    {
        Series s = new Series("S" + (i + 1));
        s.ChartType = SeriesChartType.Column;
        chart1.Series.Add(s);
    }

    chart1.ChartAreas[0].AxisX.Interval = 1;

    foreach(Series s in chart1.Series)
    {
        for (int i = 0; i < 30; i+=3)
        {
            if (R.Next(3) > 0) s.Points.AddXY(i, i+1);
            else misses.Add(new Tuple<Series, int>(s, i));
        }
    }

    foreach (Tuple<Series, int> m in misses)
    {
        if (m.Item1.Name == "S1") m.Item1.Points.AddXY(m.Item2 + "X", m.Item2 + 5);
        else m.Item1.Points.AddXY(m.Item2, m.Item2 + 5);
    }

    for (int i = 0; i < chart1.Series[0].Points.Count - 1; i++)
    {
        chart1.Series[0].Points[i].AxisLabel = chart1.Series[0].Points[i].XValue + "%";
    }
}

Let's have a look at the things that happen:

I first create three series and then fill a few points into them. However I randomly leave a few slots empty.

I store these in a List of Tuples, so I can later add them out of order.

And you can see that they all match up, except for the blue series 'S1'.

Can you see why?

I always use a nice number for the X-Values, but not for the missed points in the blues series, where I have attached an "X" to the number.

Now these points are added too, but all with an X-Value of 0 and so they all sit at position 0.

Note: Note that the above is not code you can use. It is code to study to learn about the data types of Chart values and the consequences of adding strings as X-Values!