?
Solved

Setting year axis labels on vb.net graphs

Posted on 2014-08-01
6
Medium Priority
?
932 Views
Last Modified: 2014-08-01
I use the vb charting quite a lot and usually my axes are numeric so i don't have this problem - typical code below...

In this case, i want to have years along the x axis 2005 to 2014, always 10 years (1st year 10 years ago, last year current year).

How can i change the code to make the x axis look like that?

                SQL.RunQuery("SELECT OEE As OEE, Week As Week FROM qry_OEE_Weekly")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    Chart1.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Weekly")
                Chart1.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Weekly")
                Dim Series1 As Series = Chart1.Series(0)
                Series1.Name = "Weekly OEE"
                Chart1.Series(Series1.Name).XValueMember = "Week"
                Chart1.Series(Series1.Name).YValueMembers = "OEE"
                Chart1.ChartAreas(0).AxisX.Minimum = 0
                Chart1.ChartAreas(0).AxisX.Maximum = 52
                Chart1.ChartAreas(0).AxisY.Minimum = 0
                Chart1.ChartAreas(0).AxisY.Maximum = 100
                Chart1.ChartAreas(0).AxisX.Interval = 1
                Chart1.ChartAreas(0).AxisY.Interval = 20
                Chart1.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 10)
                Chart1.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 10)
                Chart1.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                Chart1.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                Exit Sub

Thanks for the help
0
Comment
Question by:SweetingA
  • 4
  • 2
6 Comments
 

Author Comment

by:SweetingA
ID: 40235363
More or less got it all to work, see code below but one strange effect.

The first and last bar are cut in half width as the bars go on the line rather than between the lines, am i missing something?

I don;t want to add extra years in the range to solve this problem.

'Yearly OEE
            ElseIf cboPeriodSelector.SelectedIndex = 4 Then
                SQL.RunQuery("SELECT OEE As OEE, Year As Year FROM qry_OEE_Yearly")
                If SQL.recordcount = 0 Then
                    MsgBox("No records available for this selection", vbExclamation + vbOKOnly, "Message")
                    Chart1.Series(0).Points.Clear()
                    Exit Sub
                End If
                SQL.SQLDA.Fill(SQL.SQLDataSet, "qry_OEE_Yearly")
                Chart1.DataSource = SQL.SQLDataSet.Tables("qry_OEE_Yearly")
                Dim Series1 As Series = Chart1.Series(0)
                Series1.Name = "Yearly OEE"
                Chart1.Series(Series1.Name).XValueMember = "Year"
                Chart1.Series(Series1.Name).YValueMembers = "OEE"
                Dim sd As Integer
                Dim ed As Integer
                sd = Date.Today.Year
                ed = sd - 10
                Chart1.Series(0).CustomProperties = "DrawingStyle = Cylinder ,PixelPointWidth = 15"
                Chart1.ChartAreas(0).AxisX.Maximum = sd
                Chart1.ChartAreas(0).AxisX.Minimum = ed
                Chart1.ChartAreas(0).AxisY.Minimum = 0
                Chart1.ChartAreas(0).AxisY.Maximum = 100
                Chart1.ChartAreas(0).AxisX.Interval = 1
                Chart1.ChartAreas(0).AxisY.Interval = 20
                Chart1.ChartAreas(0).AxisX.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                Chart1.ChartAreas(0).AxisY.TitleFont = New Font("Arial", 10, FontStyle.Bold)
                Chart1.ChartAreas(0).AxisX.LabelStyle.Font = New Font("Arial", 10)
                Chart1.ChartAreas(0).AxisY.LabelStyle.Font = New Font("Arial", 10)
                Chart1.ChartAreas(0).AxisX.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                Chart1.ChartAreas(0).AxisY.MajorGrid.LineDashStyle = DataVisualization.Charting.ChartDashStyle.Dash
                Chart1.ChartAreas(0).AxisX.LabelStyle.Angle = -45
                Exit Sub
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 40235409
I don;t want to add extra years in the range to solve this problem.
The thing is, when you don't set a Minimum and Maximum (but make sure your dataset contains only data for the correct period), the Chart will normally display an extra year left and right to show the full cylinders. You can hide the labels by adding a Customize event handler (see below). If you still want to set the min/max, you would still add/subtract 1:
        Chart1.ChartAreas(0).AxisX.Maximum = sd + 1
        Chart1.ChartAreas(0).AxisX.Minimum = ed - 1

Open in new window

but selecting the right data would be a cleaner solution.

The event handler code:
    Private Sub Chart1_Customize(sender As System.Object, e As System.EventArgs) Handles Chart1.Customize
        Chart1.ChartAreas(0).AxisX.CustomLabels.RemoveAt(Chart1.ChartAreas(0).AxisX.CustomLabels.Count - 1)
        Chart1.ChartAreas(0).AxisX.CustomLabels.RemoveAt(0)
    End Sub

Open in new window

capture
0
 

Author Comment

by:SweetingA
ID: 40235423
I get an error tyying to do that

Index was out of range. Must be non-negative and less than the size of the collection.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:SweetingA
ID: 40235436
It is exactly what i want to do evem though its a little odd becasue when i run for instance days of teh week 1-7, i can't add a 0 and 8.  When i run years i also need to suppress the view of the data in the first point not just the title view.

Thanks for the help.
0
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 2000 total points
ID: 40235455
Do you get that error in the Customize function? Maybe you need to make sure it doesn't execute before the data is set. You could do that by attaching the handler dynamically or simply by checking in the handler:
    Private Sub Chart1_Customize(sender As System.Object, e As System.EventArgs) Handles Chart1.Customize
        If Chart1.ChartAreas(0).AxisX.CustomLabels.Count > 1 Then
            Chart1.ChartAreas(0).AxisX.CustomLabels.RemoveAt(Chart1.ChartAreas(0).AxisX.CustomLabels.Count - 1)
            Chart1.ChartAreas(0).AxisX.CustomLabels.RemoveAt(0)
        End If
    End Sub

Open in new window

So the data point for 2003 would have to be zeroed out in this case if I understand you correctly. First can you confirm that it's not possible to filter the data so only the correct years are in the data set?
0
 

Author Closing Comment

by:SweetingA
ID: 40235461
Yep, that's it - your a genius  - thanks a lot for the quick response
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question