How to creat Stack Column Chart from DataTable (vb.net)

[lulu79]

I have datatable looks like below:

I want to create a stack chart that will produce something like this:

I don't know where to start. I have google about how to create stack column but still can't get it.

So far I have a chart but mcname not include (only have LINENAME at X-Axis, DURATION at Y-Axis and Count (As Frequency) at Secondary Y-Axis)

This is my chart code:

' Setup the chart
            With Chart1
                .DataSource = grpTot ''Datatable name
                .Legends.Clear()
                .ChartAreas.Clear()
                .BackColor = Drawing.Color.Linen

                .ChartAreas.Add(New ChartArea("Fred"))

                With .ChartAreas("Fred")
                    .AxisY.LabelStyle.Format = "HH:mm:ss"
                    .AxisY.Minimum = DateTime.FromOADate( _
                    Math.Floor(CDate(grpTot.Compute("min([ChartTime])", "True")).ToOADate)) _
                   .ToOADate

                    'Play with this to get the effect you want
                    .AxisX.LabelStyle.Interval = 1
                    .AxisX.LabelStyle.Format = "G"
                    .AxisX.LabelStyle.IntervalType = DateTimeIntervalType.Auto
                    .AxisX.Title = "LINE"
                    .AxisY.Title = "AVERAGE LOST TIME (hh:mm:ss)"
                    .AxisY2.Title = "FREQUENCY"
                    .AxisX.TitleFont = New Drawing.Font("arial", 12)
                    .AxisY.TitleFont = New Drawing.Font("arial", 12)
                    .AxisY2.TitleFont = New Drawing.Font("arial", 12)
                    .AxisX.MajorGrid.Enabled = False
                    .AxisY.MajorGrid.Enabled = False



                End With

                .Series.Clear()
                .Series.Add(New Series("AVERAGE LOST TIME"))

                With .Series("AVERAGE LOST TIME")
                    .ChartArea = "Fred"
                    .ChartType = SeriesChartType.Column
                    .XValueType = ChartValueType.String
                    .XValueMember = "Linename"
                    .YValueType = ChartValueType.DateTime
                    .YValueMembers = "ChartTime"
                    .Color = Drawing.Color.DeepSkyBlue

                End With

                .Series.Add(New Series("FREQUENCY"))


                With .Series("FREQUENCY")
                    .ChartArea = "Fred"
                    .ChartType = SeriesChartType.Line
                    .BorderWidth = 2
                    .BorderColor = Drawing.Color.DarkOrange
                    .XValueType = ChartValueType.String
                    .XValueMember = "Linename"
                    .YValueType = ChartValueType.Int64
                    .YValueMembers = "Count"
                    .Color = Drawing.Color.DarkMagenta
                    .YAxisType = System.Web.UI.DataVisualization.Charting.AxisType.Secondary

                End With

            End With

Can someone help me please..

[lulu79]

I dont know why my datatable image not appear at above post. here is the image:

[TnTinMN]

You are either going to have to transform your table into format that databinding can read, or feed the points individually by looping through your table. Since you are using timespans, you will have to transform the timespan to a numeric value. You have been shown before how to do this by using the timespan ticks property to create a new DateTime and then use the the DateTime.ToOADate function.

Here is an example:

