Cannot get time xaxis labels to format

[Jeanne]

I'm displaying a relatively simple chart plotting time of the xaxis and counts on the yaxis. the chart displays fine, but I simply cannot get the xaxis labels to display as time values! I've tried all the suggestions I could find in the forums with no luck.
Here is my aspx file:

<%@ Page Title="Tickets Received by Time of Day" Language="VB" AutoEventWireup="false" CodeFile="TixByTime.aspx.vb" Inherits="Statistics_Misc_TixByTime" MasterPageFile="~/MasterPage.master" %>

<%@ MasterType VirtualPath="~/MasterPage.master" %>
<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Charting" TagPrefix="telerik" %>
<asp:Content ID="cpHeadContent" ContentPlaceHolderID="mpHeadContent" runat="server">
</asp:Content>
<asp:Content ID="cpSideMenuContent" ContentPlaceHolderID="mpSideMenuContent" runat="server">
</asp:Content>
<asp:Content ID="cpMainContent" ContentPlaceHolderID="mpMainContent" runat="server">
    <telerik:RadChart ID="rcChart2" runat="server" Height="500px" Width="900px" Skin="WebBlue">
    </telerik:RadChart>
</asp:Content>

And here is my codebehind:

Imports Workforce
Imports System.Data
Imports Telerik.Charting
Imports System.Data.SqlClient

Partial Class Statistics_Misc_TixByTIme
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        SetupChart()
        LoadData()
    End Sub

    Private Sub SetupChart()
        Dim dStart As Double = DateTime.Today.ToOADate()
        Dim dEnd As Double = DateTime.Today.AddDays(1).ToOADate()
        Dim dHourStep As Double = (1 / 24)
        With rcChart2
            .ChartTitle.TextBlock.Text = "Tickets Received by Time of Day"
            .SeriesOrientation = ChartSeriesOrientation.Vertical
            With .PlotArea.XAxis
                .AutoScale = False
                .IsZeroBased = False
                .LayoutMode = Styles.ChartAxisLayoutMode.Inside
                .AddRange(dStart, dEnd, dHourStep)
                .Appearance.MajorGridLines.Visible = False
                .Appearance.ValueFormat = Styles.ChartValueFormat.ShortTime
                .Appearance.CustomFormat = "hh:mm tt"
                .Appearance.LabelAppearance.RotationAngle = 90
                .Appearance.LabelAppearance.Position.AlignedPosition = Styles.AlignedPositions.Top
            End With
            With .PlotArea.YAxis
                .Step = 1
            End With
            Dim oSeries As New ChartSeries()
            With oSeries
                .Name = "TicketCounts2"
                .Type = ChartSeriesType.Area
                .Appearance.ShowLabels = False
            End With
            .Series.Add(oSeries)
        End With
    End Sub

    Private Sub LoadData()
        Dim oSeries As ChartSeries = rcChart2.Series(0)

        Dim oConn As SqlConnection = Nothing
        Dim oReader As SqlDataReader = Nothing
        Dim oCmd As SqlCommand = New SqlCommand()
        Dim sSQL As String
        Try
            Dim sConn As String = Profile.Company.ConnectionString
            oConn = New SqlConnection(sConn)
            oConn.Open()

            sSQL = <sql>
                SELECT Substring(Convert(nchar(8),recvdatetime,8), 1, 5) AS daTime
                        ,Count(*) AS daCount
                    FROM cwtg..tix4tbl
                    group by substring(convert(nchar(8),recvdatetime,8), 1, 5)
                    order by daTime
            </sql>.Value
            oCmd.CommandText = sSQL
            oCmd.Connection = oConn
            oReader = oCmd.ExecuteReader()
            Do While oReader.Read
                Dim dTime As Double = DateTime.Parse(oReader.GetString(oReader.GetOrdinal("daTime"))).ToOADate()
                Dim dCount As Double = oReader.GetInt32(oReader.GetOrdinal("daCount"))
                Dim oItem As New ChartSeriesItem()
                oItem.XValue = dTime
                oItem.YValue = dCount
                oSeries.Items.Add(oItem)
            Loop
        Finally
            If oReader IsNot Nothing Then oReader.Close()
            oConn.Close()
            oConn.Dispose()
        End Try
    End Sub

