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

on Dec 08, 2020 01:37 AM

Hi everyone, 

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

This is my SQL code:

SELECT Sum(Price) As Total,
    DATEPART(yyyy, Date) As [year],
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

Download FREE API for Word, Excel and PDF in ASP.Net: Download
on Dec 08, 2020 06:51 AM

Hi elvisidrizi1,

Refer below sample.


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


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
    End Sub

    Private Sub LoadYearData()
        Chart1.DataSource = GetData1()
        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())
    End Sub

    Private Function GetData1() As DataTable
        Dim dtChartData As New DataTable()
        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
        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
    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.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)
                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
        Return data
    End Function
End Class