Imports System.Windows.Forms.DataVisualization.Charting

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'  Method1 - Add the points

   Dim Chart1 As New Chart
   Chart1.ChartAreas.Add(New ChartArea With {.Name = "myarea"})
   Chart1.Legends.Add("mylegend")
   Chart1.Legends("mylegend").Alignment = StringAlignment.Center


   Dim s1 As New Series
   Dim s2 As New Series


   With s1
      .ChartArea = "myarea"
      .Name = "MC101"
      .ChartType = SeriesChartType.StackedColumn
      .XValueType = ChartValueType.String
      .YValueType = ChartValueType.Double
      With .Points
         .AddXY("J1", 2)
         .AddXY("J2", 3.5)
      End With
   End With

   With s2
      .ChartArea = "myarea"
      .Name = "MC102"
      .ChartType = SeriesChartType.StackedColumn
      .XValueType = ChartValueType.String
      .YValueType = ChartValueType.Double
      With .Points
         .AddXY("J1", 1)
         .AddXY("J2", 4)
      End With
   End With

   Chart1.Series.Add(s1)
   Chart1.Series.Add(s2)

   Me.Controls.Add(Chart1)

   ' Method2 Bound to a table

   Dim Chart2 As New Chart
   Chart2.ChartAreas.Add(New ChartArea With {.Name = "myarea"})
   Chart2.Legends.Add("mylegend")
   Chart2.Legends("mylegend").Alignment = StringAlignment.Center

   Dim dt As New DataTable
   Dim r As DataRow

   With dt
      .Columns.Add("XLables", GetType(String))
      .Columns.Add("MC101", GetType(Double))
      .Columns.Add("MC102", GetType(Double))

      r = .NewRow : r(0) = "J1" : r(1) = 2 : r(2) = 1 : .Rows.Add(r)
      r = .NewRow : r(0) = "J2" : r(1) = 3.5 : r(2) = 4 : .Rows.Add(r)
   End With

   Chart2.DataSource = dt

   Dim s1b As New Series
   Dim s2b As New Series

   With s1b

      .XValueMember = "XLables"  '***** Set this in only one series ******
      .YValueMembers = "MC101"
      .ChartArea = "myarea"
      .Name = "MC101"
      .ChartType = SeriesChartType.StackedColumn
      .XValueType = ChartValueType.String
      .YValueType = ChartValueType.Double
   End With

   With s2b
      .YValueMembers = "MC102"
      .ChartArea = "myarea"
      .Name = "MC102"
      .ChartType = SeriesChartType.StackedColumn
      .XValueType = ChartValueType.String
      .YValueType = ChartValueType.Double
   End With

   Chart2.Series.Add(s1b)
   Chart2.Series.Add(s2b)
   Chart2.Location = New Point(Chart1.Location.X + Chart1.Width + 20, Chart1.Location.Y)

   Me.Controls.Add(Chart2)
   Me.AutoSize = True

End Sub

End Class

Enjoy whatever holidays you celebrate, I'm out of here for a few weeks. I was supposed to have left already, but the weather had other plans. Hopefully I hit the road tommorow.

[lulu79]

Thank you for the code TnTinMN..
I'm working on it right now and hope it will give the desired ouput :)

Happy holiday to you too TnTinMN!
I'm not celebrate anything at the of December but will start busy with my wedding preparation. Another 4 months to go.. hehe (^_^)

Have a safe journey!

[lulu79]

