This is a migrated thread and some comments may be shown as answers.

Grouping series on a chart

5 Answers 220 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
Amy
Top achievements
Rank 1
Amy asked on 18 Jun 2009, 01:36 PM
I have the following data coming from a SqlDataAdapter:

Line                PolicyEffectiveDate     PolicyYear  TotalIncurred         ClaimCount
------------------- ----------------------- --------------------------------- ------------
Auto                2002-07-01 00:00:00.000 2002        27510                 9
Auto                2003-07-01 00:00:00.000 2003        86348                 9
Auto                2004-07-01 00:00:00.000 2004        189816                11
Auto                2005-07-01 00:00:00.000 2005        71493                 15
Auto                2006-07-01 00:00:00.000 2006        84040                 13
Auto                2007-07-01 00:00:00.000 2007        32843                 7
Auto                2008-07-01 00:00:00.000 2008        48563                 11
GL                  2002-07-01 00:00:00.000 2002        48617                 5
GL                  2003-07-01 00:00:00.000 2003        305553                4
GL                  2005-07-01 00:00:00.000 2005        714                   3
GL                  2008-07-01 00:00:00.000 2008        23000                 2
Property            2003-07-01 00:00:00.000 2003        12782                 1
Property            2007-07-01 00:00:00.000 2007        8372                  1
WC                  2003-07-01 00:00:00.000 2003        384394                25
WC                  2004-07-01 00:00:00.000 2004        304270                28
WC                  2005-07-01 00:00:00.000 2005        52678                 14
WC                  2006-07-01 00:00:00.000 2006        4638                  2
WC                  2007-07-01 00:00:00.000 2007        50125                 7
WC                  2008-07-01 00:00:00.000 2008        64037                 5

I have the following code in my NeedDataSource:

        Me.SqlDataAdapter1.SelectCommand.Parameters("@PolicyNum").Value = Me.ReportParameters("@PolicyNum").Value
        Me.SqlDataAdapter1.SelectCommand.Parameters("@AcctNum").Value = Me.ReportParameters("@AcctNum").Value
        Me.SqlDataAdapter1.SelectCommand.Parameters("@SaleNum").Value = Me.ReportParameters("@SaleNum").Value
        Me.SqlDataAdapter1.SelectCommand.Parameters("@Lineid").Value = Me.ReportParameters("@Lineid").Value
        Me.SqlDataAdapter1.SelectCommand.Parameters("@ParentAgent").Value = Me.ReportParameters("@ParentAgent").Value
        Me.SqlDataAdapter1.SelectCommand.Parameters("@SubAgent").Value = Me.ReportParameters("@SubAgent").Value
        Me.SqlDataAdapter1.SelectCommand.Parameters("@StartEffDate").Value = Me.ReportParameters("@StartEffDate").Value
        Me.SqlDataAdapter1.SelectCommand.Parameters("@EndEffDate").Value = Me.ReportParameters("@EndEffDate").Value

        Dim dataSet As New DataSet()
        SqlDataAdapter1.Fill(dataSet)

        Dim ChartInc As Telerik.Reporting.Processing.Chart = CType(sender, Telerik.Reporting.Processing.Chart)
        Dim DefChart As Telerik.Reporting.Chart = DirectCast(ChartInc.ItemDefinition, Telerik.Reporting.Chart)
        ChartInc.DataSource = dataSet


I am trying to have 2 different charts: one showing TotalIncurred and one showing ClaimCount as the series.  I want to show them by Policy Year and by Line on the same chart, but group them.  I thought I could use the DataGroupColumn, so I tried 

setting PlotArea->XAxis->DataLabelsColumn to PolicyYear and DataGroupColumn to Line.  It isn't working.  Any ideas on how to do this?

 



5 Answers, 1 is accepted

Sort by
0
Steve
Telerik team
answered on 23 Jun 2009, 08:35 AM
Hi Amy,