End Class

 
The values are all correct, but the xaxis labels display as 41047, 41047.0416, ... 41047.9999 (the last digits of the labels are truncated)

[Jeanne]

I managed to solve the problem by manually setting the text for the labels like this:

Protected Sub rcChart2_BeforeLayout(sender As Object, e As System.EventArgs) Handles rcChart2.BeforeLayout
    With rcChart2.PlotArea.XAxis
        For i As Integer = 0 To .Items.Count - 1
            .Items(i).TextBlock.Text = DateTime.FromOADate(.Items(i).Value).ToString("hh:mm tt")
        Next
    End With
End Sub

However I would still like to know why the method I used before didn't work. It follows the examples carefully.

[Jeanne]

Now I have another issue with the xaxis labels! If I set the RotationAngle to 90 they line up with the axis tick marks perfectly, however if I set the RotationAngle to 45 they appear to be offset to the left by one tick! If I set the RotationAngle to -45 then they appear to be offset to the right by one tick! This happens regardless of the AlignedPosition I set. In fact, the Left/Right aspects of the AlignedPosition (whether with Top or Bottom or alone) appear to have no effect at all. The Top/Bottom/Center aspects only affect the vertical positioning of the labels within the margin area.

I've attached images showing the problem.

[Peshito]

Hello Jeanne,

Onto your question about formatting, when using OLE Automation DateTime equivalent the underlying data should have the DateTime values already converted to double through ToOADate method.
You can use the following line to extract DateTime values converted to OLE Automation date directly from SQL:
"SELECT [Value], CAST([Date] AS FLOAT) + 2 as float_date FROM [Table_1]"

Regarding your issue about the labels position they are positioned properly and respectively to each axis mark. You can however control that, by using the LayoutModeproperty of the X axis specifying how axis marks are positioned with regard to series items. Layout modes change the chart appearance while the underlying data remains the same.. The available LayoutMode values are Normal, Inside and Between.You can try the following approach in your scenario:

radChart.PlotArea.XAxis.LayoutMode = Telerik.Charting.Styles.ChartAxisLayoutMode.Between

Hope this helps.

All the best,
Peshito
the Telerik team

[Jeanne]

Cut and paste answers are certainly not much use. Do you think I didn't search the forums first? I looked at all the examples and tried all the obvious solutions before posting.

I cannot convert to OADate within the select because I am grouping by time (hh:mm) but not date. It is irrelevant anyway, because the chart is not databound. I manually add chart series items, and if you look at my code, I do convert to OADate before I add the values. Also as I mentioned, the VALUES are correct, it is just the xaxis LABELS that are showing incorrectly. They are not being formatted despite my two statements specifying the format (as per your examples).

As for the label positioning, did you look at my images? The labels are NOT being properly positioned when displayed at an angle. They appear quite obviously offset by a significant amount. If you extend the X axis ticks through the labels, it is obvious that the CENTER of the label is being aligned with the tick mark. But when displayed at an angle one edge or the other should align with the tick mark (the left edge when angle is positive, the right edge when angle is negative). Aesthetically that is the way it is done. That is what I expected the AlignedPosition attribute to do for me. Instead it's Left/Right aspects appear to have no effect at all. I think that is a bug. As far as changing the LayoutMode, that is not an option. I do not want the labels laid out between the ticks, I want them laid out ON the ticks, and I want them properly (and aesthetically) aligned.

[Peshito]

Hi Jeanne,

RadChart rotates the labels around their center and this is why the center of the label is aligned with the tick mark. The only workaround I could give you that would make your labels more readable is to use the XAxis LabelStep property and set its value to 3 for example.

Hope this helps.

Greetings,
Peshito
the Telerik team