Myujth

i am a beginner in vb. i have a datagridview which the data extract from excel file. My data look like this
. I want to create a column chart where x-axis is Department and y-axis is total of status ticket that is open and close.Below is my code which i have stuck and i know i get errors but this is the idea.

i want the result like this

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

 Me.Chart1.Series("Open").Points.AddXY("Finance", value)
 Me.Chart1.Series("Open").Points.AddXY("ITMS", value)
 Me.Chart1.Series("Open").Points.AddXY("RV", value)
 Me.Chart1.Series("Open").Points.AddXY("Security", value)

 Me.Chart1.Series("Close").Points.AddXY("Finance", value)
 Me.Chart1.Series("Close").Points.AddXY("ITMS", value)
 Me.Chart1.Series("Close").Points.AddXY("RV", value)
 Me.Chart1.Series("Close").Points.AddXY("Security", value)



 For Count As Integer = 0 To DataGridView1.Rows.Count - 2
 Chart1.Series("Open").Points.AddXY(DataGridView1.Item(1, Count).Value, DataGridView1.Item(1, Count).Value)
 Chart1.Series("Close").Points.AddXY(DataGridView1.Item(1, Count).Value, DataGridView1.Item(1, Count).Value)

 Next
End Sub

End Class

From what I can decipher from the pictures of the data and the chart… you want the number of “open-close” tickets from “Finance” for the first two data points in the chart. The other points are ITMS, RV and Security “departments.” The problem with the posted code is that the current loop is not “adding” the “open” tickets or “closed” tickets for each department. The code is simply “adding” another point to the chart instead of “adding 1” to the existing point.

Tracing the posted for loop using the picture of the grid data, this will add a total of nine (9) points in the chart, the “SAME” nine points are in both the Open/Close” series. However, since departments like “Finance” have three (3) points with the same value, this will display in the chart as three (3) separate “Finance” “departments” … it is here that you need to “add” 1 to the value of the existing “Finance-open/close” point and not simply add “another” “Finance-open/close” point.

The code will need to loop through the grids rows and accumulate the total number of rows that have “Finance” AND “Open”, “Finance” AND “Close”, “ITMS” AND “Open” … etc. This will give you the charts “y” values for each department-open/close. Given this, one possible solution is to write a method that takes two string parameters (department, status) and returns an integer that contains the numbers of rows that match “both” the department and status. These would become the data points for the chart. In this example, you would only be adding eight (8) data points, One for “Finance” “Open” another for “Finance” “Close” … etc... I hope that makes sense.

Below is a simple loop through the grids rows that accumulates the eight points. The method described above is not necessarily the best approach if the data set is large. The described method would loop through the grid a total of eight (8) times. Once for “Finance” and “Open”, another loop through the grid to get “Finance” “Close” … etc. The code below loops through the grid only “once”, however this approach requires the addition of the 8 variables. Pick your poison… hope this helps.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
 ' 8 variables to hold the totals
 Dim FinanceOpenCount = 0
 Dim FinanceCloseCount = 0
 Dim ITMSOpenCount = 0
 Dim ITMSCloseCount = 0
 Dim RVOpenCount = 0
 Dim RVCloseCount = 0
 Dim SecurityOpenCount = 0
 Dim SecurityCloseCount = 0
 ' loop through each row and "ADD" 1 to the proper variables 
 For Each row As DataGridViewRow In DataGridView1.Rows
 Select Case row.Cells("Department").Value
 Case "Finance"
 If (row.Cells("Status").Value = "Open") Then
 FinanceOpenCount += 1
 Else
 FinanceCloseCount += 1
 End If
 Case "ITMS"
 If (row.Cells("Status").Value = "Open") Then
 ITMSOpenCount += 1
 Else
 ITMSCloseCount += 1
 End If
 Case "RV"
 If (row.Cells("Status").Value = "Open") Then
 RVOpenCount += 1
 Else
 RVCloseCount += 1
 End If
 Case "Security"
 If (row.Cells("Status").Value = "Open") Then
 SecurityOpenCount += 1
 Else
 SecurityCloseCount += 1
 End If
 End Select
 Next
 ' add the points to the chart
 Chart1.Series("Open").Points.Clear()
 Chart1.Series("Close").Points.Clear()
 Chart1.Series("Open").Points.AddXY("Finance", FinanceOpenCount)
 Chart1.Series("Close").Points.AddXY("Finance", FinanceCloseCount)
 Chart1.Series("Open").Points.AddXY("ITMS", ITMSOpenCount)
 Chart1.Series("Close").Points.AddXY("ITMS", ITMSCloseCount)
 Chart1.Series("Open").Points.AddXY("RV", RVOpenCount)
 Chart1.Series("Close").Points.AddXY("RV", RVCloseCount)
 Chart1.Series("Open").Points.AddXY("Security", SecurityOpenCount)
 Chart1.Series("Close").Points.AddXY("Security", SecurityCloseCount)
End Sub

Multiple looping method described above.

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
 Chart2.Series("Open").Points.Clear()
 Chart2.Series("Close").Points.Clear()
 Chart2.Series("Open").Points.AddXY("Finance", GetDeptStatusCount("Finance", "Open"))
 Chart2.Series("Open").Points.AddXY("ITMS", GetDeptStatusCount("ITMS", "Open"))
 Chart2.Series("Open").Points.AddXY("RV", GetDeptStatusCount("RV", "Open"))
 Chart2.Series("Open").Points.AddXY("Security", GetDeptStatusCount("Security", "Open"))

 Chart2.Series("Close").Points.AddXY("Finance", GetDeptStatusCount("Finance", "Close"))
 Chart2.Series("Close").Points.AddXY("ITMS", GetDeptStatusCount("ITMS", "Close"))
 Chart2.Series("Close").Points.AddXY("RV", GetDeptStatusCount("RV", "Close"))
 Chart2.Series("Close").Points.AddXY("Security", GetDeptStatusCount("Security", "Close"))
End Sub

Private Function GetDeptStatusCount(department As String, status As String) As Int32
 Dim count = 0
 For Each row As DataGridViewRow In DataGridView1.Rows
 If (row.Cells("Department").Value = department And row.Cells("Status").Value = status) Then
 count += 1
 End If
 Next
 Return count
End Function