Setting DataXColumn for every Databound Series

Thread is closed for posting
9 posts, 0 answers
  1. Michael
    Michael avatar
    7 posts
    Member since:
    Dec 2010

    Posted 13 Jan 2011 Link to this post

    Hi,

    I'm trying to put together a line chart showing transactions per day over a period of time, separated out by store.  I noticed that the dates on the X-axis are not correct when compared to the actual data.  The dates on the bottom of the page don't scale, (instead it just uses each date point, so a hash mark can be one day, then jump to a week) and rather than place the datapoint at the right date, it just places it on the next one over.

    When I set the DataXColumn setting for some hardcoded series, it worked fine.  So, is there a way to set the DataXColumn for each series and have the chart display the correct dates?  My code is below.

    <telerik:RadChart runat="server" ID = "chartTransactions" AutoLayout="true" DataSourceID="datTransactions" DefaultType="Line" DataGroupColumn = "Store" Width="800" >
            <ChartTitle><TextBlock Text = "Transactions - Store"></TextBlock></ChartTitle>      
            <PlotArea>
                <XAxis DataLabelsColumn = "AppRejDate" >
                    <Appearance ValueFormat="ShortDate">
                        <LabelAppearance RotationAngle="270">
                        </LabelAppearance>
                    </Appearance>
                </XAxis>
            </PlotArea>
        </telerik:RadChart>
    <asp:SqlDataSource ID = "datTransactions" runat="server" ConnectionString="<%$ ConnectionStrings:DBString %>" SelectCommand = "Select tblStore_Ref.Description, CAST(DATEADD(Day, DATEDIFF(Day, 0, AppRejDateTime), 0) AS Float) + 2 AS AppRejDate, Count(*) as Transactions From tblTransactionHistory INNER JOIN tblTransactionItems ON tblTransactionHistory.TransactionID = tblTransactionItems.TransactionID INNER JOIN tblStore_Ref ON tblTransactionItems.Store = tblStore_Ref.Store Where Status = 'A' AND AppRejDateTime Between @dateBegin AND @dateEnd Group By DATEADD(Day, DATEDIFF(Day, 0, AppRejDateTime), 0), tblStore_Ref.Description Order By AppRejDate, tblStore_Ref.Description">
            <SelectParameters>
                <asp:ControlParameter ControlID = "dateBegin" Name = "dateBegin" PropertyName="SelectedDate" />
                <asp:ControlParameter ControlID = "dateEnd" Name = "dateEnd" PropertyName="SelectedDate" />
            </SelectParameters>
        </asp:SqlDataSource>

    Some sample data:

    40513   S-2   1
    40518   S-2   2
    40519   S-2   1
    40549   S-2   4
    40553   S-2   3

    Thanks.
  2. Michael
    Michael avatar
    7 posts
    Member since:
    Dec 2010

    Posted 14 Jan 2011 Link to this post

    Updated the previous post, but I'm having major issues with getting a Date line chart working properly.

    The bottom X-axis isn't scaling, it just has different hash marks corresponding to different data point's dates and the datapoints aren't matching up with the right date.
  3. Michael
    Michael avatar
    7 posts
    Member since:
    Dec 2010

    Posted 18 Jan 2011 Link to this post

    I figured out how to scale the X-Axis with Dates.  I had to use an AddRange during the DataBound event like so:

    Protected Sub chartTransactions_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles chartTransactions.DataBound
            Dim dblBegin As Double, dblEnd As Double
            dblBegin = DateTime.Parse(dateBegin.SelectedDate).Date.ToOADate
            dblEnd = DateTime.Parse(dateEnd.SelectedDate).Date.ToOADate
            chartTransactions.PlotArea.XAxis.AutoScale = False
            chartTransactions.PlotArea.XAxis.IsZeroBased = False
            chartTransactions.PlotArea.XAxis.AddRange(dblBegin, dblEnd, 2)
     
        End Sub

    And my XAxis works awesome.  Now, my only problem is getting the created series to use the dates as the X Column and not just put them in the order the datasource returns.

    I tried using the DataManager.ValuesXColumn but that seems to have no effect.  I'll keep plugging away, but this is much harder than I thought it would be.
  4. Yavor
    Admin
    Yavor avatar
    401 posts

    Posted 19 Jan 2011 Link to this post

    Hi Michael,

    RadChart treats your dates as categories because  you are mapping your DateTime column to the DataLabelsColumn property of the Axis-X. If you map it to the DataXColumn the dates will appear normally. You can set the DataXColumn on the series like this:

    ChartSeries series = new ChartSeries();
    series.Type = ChartSeriesType.Line;
    series.DataYColumn = "Value";
    series.DataXColumn = "Date";

    Series are mapped to data as it is set to the datasource. RadChart doesn't sort your data, so you have to sort your data by the date column that your axis-X is mapped to prior to setting it to the datasource. You can do this using SQL in your query or perhaps you can do it in code behind using LINQ. Feel free to choose the approach that suits you best.

    I have attached a small sample project that demonstrates how to bind RadChart to a collection of objects using DateTime as value for X-axis.

    Hope this helps!


    All the best,
    Yavor Ivanov
    the Telerik team
    Browse the vast support resources we have to jump start your development with RadControls for ASP.NET AJAX. See how to integrate our AJAX controls seamlessly in SharePoint 2007/2010 visiting our common SharePoint portal.
  5. Michael
    Michael avatar
    7 posts
    Member since:
    Dec 2010

    Posted 19 Jan 2011 Link to this post

    I knew I could set it to the DataXColumn manually, but the problem was that I wanted multiple series created based on the data.  So, if there were 3 categories, I wanted 3 lines and I couldn't figure out how to set the DataXColumn on each of those 3 series.

    The solution turned out to be the DataManager:

    Protected Sub chartTransactions_DataBinding(ByVal sender As Object, ByVal e As System.EventArgs) Handles chartTransactions.DataBinding
            chartTransactions.DataManager.ValuesXColumn = "AppRejDate"
        End Sub

    It had to be done in the DataBinding for it to work, but now everything works properly.

    When I have time, I'll post my full solution.
  6. Michael
    Michael avatar
    7 posts
    Member since:
    Dec 2010

    Posted 19 Jan 2011 Link to this post

    As promised, the full solution:  A Line Chart with a Scaling Date X-Axis and multiple series based on database results.

    .aspx file

    <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Dash_Transactions.aspx.vb" Inherits="Dash_Transactions" Title="Dashboard: Transactions" %>
    <%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Charting" TagPrefix="telerik" %>

    <table>
        <tr>
            <td>
                <asp:Label ID="lblStartDate" runat="server" Text="Starting Date" Font-Names="Tahoma"></asp:Label>
                <telerik:RadDatePicker runat="server" ID = "dateBegin"></telerik:RadDatePicker>
                <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator1" ControlToValidate="dateBegin"
                    ErrorMessage="Enter a date!" Display="Dynamic"></asp:RequiredFieldValidator>
            </td>
            <td>
                <asp:Label ID="lblEndDate" runat="server" Text="Ending Date" Font-Names="Tahoma"></asp:Label>
                <telerik:RadDatePicker runat="server" ID = "dateEnd"></telerik:RadDatePicker>
                 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator2" ControlToValidate="dateEnd"
                    ErrorMessage="Enter a date!" Display="Dynamic"></asp:RequiredFieldValidator>
                <asp:Button Text = "Update" runat="server" ID="btnUpdate" />
                <asp:Button Text = "Reset" runat="server" ID="btnReset" />
                 <asp:CompareValidator ID="dateCompareValidator" runat="server" ControlToValidate="dateEnd"
                    ControlToCompare="dateBegin" Operator="GreaterThan" Type="Date" ErrorMessage="The second date must be after the first one." />
            </td>
           
        </tr>
        </table>
     
    <telerik:RadChart runat="server" ID = "chartTransactions" AutoLayout="true" DataSourceID="datTransactions" DefaultType="Line" DataGroupColumn = "Store" Width="800" >
            <ChartTitle><TextBlock Text = "Transactions - Store"></TextBlock></ChartTitle>     
            <PlotArea>
                <XAxis>
                    <Appearance ValueFormat="ShortDate">
                        <LabelAppearance RotationAngle="270">
                        </LabelAppearance>
                    </Appearance>
                </XAxis>
            </PlotArea>
        </telerik:RadChart>
    <asp:SqlDataSource ID = "datTransactions" runat="server" ConnectionString="<%$ ConnectionStrings:DBString %>" SelectCommand = "Select tblStore_Ref.Description, CAST(DATEADD(Day, DATEDIFF(Day, 0, AppRejDateTime), 0) AS Float) + 2 AS AppRejDate, Count(*) as Transactions From tblTransactionHistory INNER JOIN tblTransactionItems ON tblTransactionHistory.TransactionID = tblTransactionItems.TransactionID INNER JOIN tblStore_Ref ON tblTransactionItems.Store = tblStore_Ref.Store Where Status = 'A' AND AppRejDateTime Between @dateBegin AND @dateEnd Group By DATEADD(Day, DATEDIFF(Day, 0, AppRejDateTime), 0), tblStore_Ref.Description Order By AppRejDate, tblStore_Ref.Description">
            <SelectParameters>
                <asp:ControlParameter ControlID = "dateBegin" Name = "dateBegin" PropertyName="SelectedDate" />
                <asp:ControlParameter ControlID = "dateEnd" Name = "dateEnd" PropertyName="SelectedDate" />
            </SelectParameters>
        </asp:SqlDataSource>

    The DataGroupColumn sets the grouping, which creates series based on the Store.

    The CodeBehind:
    Imports Telerik.Charting
    Partial Class Dash_Transactions
        Inherits System.Web.UI.Page
         'For every series, includes a pointmark that's the color of the series line.
      Protected Sub chartTransactions_BeforeLayout(ByVal sender As Object, ByVal e As System.EventArgs) Handles chartTransactions.BeforeLayout
            Dim series As ChartSeries
            For Each series In chartTransactions.Series
                series.Appearance.PointMark.Visible = True
                series.Appearance.ShowLabels = False
                series.Appearance.PointMark.FillStyle.MainColor = series.Appearance.LineSeriesAppearance.Color
            Next
        End Sub
              'Scales the X-Axis properly based on the chosen start date/end date
        Protected Sub chartTransactions_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles chartTransactions.DataBound
            Dim dblBegin As Double, dblEnd As Double
            dblBegin = DateTime.Parse(dateBegin.SelectedDate).Date.ToOADate
            dblEnd = DateTime.Parse(dateEnd.SelectedDate).Date.ToOADate
            chartTransactions.PlotArea.XAxis.AutoScale = False
            chartTransactions.PlotArea.XAxis.IsZeroBased = False
            chartTransactions.PlotArea.XAxis.AddRange(dblBegin, dblEnd, 4)
     
        End Sub
     
              'Sets the proper X-values.
        Protected Sub chartTransactions_DataBinding(ByVal sender As Object, ByVal e As System.EventArgs) Handles chartTransactions.DataBinding
            chartTransactions.DataManager.ValuesXColumn = "AppRejDate"
        End Sub
     
    End Class
  7. Michael
    Michael avatar
    7 posts
    Member since:
    Dec 2010

    Posted 19 Jan 2011 Link to this post

    Final Problem:

    I'm adding some DrillDown behavior to the chart.  I have PointMarks implemented on the Line Chart.

    If I click on the legend, the OnClick method is called and everything works.

    If I click on the pointmarks, it throws an exception without even going to the OnClick.  Here's the exception:

    Server Error in Application.
    --------------------------------------------------------------------------------
     
    Index was out of range. Must be non-negative and less than the size of the collection.
    Parameter name: index
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
     
    Exception Details: System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
    Parameter name: index
     
    Source Error:
     
    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 
     
    Stack Trace:
     
     
    [ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
    Parameter name: index]
       System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource) +64
       System.ThrowHelper.ThrowArgumentOutOfRangeException() +15
       System.Collections.Generic.List`1.get_Item(Int32 index) +7497276
       Telerik.Charting.ChartSeriesCollection.get_Item(Int32 index) +48
       Telerik.Web.UI.RadChart.RaisePostBackEvent(String eventArg) +452
       System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
       System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +175
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
     
      
     
     
    --------------------------------------------------------------------------------
    Version Information: Microsoft .NET Framework Version:2.0.50727.3082; ASP.NET Version:2.0.50727.3082

    Help!  Thanks.
  8. Evgenia
    Admin
    Evgenia avatar
    1437 posts

    Posted 25 Jan 2011 Link to this post

    Hi Michael,

    We are unable to define the state of the problem from this stack trace. Could you open a Support thread and send us the sample project attached where this issue occurs so that we will be able to investigate it and help you?

    Greetings,
    Evgenia
    the Telerik team
    Browse the vast support resources we have to jump start your development with RadControls for ASP.NET AJAX. See how to integrate our AJAX controls seamlessly in SharePoint 2007/2010 visiting our common SharePoint portal.
  9. Michael
    Michael avatar
    7 posts
    Member since:
    Dec 2010

    Posted 26 Jan 2011 Link to this post

    Sent in a support ticket and they fixed it!

    I was calling RemoveAllSeries() in the Page_Load section, when it should be in the Chart onClick event handler.

    Thanks a lot.
Back to Top