VB.NET - Creating your own pie charts in VB.net but not using the standard toolbox chart

Asked By Michael Newman on 01-Sep-20 06:01 AM
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 EventArgsHandles 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 EventArgsHandles 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.