How to show the Datatable in Chart?
I would like to shown the data in table form with the chart together, the data were gathered from the GridView as follows:
Here is the coding of my chart:
Protected Sub OnGenerate(ByVal sender As Object, ByVal e As EventArgs)
Dim dt As DataTable = New DataTable()
dt.Columns.Add("Department")
dt.Columns.Add("Period_Shown")
dt.Columns.Add("Num_Int")
dt.Rows.Add("Dept_AED(EMW)", "2018_10", 0)
dt.Rows.Add("Dept_AED(EMW)", "2018_11", 2.89)
dt.Rows.Add("Dept_AED(EMW)", "2018_12", 0)
dt.Rows.Add("Dept_AED(EMW)", "2019_01", 0)
dt.Rows.Add("Dept_MG", "2017_01", 0.61)
dt.Rows.Add("Dept_MG", "2017_02", 0.35)
dt.Rows.Add("Dept_MG", "2017_03", 0.45)
dt.Rows.Add("Dept_MG", "2017_04", 0.66)
gvInt.DataSource = dt
gvInt.DataBind()
Dim dtInt As DataTable = New DataTable("dtInts")
For Each cell As TableCell In gvInt.HeaderRow.Cells
dtInt.Columns.Add(cell.Text.Trim())
Next
For Each row As GridViewRow In gvInt.Rows
dtInt.Rows.Add()
Dim k As Integer = 0
For k = 0 To row.Cells.Count - 1
dtInt.Rows(row.RowIndex)(k) = row.Cells(k).Text.Trim()
Next
Next
Dim departments As List(Of String) = New List(Of String)()
departments = (From p In dtInt.AsEnumerable()
Select p.Field(Of String)("Department")).Distinct().ToList()
If RateChart.Series.Count() = 1 Then
RateChart.Series.Remove(RateChart.Series(0))
End If
Dim i As Integer = 0
For i = 0 To departments.Count - 1 Step 1
Dim x As String() = (From p In dtInt.AsEnumerable()
Order By p.Field(Of String)("Period_Shown")
Select p.Field(Of String)("Period_Shown")).Distinct().ToArray()
Dim y As List(Of Decimal) = New List(Of Decimal)()
Dim j As Integer = 0
For j = 0 To x.Length - 1 Step 1
Dim numInt As Decimal = (From p In dtInt.AsEnumerable()
Where p.Field(Of String)("Department") = departments(i) _
AndAlso p.Field(Of String)("Period_Shown") = x(j)
Order By p.Field(Of String)("Period_Shown")
Select Convert.ToDecimal(p.Field(Of String)("Num_Int"))).FirstOrDefault()
y.Add(numInt)
Next
RateChart.Series.Add(New Series(departments(i)))
RateChart.Series(departments(i)).IsValueShownAsLabel = False
RateChart.Series(departments(i)).BorderWidth = 3
RateChart.Series(departments(i)).ChartType = SeriesChartType.Line
RateChart.Series(departments(i)).Points.DataBindXY(x, y)
RateChart.Series(departments(i)).MarkerStyle = MarkerStyle.Star10
Next
RateChart.Legends.Add("Default")
RateChart.Legends(0).Docking = Docking.Bottom
RateChart.Legends(0).IsTextAutoFit = True
RateChart.Legends(0).Alignment = Drawing.StringAlignment.Center
RateChart.Legends(0).Enabled = True
RateChart.ChartAreas(0).AxisX.Interval = 1
RateChart.ChartAreas(0).AxisX.Title = "Period"
RateChart.ChartAreas(0).AxisX.TitleForeColor = System.Drawing.Color.Red
RateChart.ChartAreas(0).AxisY.Title = "Number of incidents"
RateChart.ChartAreas(0).AxisY.TitleForeColor = Drawing.Color.Red
RateChart.ChartAreas(0).AxisY.TitleAlignment = Drawing.StringAlignment.Far
RateChart.ChartAreas(0).AxisY.TextOrientation = TextOrientation.Rotated90
End Sub
Here is the Datatable format which I need to display:
|
2017_01 |
2017_02 |
2017_03 |
2017_04 |
2017_05 |
2017_06 |
2017_07 |
2017_08 |
2017_09 |
2017_10 |
2017_11 |
2017_12 |
2018_01 |
2018_02 |
2018_03 |
2018_04 |
2018_05 |
2018_06 |
2018_07 |
2018_08 |
2018_09 |
2018_10 |
2018_11 |
2018_12 |
2019_01 |
2019_02 |
2019_03 |
2019_04 |
2019_05 |
2019_06 |
2019_07 |
2019_08 |
2019_09 |
2019_10 |
2019_11 |
2019_12 |
Dept_AED(EMW) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Dept_MG |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|