Show annotation when Mouse Move on Chart using VB.Net in Windows Application

Last Reply 2 months ago By dharmendr

Posted 2 months ago

Hi everyone, 

I have this SQL code which I would want to show in my data chart in vb.net as chart1_mouseMove.  

This is my SQL code:

SELECT Sum(Price) As Total,
    DATEPART(yyyy, Date) As [year],
    Department		 
FROM dbo.Expenses
    WHERE DATEPART(yyyy, Date) = '2020' 
GROUP BY DATEPART(yyyy, Date), Department

which shows this information :

259.84 2020 Clothes

860.97 2020 Electronics

12222.72 2020 FixCost

2787.82 2020 Grocery

1187.63 2020 Other

And I would want to show that information in the dots when I move my mouse in this chart.

Thank you very much.

Yearly Data

You are viewing reply posted by: dharmendr 2 months ago.
Posted 2 months ago

Hi elvisidrizi1,

Refer below sample.

Namespaces

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Windows.Forms.DataVisualization.Charting

Code

Public Class Form1    
    Dim constr As String = "Data Source=####/SQLEXPRESS;Initial Catalog=Expenses;Trusted_Connection=yes;"
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        LoadYearData()
    End Sub

    Private Sub LoadYearData()
        Chart1.DataSource = GetData1()
        Chart1.Series("Chart1").Points.Clear()
        Chart1.Series("Chart1").XValueMember = "Year"
        Chart1.Series("Chart1").YValueMembers = "Total"
        'Chart1.Series("Chart1").Color = Color.FromArgb(152, 251, 152)

        Chart1.Series("Chart1").IsValueShownAsLabel = True
        Chart1.Series("Chart1").BorderWidth = 2
        Chart1.Series("Chart1").ChartType = SeriesChartType.Spline
        Chart1.Series("Chart1").LabelForeColor = Color.Red
        Chart1.Series("Chart1").EmptyPointStyle.Color = Color.Red
        Chart1.Series("Chart1").EmptyPointStyle.AxisLabel = "Empty"
        Chart1.Series("Chart1").EmptyPointStyle.IsValueShownAsLabel = False
        Chart1.Legends(0).Enabled = False
        Dim MyData As Single() = New Single(Chart1.Series.Count - 1) {}
        For i = 0 To Chart1.Series.Count - 1
            MyData(i) = Convert.ToSingle(5 * Math.Sin(i / CDbl(2)))
            Dim withBlock = Chart1.Series(i)
            withBlock.BorderWidth = 2
            withBlock.MarkerStyle = MarkerStyle.Circle
            withBlock.MarkerSize = 8
            Chart1.Annotations.Add(New CalloutAnnotation())
        Next
    End Sub

    Private Function GetData1() As DataTable
        Dim dtChartData As New DataTable()
        dtChartData.Columns.Add("Year")
        dtChartData.Columns.Add("Total")
        dtChartData.Rows.Add("2016", "182.7")
        dtChartData.Rows.Add("2017", "13372.18")
        dtChartData.Rows.Add("2018", "12975.71")
        dtChartData.Rows.Add("2019", "17346.86")
        dtChartData.Rows.Add("2020", "17318.98")
        dtChartData.Rows.Add("2021", "6948.88")

        Return dtChartData
    End Function

    Private prevPosition As Point? = Nothing
    Private tooltip As ToolTip = New ToolTip()
    Private Sub Chart1_MouseMove(ByVal sender As Object, ByVal e As MouseEventArgs) Handles Chart1.MouseMove
        Dim pos = e.Location
        If prevPosition.HasValue AndAlso pos = prevPosition.Value Then Return
        tooltip.RemoveAll()
        prevPosition = pos
        Dim results = Chart1.HitTest(pos.X, pos.Y, False, ChartElementType.DataPoint)
        For Each result In results
            If result.ChartElementType = ChartElementType.DataPoint Then
                Dim year As String = result.Series.Points(result.PointIndex).AxisLabel
                tooltip.BackColor = Color.LightGray
                tooltip.ToolTipTitle = "Data Information"
                tooltip.Show(GetDetails(year), Chart1, pos.X, pos.Y - 15)
            End If
        Next
    End Sub

    Private Sub toolTip_Draw(ByVal sender As Object, ByVal e As DrawToolTipEventArgs)
        Dim f As Font = New Font("Arial", 8.0F)
        Dim b As Brush = New SolidBrush(tooltip.BackColor)
        e.Graphics.FillRectangle(b, e.Bounds)
        e.DrawBackground()
        e.DrawBorder()
        e.Graphics.DrawString(e.ToolTipText, f, SystemBrushes.ActiveCaptionText, e.Bounds)
    End Sub

    Private Function GetDetails(ByVal year As String) As String
        Dim data As String = ""
        Dim query As String = "SELECT Sum(Price) As Total, DATEPART(yyyy, Date) As [year], Department FROM dbo.Expenses "
        query += "WHERE DATEPART(yyyy, Date) = @Year GROUP BY DATEPART(yyyy, Date), Department"
        Dim dt As DataTable = New DataTable()
        Using con As SqlConnection = New SqlConnection(constr)
            Using command As New SqlCommand(query, con)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@Year", year)
                Using sda As SqlDataAdapter = New SqlDataAdapter(command)
                    sda.Fill(dt)
                End Using
            End Using
        End Using

        For Each row As DataRow In dt.Rows
            Dim department As String = row("Department").ToString().Trim()
            Dim total As String = row("Total").ToString()
            data = data & total & " - " & year & " - " & department & Environment.NewLine
        Next
        Return data
    End Function
End Class

Screenshot