Solved

Setting year axis labels on vb.net graphs

Posted on 2014-08-01
6
805 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 500 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now