An explanation of how grouping works for the chart is available in this help article. You can review it (if you haven't already) and explain what you need. We ask this because we could not understand your exact inquiry from your post i.e. if we follow your thoughts, setting DataGroupColumn to "Line" you would get 4 series - one for each unique values in this column. At the same time you state that you want two charts - should we understand two series instead?

Greetings,
Steve
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.
0
Amy
Top achievements
Rank 1
answered on 23 Jun 2009, 04:11 PM

Thanks.  That article was a tremendous help.  I hadn't come across it before because I am trying to only look at the charts under reports - I ran into too many differences when trying to solve another problem - but this one worked very well. 

One problem - my data might be too variable to use this.  I would attach some examples, but I can't seem to here.  Basically, the chart is not really grouping things correctly, because the data can be different for each line.  For example, we might have consistent policy periods for one line (2000, 2001, 2002, 2003, 2004, etc.) but some skip years (2001, 2003, 2004).  When that happens, the data shows up in the wrong spots. 

Another question - I added a brand new chart and other than setting the data source, i just used your example.  I didn't set any series or anything.  It automatically took the Claims column to graph.  I would also like to show another separate graph with the Incurred column.  What is the best way to go about that?  

Thanks.

0
Steve
Telerik team
answered on 26 Jun 2009, 01:43 PM
Hello Amy,

First I would like to note that I am not sure what you mean by "data shows up in the wrong spots". Which columns have you set as DataGroupColumn and DataLabelsColumn?
As for your second inquiry - unfortunately this is not possible. When grouped, the chart would create the series automatically and it uses one numeric column, which is divided into several series according to the values in the DataGroupColumn.
We believe that this is limitation in our chart, but its development is currently frozen, so this would most probably not be implemented.

All the best,
Steve
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.
0
Amy
Top achievements
Rank 1
answered on 23 Jul 2009, 07:56 PM
I am trying a different approach to this problem.  Below is my needdatasource:

   Private Sub ChartIncAcct_NeedDataSource(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChartIncAcct.NeedDataSource
        ChartInc.Visible = False
        Dim ChartIncAcct As Telerik.Reporting.Processing.Chart = CType(sender, Telerik.Reporting.Processing.Chart)
        Dim defChart As Telerik.Reporting.Chart = DirectCast(ChartIncAcct.ItemDefinition, Telerik.Reporting.Chart)
        defChart.DataGroupColumn = "PolicyYear"

        Dim dataSet As New DataSet()
        SqlDataAdapter1.Fill(dataSet)

        Dim table As New DataTable()
        table.Columns.Add("Line", GetType(String))
        table.Columns.Add("Lineid", GetType(String))
        table.Columns.Add("PolicyYear", GetType(String))
        table.Columns.Add("TotalIncurred", GetType(Integer))
        SqlDataAdapter1.Fill(table)

        defChart.Series.Clear()

        defChart.PlotArea.XAxis.AutoScale = False
        defChart.PlotArea.XAxis.Appearance.ValueFormat = Styles.ChartValueFormat.General
        defChart.PlotArea.XAxis.IsZeroBased = False
        defChart.PlotArea.XAxis.DataLabelsColumn = "Line"

        Dim Series2006 As New ChartSeries()
        Dim Series2007 As New ChartSeries()
        Dim Series2008 As New ChartSeries()
        Dim Series2009 As New ChartSeries()

        For Each row As DataRow In table.Rows
            Dim val As Double = CType(row("TotalIncurred"), Double)
            'Dim cat As String = CType(row("Line"), String).Trim
            Dim cat As String = CType(row("Lineid"), String).Trim
            Dim year As String = CType(row("PolicyYear"), String).Trim
            Dim item As New ChartSeriesItem()
            item.XValue = cat
            item.YValue = val
            item.Label.TextBlock.Text = year & "-" & val

            Select Case CType(row("PolicyYear"), String).Trim
                Case "2006"
                    Series2006.Items.Add(item)
                Case "2007"
                    Series2007.Items.Add(item)
                Case "2008"
                    Series2008.Items.Add(item)
                Case "2009"
                    Series2009.Items.Add(item)
            End Select

        Next
        Series2006.Name = "2006"
        Series2007.Name = "2007"
        Series2008.Name = "2008"
        Series2009.Name = "2009"

        defChart.Series.Add(Series2006)
        defChart.Series.Add(Series2007)
        defChart.Series.Add(Series2008)
        defChart.Series.Add(Series2009)

    End Sub

It comes out close, but it doesn't have the labels correct and it is showing too many lines.  I used the same data as above, but limited it to policy year 2006 and forward.  The labels are numeric because they gave me an error when I tried to use character data.  The lineid column are the codes, and the line column are the words.  So, for example, 1 (lineid) is Auto (line), 2 is GL, 3 is Property and 5 is WC.  Those are our actual codes for those lines.  But the graph is treating it as numeric, so puts in the numbers for the labels and includes 0, 4, 6 and 7, which don't have values.  I can't paste a screen print of what the graph looks like, but if it close.  If I could change the labels to the words (line) and eliminate the lineids that don't have values, I think it would work.  Can I do that?

0
Giuseppe
Telerik team
answered on 24 Jul 2009, 01:23 PM
Hello Amy,

Here is a modified code snippet that based on your guidelines achieves the desired effect:

    Private Sub Chart1_NeedDataSource(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles Chart1.NeedDataSource 
        'chart1.DataGroupColumn = "PolicyYear"; 
        Chart1.AutoLayout = True 
        Chart1.PlotArea.XAxis.AutoScale = False 
        Chart1.PlotArea.XAxis.AddRange(1, 4, 1) 
        Chart1.PlotArea.YAxis.Appearance.MinorGridLines.Visible = False 
        'chart1.PlotArea.XAxis.Appearance.ValueFormat = ChartValueFormat.General; 
        'chart1.PlotArea.XAxis.IsZeroBased = false; 
        'chart1.PlotArea.XAxis.DataLabelsColumn = "Line"; 
 
        Dim Series2006 As New ChartSeries() 
        Dim Series2007 As New ChartSeries() 
        Dim Series2008 As New ChartSeries() 
        Dim Series2009 As New ChartSeries() 
 
        For Each row As DataRow In GetData().Rows 
            Dim val As Double = DirectCast(row("TotalIncurred"), Double
 
            Dim cat As Integer = (DirectCast(row("Lineid"), Integer)) 
            Dim year As String = (DirectCast(row("PolicyYear"), String)).Trim() 
            Dim item As New ChartSeriesItem() 
            'item.XValue = cat; 
            item.YValue = val 
            item.Label.TextBlock.Text = year + "-" + val 
 
            Select Case (DirectCast(row("PolicyYear"), String)).Trim() 
                Case "2006" 
                    Series2006.Items.Add(item) 
                    Exit Select 
                Case "2007" 
                    Series2007.Items.Add(item) 
                    Exit Select 
                Case "2008" 
                    Series2008.Items.Add(item) 
                    Exit Select 
                Case "2009" 
                    Series2009.Items.Add(item) 
                    Exit Select 
            End Select 
        Next 
        Series2006.Name = "2006" 
        Series2007.Name = "2007" 
        Series2008.Name = "2008" 
        Series2009.Name = "2009" 
 
        Chart1.Series.Add(Series2006) 
        Chart1.Series.Add(Series2007) 
        Chart1.Series.Add(Series2008) 
        Chart1.Series.Add(Series2009) 
 
        Dim years As String() = New String() {"2006""2007""2008""2009"
 
        Dim i As Integer = 0 
        While i < Chart1.PlotArea.XAxis.Items.Count 
            Chart1.PlotArea.XAxis.Items(i).TextBlock.Text = years(i) 
            System.Math.Max(System.Threading.Interlocked.Increment(i), i - 1) 
        End While 
    End Sub 
 
    Private Function GetData() As DataTable 
        Dim table As New DataTable() 
        table.Columns.Add("Line"GetType(String)) 
        table.Columns.Add("Lineid"GetType(Integer)) 
        table.Columns.Add("PolicyYear"GetType(String)) 
        table.Columns.Add("TotalIncurred"GetType(Double)) 
 
        Dim dr As DataRow = table.NewRow() 
        dr(0) = "Auto" 
        dr(1) = 1 
        dr(2) = "2006" 
        dr(3) = 84040 
        table.Rows.Add(dr) 
 
        dr = table.NewRow() 
        dr(0) = "Auto" 
        dr(1) = 1 
        dr(2) = "2007" 
        dr(3) = 32843 
        table.Rows.Add(dr) 
 
        dr = table.NewRow() 
        dr(0) = "Auto" 
        dr(1) = 1 
        dr(2) = "2008" 
        dr(3) = 48563 
        table.Rows.Add(dr) 
 
        dr = table.NewRow() 
        dr(0) = "GL" 
        dr(1) = 2 
        dr(2) = "2008" 
        dr(3) = 23000 
        table.Rows.Add(dr) 
 
        dr = table.NewRow() 
        dr(0) = "Property" 
        dr(1) = 3 
        dr(2) = "2007" 
        dr(3) = 8372 
        table.Rows.Add(dr) 
 
        dr = table.NewRow() 
        dr(0) = "WC" 
        dr(1) = 5 
        dr(2) = "2006" 
        dr(3) = 4638 
        table.Rows.Add(dr) 
 
        dr = table.NewRow() 
        dr(0) = "WC" 
        dr(1) = 5 
        dr(2) = "2007" 
        dr(3) = 50125 
        table.Rows.Add(dr) 
 
        dr = table.NewRow() 
        dr(0) = "WC" 
        dr(1) = 5 
        dr(2) = "2008" 
        dr(3) = 64037 
        table.Rows.Add(dr) 
 
        Return table 
    End Function 

Note we have commented out the DataGroupColumn and DataLabelsColumn setters as well as the setting of XValue for the series items (this causes the chart to enter "strict" mode and causes the "gaps" where you do not have data). We are populating the axis item labels manually with the year values (2006, 2007, etc.) as it seems you are grouping the data based on this principle. As for the lines -- we assume you are talking about the YAxis minor gridlines and we have set their visibility to false as well.


Kind regards,
Manuel
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.
Tags
General Discussions
Asked by
Amy
Top achievements
Rank 1
Answers by
Steve
Telerik team
Amy
Top achievements
Rank 1
Giuseppe
Telerik team
Share this question
or