Cannot get time xaxis labels to format

Thread is closed for posting
6 posts, 0 answers
  1. Jeanne
    Jeanne avatar
    58 posts
    Member since:
    Sep 2011

    Posted 18 May 2012 Link to this post

    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)

  2. Jeanne
    Jeanne avatar
    58 posts
    Member since:
    Sep 2011

    Posted 18 May 2012 Link to this post

    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.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Jeanne
    Jeanne avatar
    58 posts
    Member since:
    Sep 2011

    Posted 18 May 2012 Link to this post

    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.
  5. Peshito
    Admin
    Peshito avatar
    496 posts

    Posted 23 May 2012 Link to this post

    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
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
  6. Jeanne
    Jeanne avatar
    58 posts
    Member since:
    Sep 2011

    Posted 23 May 2012 Link to this post

    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.
  7. Peshito
    Admin
    Peshito avatar
    496 posts

    Posted 28 May 2012 Link to this post

    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
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017