[RESOLVED] MSChart - trying to use total time as YAxis

[WNxTilly]

Hi,

I've been looking all over for this and I'm not sure if it's going to be possible or not.

I have an MSChart (System.Windows.Forms.DataVisualization.Charting)

I've pulled data from DB and linked a BindingSource to the Datatable I've got the results in. Then binded the BindingSource to the mschart.

One of the columns is "total time" which is total seconds, the other column is "person name"

The bar chart looks fine but the values are massive numbers that don't really mean anything to most people. (i.e. 2132343 seconds)
Is there a way to make this show as (Like in Excel [h]:mm:ss) a total time. So total hours:mins:secs?

Any ideas?

Cheers

Tilly

[WNxTilly]

I guess this one isn't possible, I've been googling for days and kept thinking I was close but nothing really worked.

Anyone have any ideas?

Cheers

Tilly

[WNxTilly]

If this can't be done then can someone please close this down as there is no point in it being left open (But with a title of Not Resolved)

Cheers

[enrico1982]

It can be done.

I've found the charts very hard to work with directly, so found the best way is to format your data first before you give it to the chart. So in your case, either in your sql statement or after (would be quicker in the sql statement), format your seconds field into how you want, then bind it to the chart.

[WNxTilly]

The format I want is the issue, I want it to be Total Hours:mins:secs and for this to actually work. I can't think how else to do it. (Because the seconds could go over 60*60*24, 24 hours)

I've tried creating a new DataColumn for my DataTable as System.DateTime DataType and setting the value to be a timespan (Timespan.FromSeconds) which works perfectly if it's under 24 hours!

How else would you think to do it?

Cheers

[enrico1982]

It should work for a timespan much greater than that: http://msdn.microsoft.com/en-us/libr....maxvalue.aspx

Post your code, may be some issues there.

[WNxTilly]

Ah Ok,

What DataType should I store in the DataTable that will get binded to the chart? Int64?

Then what should I set the YValueType's ChartValueType to be? Auto? Or set it as Int64?

Then the big question is what should AxisY.LabelStyle.Format be set to so that it can display total hours / mins / secs?

I'll post the code next time if I can't explain it properly this time.

Cheers

[WNxTilly]

Also, the chart object only accepts certain datatypes & Timespan isn't one of them. So what would you suggest to use as the datatype for this field so that it display total hours / mins / secs? Or can I use Int64 as I stated above and use the LabelStyle.Format to format the output in the way I would like?

Sorry to be a pain but it's something I really need to find an answer to!

Thanks again

[enrico1982]

You don't need to set the datatype for the datatable column or the labelstyle. If your data is in the datatable in the format you want it shown, then thats how it will be shown in the chart. I find this a much better was as dealing with mschart axis labels

Again, if you show your code, we may have sorted this a few posts ago.

First look at converting your seconds to a timespan properly.

Then incorperate that into your datatable.

Then work on your chart.

[WNxTilly]

Dim chartDT As New System.Data.DataTable
chartDT = 'Some function that runs a stored proc and returns a datatable with String and Integer as two columns and rows are names and total seconds
chartBS = chartDT

chrtGraphs.ChartAreas(0).Area3DStyle.Enable3D = True
chrtGraphs.Series(0).ChartType = SeriesChartType.Bar
chrtGraphs.Series(0).XValueMember = "Person" 'The string column
chrtGraphs.Series(0).YValueMembers = "TotalTime" 'The integer column
chrtGraphs.Series(0).Label = "#AXISLABEL"

chrtGraphs.DataSource = chartBS

The code above works fine and shows total seconds but obviously what I'd want to show is total time but in the different format as stated previously. Charting doesn't accept timespan so my issue is how do I get it in the format that I want?

Sorry for not posting earlier but it's such basic code that I had hoped I could get an answer quite easily!

Thanks again!

[WNxTilly]

Sorry for not posting the other part!

		Dim TotalDateTime As DataColumn = New DataColumn("TotalTimeSpan", System.Type.GetType("System.Timespan"))
		TopLevelData.Columns.Add(TotalDateTime)

		For I = 0 To TopLevelData.Rows.Count-1
			Dim t As TimeSpan = TimeSpan.FromSeconds(CInt(TopLevelData.Rows(I).Item("Timer Time")))
			TopLevelData.Rows(I).Item("TotalTimeSpan") = t
		Next