I tried below code to generate the stacked chart but I can't get the desired output.
I want the chart output is based on the datatable output.
What is wrong with my code? :(

conn.Open()

            mySelect = "SELECT REFLINKTBL.LINENAME, MCNAME, JSFACTOR.EVENTTM "
            mySelect = mySelect & " FROM (SELECT JSLINE.LINENAME, JSMC.RECID, JSMC.MCNAME, JSMC.GOH "
            mySelect = mySelect & " FROM JSLINE RIGHT OUTER JOIN  "
            mySelect = mySelect & " JSMC ON JSLINE.RECID = JSMC.ID_JSLINE) REFLINKTBL INNER JOIN  "
            mySelect = mySelect & " JSFACTOR ON JSFACTOR.ID_JSMC = REFLINKTBL.RECID  "
            mySelect = mySelect & " WHERE JSFACTOR.YOUIN LIKE '%PARTSC%' "
            mySelect = mySelect & "  AND LINENAME LIKE 'M%' "
            mySelect = mySelect & "  AND MCNAME NOT LIKE 'HT%' "
            mySelect = mySelect & " AND (JSFACTOR.EVENTST BETWEEN ('" & strtShift & "') AND ('" & endShift & "')) "
            mySelect = mySelect & " ORDER BY SUBSTR (LINENAME,1,1), LENGTH (LINENAME), LINENAME, JSFACTOR.EVENTST  "

            myDataAdapter = New OracleDataAdapter(mySelect, conn)

            Dim Ds As New DataSet()
            myDataAdapter.Fill(Ds)

            Dim grpTot As DataTable = Ds.Tables(0).Clone

            'Make the changes to the Table structure.

            grpTot.Columns("EVENTTM").DataType = GetType(TimeSpan)
            grpTot.Columns("EVENTTM").ColumnName = "DURATION"


            grpTot.Columns.Add("TotalSum", GetType(TimeSpan))
            grpTot.Columns.Add("Count", GetType(Int32))


            Dim group2 As IEnumerable(Of DataRow) = _
From row In Ds.Tables(0).AsEnumerable() _
           Group row By LINENAME = row.Field(Of String)("LINENAME"), _
           MCNAME = row.Field(Of String)("MCNAME") Into grp = Group _
        Select DTRow(grpTot, _
           New Object() {LINENAME, MCNAME, _
                         New TimeSpan(CType( _
                            grp.Average(Function(r2 As DataRow) TimeSpan.Parse(r2.Field(Of String)("EVENTTM")).Ticks),  _
                            Int64 _
                            ) _
                            ), _
              New TimeSpan( _
                                            grp.Sum(Function(r2 As DataRow) TimeSpan.Parse(r2.Field(Of String)("EVENTTM")).Ticks _
                                                        ) _
                                                      ), _
                            grp.Count() _
                            })



            'Add the query results to the new Table

            For Each row As DataRow In group2
                grpTot.Rows.Add(row)
            Next

            '''''' Change new column to HH:mm:ss format ''''''

            Dim datanew As DataColumn = grpTot.Columns(2)
            Dim time As New DateTime

            For i As Integer = 0 To grpTot.Rows.Count - 1
                time = grpTot.Rows(i).Item("DURATION").ToString()
                grpTot.Rows(i).Item(datanew) = time.ToString("HH:mm:ss")
            Next

            GridView1.DataSource = grpTot
            GridView1.DataBind()

            'Add new column

            grpTot.Columns.Add("ChartTime", GetType(DateTime))


            For Each g In grpTot.Rows
                g("ChartTime") = myToday.Add(CType(g("DURATION"), TimeSpan))
            Next


            '  Method1 - Add the points
            'Dim Chart4 As New Chart
            Chart4.ChartAreas.Add(New ChartArea With {.Name = "myarea"})
            Chart4.Legends.Add("mylegend")
            Chart4.Legends("mylegend").Alignment = StringAlignment.Center

            Dim s1 As New Series
            Dim s2 As New Series
            Dim s3 As New Series
            Dim s4 As New Series
            Dim s5 As New Series
            Dim s6 As New Series
            Dim s7 As New Series
            Dim s8 As New Series
            Dim s9 As New Series
            Dim s10 As New Series

            With s1
                .ChartArea = "myarea"
                .Name = "SIG200A"
                .ChartType = SeriesChartType.StackedColumn
                .XValueType = ChartValueType.String
                .YValueType = ChartValueType.DateTime


                With .Points
                    Dim rows() As DataRow = grpTot.Select("McName = 'SIG200A'")
                    If rows.Count > 0 Then
                        For i = 0 To rows.Count - 1
                            .AddXY(rows(i).Item("Linename"), rows(i).Item("ChartTime"))
                        Next

                    End If
                End With
            End With

            With s2
                .ChartArea = "myarea"
                .Name = "SIG200B"
                .ChartType = SeriesChartType.StackedColumn
                .XValueType = ChartValueType.String
                .YValueType = ChartValueType.DateTime


                With .Points
                    Dim rows() As DataRow = grpTot.Select("McName = 'SIG200B'")
                    If rows.Count > 0 Then
                        For i = 0 To rows.Count - 1
                            .AddXY(rows(i).Item("Linename"), rows(i).Item("ChartTime"))
                        Next

                    End If
                End With
            End With

            With s3
                .ChartArea = "myarea"
                .Name = "KE2060"
                .ChartType = SeriesChartType.StackedColumn
                .XValueType = ChartValueType.String
                .YValueType = ChartValueType.DateTime


                With .Points
                    Dim rows() As DataRow = grpTot.Select("McName = 'KE2060'")
                    If rows.Count > 0 Then
                        For i = 0 To rows.Count - 1
                            .AddXY(rows(i).Item("Linename"), rows(i).Item("ChartTime"))
                        Next

                    End If
                End With
            End With

            With s4
                .ChartArea = "myarea"
                .Name = "SIF130"
                .ChartType = SeriesChartType.StackedColumn
                .XValueType = ChartValueType.String
                .YValueType = ChartValueType.DateTime


                With .Points
                    Dim rows() As DataRow = grpTot.Select("McName = 'SIF130'")
                    If rows.Count > 0 Then
                        For i = 0 To rows.Count - 1
                            .AddXY(rows(i).Item("Linename"), rows(i).Item("ChartTime"))
                        Next

                    End If
                End With
            End With


            With s5
                .ChartArea = "myarea"
                .Name = "SIF209"
                .ChartType = SeriesChartType.StackedColumn
                .XValueType = ChartValueType.String
                .YValueType = ChartValueType.DateTime


                With .Points
                    Dim rows() As DataRow = grpTot.Select("McName = 'SIF209'")
                    If rows.Count > 0 Then
                        For i = 0 To rows.Count - 1
                            .AddXY(rows(i).Item("Linename"), rows(i).Item("ChartTime"))
                        Next

                    End If
                End With
            End With


            With s6
                .ChartArea = "myarea"
                .Name = "HT122"
                .ChartType = SeriesChartType.StackedColumn
                .XValueType = ChartValueType.String
                .YValueType = ChartValueType.DateTime


                With .Points
                    Dim rows() As DataRow = grpTot.Select("McName = 'HT122'")
                    If rows.Count > 0 Then
                        For i = 0 To rows.Count - 1
                            .AddXY(rows(i).Item("Linename"), rows(i).Item("ChartTime"))
                        Next

                    End If
                End With
            End With

            With s7
                .ChartArea = "myarea"
                .Name = "CM212M"
                .ChartType = SeriesChartType.StackedColumn
                .XValueType = ChartValueType.String
                .YValueType = ChartValueType.DateTime


                With .Points
                    Dim rows() As DataRow = grpTot.Select("McName = 'CM212M'")
                    If rows.Count > 0 Then
                        For i = 0 To rows.Count - 1
                            .AddXY(rows(i).Item("Linename"), rows(i).Item("ChartTime"))
                        Next

                    End If
                End With
            End With



            With s8
                .ChartArea = "myarea"
                .Name = "CM301DS"
                .ChartType = SeriesChartType.StackedColumn
                .XValueType = ChartValueType.String
                .YValueType = ChartValueType.DateTime


                With .Points
                    Dim rows() As DataRow = grpTot.Select("McName = 'CM301DS'")
                    If rows.Count > 0 Then
                        For i = 0 To rows.Count - 1
                            .AddXY(rows(i).Item("Linename"), rows(i).Item("ChartTime"))
                        Next

                    End If
                End With
            End With

            With s9
                .ChartArea = "myarea"
                .Name = "CM202DH"
                .ChartType = SeriesChartType.StackedColumn
                .XValueType = ChartValueType.String
                .YValueType = ChartValueType.DateTime


                With .Points
                    Dim rows() As DataRow = grpTot.Select("McName = 'CM202DH'")
                    If rows.Count > 0 Then
                        For i = 0 To rows.Count - 1
                            .AddXY(rows(i).Item("Linename"), rows(i).Item("ChartTime"))
                        Next

                    End If
                End With
            End With


            With s10
                .ChartArea = "myarea"
                .Name = "GSM"
                .ChartType = SeriesChartType.StackedColumn
                .XValueType = ChartValueType.String
                .YValueType = ChartValueType.DateTime


                With .Points
                    Dim rows() As DataRow = grpTot.Select("McName = 'GSM'")
                    If rows.Count > 0 Then
                        For i = 0 To rows.Count - 1
                            .AddXY(rows(i).Item("Linename"), rows(i).Item("ChartTime"))
                        Next

                    End If
                End With
            End With

            With Chart4.ChartAreas("myarea")
                .AxisX.LabelStyle.Format = "G"
                .AxisX.LabelStyle.Interval = 1
                .AxisY.LabelStyle.Format = "HH:mm:ss"
                .AxisX.LabelStyle.IntervalType = DateTimeIntervalType.Auto
                .AxisX.Title = "LINE"
                .AxisY.Title = "AVERAGE LOST TIME (hh:mm:ss)"
                .AxisY.Minimum = DateTime.FromOADate( _
                        Math.Floor(CDate(grpTot.Compute("min([ChartTime])", "True")).ToOADate)) _
                       .ToOADate


            End With

            Chart4.Series.Add(s1)
            Chart4.Series.Add(s2)
            Chart4.Series.Add(s3)
            Chart4.Series.Add(s4)
            Chart4.Series.Add(s5)
            Chart4.Series.Add(s6)
            Chart4.Series.Add(s7)
            Chart4.Series.Add(s8)
            Chart4.Series.Add(s9)
            Chart4.Series.Add(s10)


            Me.Controls.Add(Chart4)




        Catch ex As OracleException

            MsgBox("Error: " & ex.ToString())

        End Try

Here is the output:

And here is the output of datatable: