This is a migrated thread and some comments may be shown as answers.
Setting DataXColumn for every Databound Series
8 Answers 158 Views
This is a migrated thread and some comments may be shown as answers.
Michael
Top achievements
Rank 1
Michael asked on 14 Jan 2011, 04:04 AM
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.

8 Answers, 1 is accepted

Sort by
0
Michael
Top achievements
Rank 1
answered on 14 Jan 2011, 09:46 PM
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.
0
Michael
Top achievements
Rank 1
answered on 18 Jan 2011, 06:21 PM
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.
0
Yavor
Telerik team
answered on 19 Jan 2011, 02:39 PM
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.
0
Michael
Top achievements
Rank 1
answered on 19 Jan 2011, 07:37 PM
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.
0
Michael
Top achievements
Rank 1
answered on 19 Jan 2011, 10:17 PM
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
0
Michael
Top achievements
Rank 1
answered on 20 Jan 2011, 12:57 AM
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.
0
Evgenia
Telerik team
answered on 25 Jan 2011, 12:10 PM
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.
0
Michael
Top achievements
Rank 1
answered on 26 Jan 2011, 06:07 PM
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.
Asked by
Michael
Top achievements
Rank 1
Answers by
Michael
Top achievements
Rank 1
Yavor
Telerik team
Evgenia
Telerik team
Share this question
or