I have found the charts that come with Visual Studio very useful but do not provide what I need. For instance, the chart displays the columns chosen from the database but also only shows percentages for the displayed columns. In my case the results are very misleading.
I am drawing my data from a database of customers and the value of their total purchases each for the year. Let us say that I have 100 customers and the total paid by those customers is 500,000 in the local currency. Displaying 100 customers in a single pie chart would be totally impractical. My aim is to select the top 8 from a data table and display them in a pie chart BUT show the percentage for each customer as a percentage of the of the 500,000. The chart that comes with Visual Studio takes the total value for the 8 customers only which makes the results basically useless.
Here is the code for what I have at present. Note that I have provided a numeric up/down counter to provide greater resolution of the data if needed. Please ignore the value of the counter as this was used to prove that it could work.
Snippet
Imports System.Data.SqlClient
Imports System.Linq
Imports System.Windows.Forms.DataVisualization.Charting
Public Class PieChart2
Dim con As SqlConnection = ConnectionString.GetConnection
Dim dt2 As DataTable
Dim dt As DataTable
Dim ds As DataSet
Dim X As Integer
Private Sub PieChart2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.MdiParent = MDIParent1
Me.Width = 1240
Me.Height = 640
Me.Left = (MdiParent.Width - Me.Width - 27) / 2
Me.Top = (MdiParent.Height - Me.Height - 80) / 2
' Me.Height = 600
Me.BackColor = Color.White
Panel1.BackColor = Color.Azure
'Set the default number of records to 5
X = 5
NumericUpDown1.Value = X
'Make chart 3D
Chart1.ChartAreas(0).Area3DStyle.Enable3D = True
'Create a title for the Pie Chart
Dim Area1Title As Title = New Title("Sales", Docking.Top, New Font("Arial", 14), Color.Black)
Area1Title.DockedToChartArea = Chart1.ChartAreas(0).Name
Chart1.Titles.Add(Area1Title)
End Sub
Private Sub btnGetData_Click(sender As Object, e As EventArgs) Handles btnGetData.Click
' Dim GraphQuery As String = "SELECT format(date, 'yyyy/MM') AS Date, SUM(Income) AS Income, SUM(Expense) AS Expense FROM [Test] GROUP BY format(date, 'yyyy/MM')" '
Dim GraphQuery As String = "SELECT SUM(Amount) AS Amount, CustName FROM AccountsReceivable WHERE Date >= @Date1 AND date < @Date2 GROUP BY CustName ORDER BY Amount Desc"
'GROUP BY CustName 'WHERE Date >= @Date1 AND date < @Date2 'group by CustName ', format(date, 'yyyy/MM')
Dim cmd3 As SqlCommand = New SqlCommand(GraphQuery, con)
cmd3.Parameters.Add("@Date1", SqlDbType.DateTime).Value = dtpStart.Value
cmd3.Parameters.Add("@Date2", SqlDbType.DateTime).Value = dtpEnd.Value
con.Open()
Try
Dim da As SqlDataAdapter = New SqlDataAdapter(cmd3)
'The following 3 lines of code came fron:
'https://stackoverflow.com/questions/9028029/how-to-change-datatype-of-a-datacolumn-in-a-datatable
'Convert a decimal value in the Income column to Integer
Dim dt As New DataTable("Amount")
da.FillSchema(dt, SchemaType.Source)
dt.Columns(0).DataType = GetType(Int32)
'Fill the datatable with the converted values
da.Fill(dt)
con.Close()
If dt.Rows.Count = 0 Then
MessageBox.Show("There is no data for the chosen dates", "Error")
End If
If dt.Rows.Count > 0 Then
'Get the total amount from all customers
Dim sumObject As Object
sumObject = dt.Compute("Sum(Amount)", "")
TextBox1.Text = sumObject.ToString
Label4.Text = sumObject.ToString
X = NumericUpDown1.Value
'Get the first number of rows in dt
dt2 = dt.AsEnumerable().Take(X).CopyToDataTable()
'dt.AsEnumerable().Reverse().Take(5).CopyToDataTable()
Label3.Text = dt2.Rows.Count
'MessageBox.Show("You have data")
DataGridView1.DataSource = dt2
'DataGridView1.Columns("Amount").DefaultCellStyle.Format = "N0"
DataGridView1.Columns("Amount").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
'CONNECT CHART WITH DT
'From:https://www.syncfusion.com/kb/7684/how-to-bind-chart-with-data-from-sql-database
Me.Chart1.DataSource = dt2
Me.Chart1.DataBind()
Me.Chart1.Series("Sales").XValueMember = "CustName"
Me.Chart1.Series("Sales").YValueMembers = "Amount"
Chart1.Series(0).LegendText = "#VAL (#PERCENT)"
'From:https://www.youtube.com/watch?v=xNr-L7ILEmk
'You can also place the Pie chart labels outside of the Chart. See above video
Chart1.Series(0)("PieLabelStyle") = "outside"
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
'Close the database connection in the event of a failure
If con.State = ConnectionState.Open Then
con.Close()
End If
End Sub
End Class
I have read an article on your website about creating pie charts but, although it works, it does not go far enough and I need a nudge to help me achieve my goal.