Chart Multiple Y series bind problem

[Nicolas]

Hi there,

I am struggling with this problem for a few days now and can not get it to work.
In my solution, I have added a RadChart with a SqlDataSource like this:

<div>

        <telerik:RadChart ID="RadChart1" runat="server" DataSourceID="SqlDataSource1"
            Height="400px" Skin="Vista" Width="800px">
        </telerik:RadChart>

        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:EUD_SD_TodoListConnectionString %>"
            SelectCommand="select s.Description [Status], DateName( month , DateAdd( month , Month(i.StartC) , 0 ) - 1 ) as [Month], count(*) as [Value] from Items i INNER JOIN Status s ON s.ID = i.StatusID where Year(i.StartC) = 2011 group by s.Description, Month(i.StartC) order by Month(i.StartC) desc">
        </asp:SqlDataSource>

    </div>

Code Behind to group the chart:

RadChart1.DataGroupColumn = "Status";
RadChart1.PlotArea.XAxis.DataLabelsColumn = "Month";
RadChart1.Legend.Appearance.GroupNameFormat = "#NAME:#VALUE";

I am trying to bind a crosstable to the RadChart control but I cannot manage to get it working, you can view the results of my query on the attached file in this post (Untitled.png).

The thing i am trying to do is to get the Year on the X-axis and the corresponsing status grouped by Year for the number of items existing (Value). AS you can see the Statuses are not mapped to the correct Year?

Can somebody help me with this matter?

If i configure it thrue the Chart Wizard it creates statis series items in the code, and the series have to be dynamic because the year/statuses can change in the dba...

Thanks in advance!
Nicolas

[Nicolas]

I think the problem is that for each series there is a different number of months.
I am grouping the items according to their Status, so for every Status the Chart is going to map the Month without checking theactual  values of the month.

The thing i need to do is for each series, in my case Status, have the same number of Month values, even though the series does not have accordingly yaxis values...

[Nicolas]

Managed to find a successfull solution.

The case was indeed that for each status there had to be an input value in the recordset:

So for each month I had to enumerate all the available statuses (helper class list), that gave me all the possible combinations for each month with their statuses. Then I had to check in this container for how many times that status corresponded and made a count from it. I also had to make sure that the collection where i was looking into was filtered by the appropriate month and year (year in declaration, month after foreach loop so each month can be grouped seperatly). Then I had three combinations inside of my HelperObject List, Month, Status and Number of Items. The List.Count gave me 28, all with Month and Status filled in and with their matching number of the times being present in the items container.

Code:

EUD_SD_TodoListEntities context = new EUD_SD_TodoListEntities();

       IQueryable<Items> items = context.Items.Where(uy => uy.StartC.Value.Year == 2011);
       IQueryable<Status> statuses = context.Status;

       IQueryable<int> monthsinitems = from ui in context.Items where ui.StartC.Value.Month != null orderby ui.StartC.Value.Month select ui.StartC.Value.Month;
       monthsinitems = monthsinitems.Distinct();

       List<HelperItem> helperitems = new List<HelperItem>();

       foreach (var month in monthsinitems)
       {
           foreach (var status in statuses)
           {

               //count the number of times this status matches with the items table
               var hhh = from ops in items where ops.StatusID == status.ID && ops.StartC.Value.Month == month select ops;
               var uis = hhh.Count();

               HelperItem h = new HelperItem();
               h.Status = status.Description;
               h.Month = month.ToString();
               h.NumberOfPackages = uis.ToString();

               helperitems.Add(h);

           }
       }

       RadChart1.DataSource = helperitems;
       RadChart1.DataBind();

Hope this helps for all you guys struggling with binding multiple series with different number of items.
Sorry for the variable naming :-)

Many regards,
Nicolas