This is the code I used to create Timespans in the datatable but then this is the part where I get stuck because the charting system can't use Timespans as XValueMember or YValueMember so what can I use to get it to display how I want? (Surely Strings wont work because how can you graph the string?)

Cheers

[WNxTilly]

Does anyone else maybe have any ideas for this?

I'm still trying to figure this out and I've spent quite a few hours on this already and it would be a massive help if anyone has done this before!

Thanks again

Tilly

[WNxTilly]

Can someone please delete this, the site crashed and doubled posted!

[Inferrd]

Have you considered providing your own axis labels programmatically using the Axis.CustomLabels Property ? I've not actually done this before, but you never know.

[WNxTilly]

Hi Inferrd,

I think this was something I had thought about previously but I think it was again down to the formatting of the labels that I couldn't seem to get to work. Does anyone have any other ideas?

Thanks

Tilly

[Inferrd]

Just incase the equine hasn't quite deceased, one last thrash.

This was what I was playing around with. I think it is along the lines of what you are trying to accomplish.

Start a new Windows Forms Application and place a Chart component on the Form. Give it a reasonably large horizontal size as these labels are long. Then try the following code:

Imports System.Windows.Forms.DataVisualization.Charting
 
Public Class Form1
 
 
    Private chartDT As DataTable
    Private showingCustomisedLabels As Boolean = False
 
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        'incase we're on 64 bit OS
        Try
 
            '   Create some data
            chartDT = New System.Data.DataTable
            chartDT.Columns.Add("Person", GetType(String))
            chartDT.Columns.Add("TotalTime", GetType(Long))
            chartDT.Rows.Add("Fred", 45678)
            chartDT.Rows.Add("Bob", 94561)
            chartDT.Rows.Add("Jim", 64576)
            chartDT.Rows.Add("Alex", 83457)
 
            '   initialise the chart
            Chart1.ChartAreas(0).Area3DStyle.Enable3D = True
            Chart1.Series(0).ChartType = SeriesChartType.Bar
            Chart1.Series(0).XValueMember = "Person"
            Chart1.Series(0).YValueMembers = "TotalTime"
            Chart1.Series(0).Label = "#AXISLABEL"
 
            '   add the data to the chart
            Chart1.DataSource = chartDT
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
 
 
    Private Sub CustomiseYaxis()
        Dim yAxis As Axis = Chart1.ChartAreas(0).AxisY
 
        yAxis.CustomLabels.Clear()
        Dim labelInterval = yAxis.LabelStyle.Interval
 
        For value = yAxis.Minimum To yAxis.Maximum Step labelInterval
            Dim ts = TimeSpan.FromSeconds(value)
            yAxis.CustomLabels.Add(value - labelInterval / 2, _
                              value + labelInterval / 2, _
                              String.Format("{0}h:{1}m:{1}s", ts.Days * 24 + ts.Hours, ts.Minutes, ts.Seconds))
        Next
    End Sub
 
 
    Private Sub Chart1_Customize(sender As Object, e As System.EventArgs) Handles Chart1.Customize
        If Not showingCustomisedLabels Then
            CustomiseYaxis()
            showingCustomisedLabels = True
        End If
    End Sub
 
End Class

I found that getting the labels to show was somewhat troublesome (not a Chart guru, remember) as the chart has to have been drawn before you can access the necessary properties. However, the Customize event seems to be raised whenever the chart is drawn/redrawn/resized etc., so I picked its event handler to call a method that generates the custom labels. The intent of the global boolean is to stop the method being rerun every time the chart is resized and so on.

Maybe you can adapt this to your own needs.

[WNxTilly]

Thanks,

I'll take a look this week and let you know how I get on!

Tilly

[WNxTilly]

Hi,

I think this is exactly what I'm looking for! Thank you so much, I didn't know about the customize function and how that could change the axis formats. I've tried just doing the String Formatting on the axis but that never worked!

Thanks again for you help!

Tilly