locked
Dynamically created series using StackedColumn - Overlap / gaps between series. RRS feed

  • Question

  • User705223197 posted

    I've read various threads around this issue which is caused when there are no data-points between series but I can't quite work out how to plug it in when dynamically creating the series for a StackedColumn chart. I'm almost there ...

    Image showing overlap / gaps.

    Code behind:

      Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
            If Not Me.IsPostBack Then
                'Fetch the data from database.
                Dim query As String = "SELECT DISTINCT TickC, CODE, Week FROM someview order by code desc, week"
                Dim dt As DataTable = GetData(query)
    
                'Get the DISTINCT subcodes.
                Dim codes As List(Of String) = (From p In dt.AsEnumerable()
                                                Select p.Field(Of String)("CODE")).Distinct().ToList()
    
                'Loop through the subcodes.
                For Each subcode As String In codes
                    ' If Not subcode Is DBNull.Value Then
                    'Get the weekno for each subcode.
                    Dim x As Integer() = (From p In dt.AsEnumerable()
                                          Where p.Field(Of String)("CODE") = subcode
                                          Order By p.Field(Of Integer)("Week")
                                          Select p.Field(Of Integer)("Week")).ToArray()
    
                    'Get the Total of tickets for each subcode.
                    Dim y As Integer() = (From p In dt.AsEnumerable()
                                          Where p.Field(Of String)("CODE") = subcode
                                          Order By p.Field(Of Integer)("Week")
                                          Select p.Field(Of Integer)("TickC")).ToArray()
    
                    'Add Series to the Chart.
                    Chart1.Series.Add(New Series(subcode))
                    Chart1.Series(subcode).IsValueShownAsLabel = False
                    Chart1.Series(subcode).BorderWidth = 0
                    Chart1.Series(subcode).ChartType = SeriesChartType.StackedColumn
                    Chart1.Series(subcode).Points.DataBindXY(x, y)
                    Chart1.Series(subcode).ToolTip = "#SERIESNAME(#VALY)"
                    'Chart1.AlignDataPointsByAxisLabel()
                    'Chart1.DataManipulator.Sort(PointSortOrder.Ascending, "X", subcode);
                    'Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, subcode);
                    Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, 1, IntervalType.Number, subcode)
                    ' End If
                Next
                'Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, 0, IntervalType.Number, Series As codes)
                Chart1.Legends(0).Enabled = True
            End If

    As you can see I've clumsily tried various things with DataManupulator but with zero luck. I'm struggling how I plug this in, if indeed this is the right method, in to the code.

    Any pointers, greatly appreciated.

    TIA, Paul.

    Friday, November 29, 2019 7:04 AM

Answers

  • User-719153870 posted

    Hi darkgen,

    darkgen

    So nothing to do with maximums; the error occurs with the following dataset. 

    Yes and sorry you met the problem because i took a shortcut in the code i provided.

    In the early sample datatable you provided, the Week value was 1,2 and 3 thus i took it as the counter which you can see it as xi in the code.

    For Each xi As Integer In x
                        If Not x1.Contains(xi) Then
                            Array.Resize(y, y.Length + 1)
                            For i = y.Length - 1 To xi Step -1
                                y(i) = y(i - 1)
                            Next
                            y(xi - 1) = 0
                        End If
                    Next

    In the newest datatable, the week could be 45,46 and so on, so the counter xi would break.

    To solve this problem, all you need to do is to add a fresh counter in the code bove. Please refer to below demo:

    aspx:

    <%@ Register Assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Namespace="System.Web.UI.DataVisualization.Charting" TagPrefix="asp" %>
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:Chart ID="Chart1" runat="server" Height="594px" Width="1000px" BackColor="Silver" BorderlineColor="LightGray">
                    <Titles>
                        <asp:Title Name="Items" Text="Created IM by subcode" />
                    </Titles>
                    <Legends>
                        <asp:Legend Alignment="Center" Docking="Right" IsTextAutoFit="True" Name="Default"
                            LegendStyle="column" />
                    </Legends>
                    <ChartAreas>
                        <asp:ChartArea Name="ChartArea1" BorderWidth="1" />
                    </ChartAreas>
                </asp:Chart>
            </div>
        </form>
    </body>
    </html>
    

    cs:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            If Not Me.IsPostBack Then
                'Fetch the data from database.
                'Dim query As String = "SELECT distinct(ISNULL(TickC,0)) as TickC, CODE, Week FROM someview order by code desc, week"
                Dim dt As DataTable = New DataTable()
                dt.Columns.AddRange(New DataColumn(2) {New DataColumn("CODE"), New DataColumn("Week", Type.GetType("System.Int32")), New DataColumn("TickC", Type.GetType("System.Int32"))})
                dt.Rows.Add("ACCESS ISSUE", 44, 2)
                dt.Rows.Add("APPLICATION ISSUE", 44, 6)
                dt.Rows.Add("PERFORMANCE", 44, 2)
                dt.Rows.Add("T4-PERFORMANCE", 44, 1)
    
                dt.Rows.Add("ACCESS ISSUE", 45, 9)
                dt.Rows.Add("APPLICATION ERROR", 45, 1)
                dt.Rows.Add("APPLICATION ISSUE", 45, 27)
                dt.Rows.Add("CONNECTIVITY", 45, 8)
                dt.Rows.Add("PERFORMANCE", 45, 9)
                dt.Rows.Add("T4 - ACCESS ISSUE", 45, 5)
                dt.Rows.Add("T4 - CONNECTIVITY", 45, 4)
                dt.Rows.Add("T4 - PERFORMANCE", 45, 1)
    
                dt.Rows.Add("ACCESS ISSUE", 46, 13)
                dt.Rows.Add("APPLICATION ISSUE", 46, 23)
                dt.Rows.Add("CONFIGURATION", 46, 1)
                dt.Rows.Add("CONNECTIVITY", 46, 1)
                dt.Rows.Add("CONSULTANCY", 46, 1)
                dt.Rows.Add("PERFORMANCE", 46, 6)
                dt.Rows.Add("T4 - ACCESS ISSUE", 46, 6)
                dt.Rows.Add("T4 - CONFIGURATION", 46, 2)
                dt.Rows.Add("T4 - CONNECTIVITY", 46, 4)
                dt.Rows.Add("T4 - PERFORMANCE", 46, 2)
    
                dt.Rows.Add("ACCESS ISSUE", 47, 12)
                dt.Rows.Add("APPLICATION ISSUE", 47, 25)
                dt.Rows.Add("CONFIGURATION", 47, 5)
                dt.Rows.Add("CONNECTIVITY", 47, 4)
                dt.Rows.Add("CONSULTANCY", 47, 5)
                dt.Rows.Add("PERFORMANCE", 47, 6)
                dt.Rows.Add("T4 - ACCESS ISSUE", 47, 2)
                dt.Rows.Add("T4 - CONFIGURATION", 47, 3)
                dt.Rows.Add("T4 - CONNECTIVITY", 47, 5)
    
                dt.Rows.Add("ACCESS ISSUE", 48, 15)
                dt.Rows.Add("APPLICATION ISSUE", 48, 20)
                dt.Rows.Add("LICENSE", 48, 1)
                dt.Rows.Add("PERFORMANCE", 48, 3)
                dt.Rows.Add("PROFILE", 48, 1)
                dt.Rows.Add("T4 - ACCESS ISSUE", 48, 1)
                dt.Rows.Add("T4 - CONFIGURATION", 48, 1)
                dt.Rows.Add("T4 - CONNECTIVITY", 48, 2)
                dt.Rows.Add("T4 - PERFORMANCE", 48, 1)
    
                dt.Rows.Add("ACCESS ISSUE", 49, 1)
                dt.Rows.Add("APPLICATION ISSUE", 49, 7)
                dt.Rows.Add("PERFORMANCE", 49, 1)
                dt.Rows.Add("T4 - ACCESS ISSUE", 49, 1)
                dt.Rows.Add("T4 - CONFIGURATION", 49, 1)
                dt.Rows.Add("T4 - PERFORMANCE", 49, 1)
    
                'Get the DISTINCT subcodes.
                Dim codes As List(Of String) = (From p In dt.AsEnumerable()
                                                Select p.Field(Of String)("CODE")).Distinct().ToList()
    
                'Loop through the subcodes.
                For Each subcode As String In codes
    
                    'Get the weekno for each subcode.
                    Dim x1 As Integer() = (From p In dt.AsEnumerable()
                                           Where p.Field(Of String)("CODE") = subcode
                                           Order By p.Field(Of Integer)("Week")
                                           Select p.Field(Of Integer)("Week")).ToArray()
    
                    Dim x As Integer() = (From p In dt.AsEnumerable() Select p.Field(Of Integer)("Week") Distinct).ToArray()
    
                    Dim y As Integer() = (From p In dt.AsEnumerable()
                                          Where p.Field(Of String)("CODE") = subcode
                                          Order By p.Field(Of Integer)("Week")
                                          Select p.Field(Of Integer)("TickC")).ToArray()
    
                    Dim a As Integer = 1
                    For Each xi As Integer In x
                        If Not x1.Contains(xi) Then
                            Array.Resize(y, y.Length + 1)
                            For i = y.Length - 1 To a Step -1
                                y(i) = y(i - 1)
                            Next
                            y(a - 1) = 0
                            a += 1
                        Else
                            a += 1
                        End If
                    Next
                    Chart1.Series.Add(New Series(subcode))
                    Chart1.Series(subcode).IsValueShownAsLabel = False
                    Chart1.Series(subcode).BorderWidth = 2
                    Chart1.Series(subcode).ChartType = SeriesChartType.StackedColumn
    
                    Chart1.Series(subcode).Points.DataBindXY(x, y)
    
                    Chart1.Series(subcode).ToolTip = "#SERIESNAME(#VALY)"
                Next
                Chart1.Legends(0).Enabled = True
            End If
        End Sub
    
        'Private Shared Function GetData(ByVal query As String) As DataTable
        '    Dim constr As String = ConfigurationManager.ConnectionStrings("Connection").ConnectionString
        '    Using con As SqlConnection = New SqlConnection(constr)
        '        Using sda As SqlDataAdapter = New SqlDataAdapter(query, con)
        '            Dim dt As DataTable = New DataTable()
        '            sda.Fill(dt)
        '            Return dt
        '        End Using
        '    End Using
        'End Function

    Below is the result of above demo:

    Hope this could help.

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 4, 2019 5:34 AM

All replies

  • User-719153870 posted

    Hi darkgen,

    I built a demo and reproduced your problem then got a chart like below:

    As you said, this problem 'is caused when there are no data-points between series'. When there's a field value was there in other columns but not in the current one, the chart will keep a white space for this field value.

    To solve this problem, i suggest you should update your query string to make sure there's a 0 value for those fileds who has null as its value in your database.

    Since we don't have the complete code of your program, i recommend you modify your query string like below:

    Dim query As String = "SELECT distinct(ISNULL(TickC,0)) as TickC, CODE, Week FROM someview order by code desc, week"

    You can refer to below demo for more information:

    aspx:

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:Chart ID="Chart1" runat="server">
                    <Series>
                        <asp:Series Name="Series1"></asp:Series>
                    </Series>
                    <ChartAreas>
                        <asp:ChartArea Name="ChartArea1"></asp:ChartArea>
                    </ChartAreas>
                </asp:Chart>
            </div>
        </form>
    </body>
    </html>

    vb:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            If Not Me.IsPostBack Then
                'Fetch the data from database.
                'Dim query As String = "SELECT DISTINCT TickC, CODE, Week FROM someview order by code desc, week"
                Dim dt As DataTable = New DataTable()
                dt.Columns.AddRange(New DataColumn(2) {New DataColumn("Name"), New DataColumn("ID", Type.GetType("System.Int32")), New DataColumn("Qty", Type.GetType("System.Int32"))})
                dt.Rows.Add("AA", 1, 3)
                dt.Rows.Add("BB", 1, 0)
                dt.Rows.Add("CC", 1, 1)
                dt.Rows.Add("DD", 1, 0)
                dt.Rows.Add("EE", 1, 24)
                dt.Rows.Add("FF", 1, 6)
                dt.Rows.Add("GG", 1, 44)
    
                dt.Rows.Add("AA", 2, 4)
                dt.Rows.Add("BB", 2, 43)
                dt.Rows.Add("CC", 2, 0)
                dt.Rows.Add("DD", 2, 54)
                dt.Rows.Add("EE", 2, 0)
                dt.Rows.Add("FF", 2, 14)
                dt.Rows.Add("GG", 2, 22)
    
                dt.Rows.Add("AA", 3, 32)
                dt.Rows.Add("BB", 3, 5)
                dt.Rows.Add("CC", 3, 9)
                dt.Rows.Add("DD", 3, 0)
                dt.Rows.Add("EE", 3, 34)
                dt.Rows.Add("FF", 3, 0)
                dt.Rows.Add("GG", 3, 22)
    
                'Get the DISTINCT subcodes.
                Dim codes As List(Of String) = (From p In dt.AsEnumerable()
                                                Select p.Field(Of String)("Name")).Distinct().ToList()
    
                'Loop through the subcodes.
                For Each subcode As String In codes
                    ' If Not subcode Is DBNull.Value Then
                    'Get the weekno for each subcode.
                    Dim x As Integer() = (From p In dt.AsEnumerable()
                                          Where p.Field(Of String)("Name") = subcode
                                          Order By p.Field(Of Integer)("ID")
                                          Select p.Field(Of Integer)("ID")).ToArray()
    
                    'Get the Total of tickets for each subcode.
                    Dim y As Integer() = (From p In dt.AsEnumerable()
                                          Where p.Field(Of String)("Name") = subcode
                                          Order By p.Field(Of Integer)("ID")
                                          Select p.Field(Of Integer)("Qty")).ToArray()
    
                    'Add Series to the Chart.
                    Chart1.Series.Add(New Series(subcode))
                    Chart1.Series(subcode).IsValueShownAsLabel = False
                    Chart1.Series(subcode).BorderWidth = 0
                    Chart1.Series(subcode).ChartType = SeriesChartType.StackedColumn
                    Chart1.Series(subcode).Points.DataBindXY(x, y)
                    Chart1.Series(subcode).ToolTip = "#SERIESNAME(#VALY)"
                    'Chart1.AlignDataPointsByAxisLabel()
                    'Chart1.DataManipulator.Sort(PointSortOrder.Ascending, "X", subcode);
                    'Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, subcode);
                    Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, 1, IntervalType.Number, subcode)
                    ' End If
                Next
                'Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, 0, IntervalType.Number, Series As codes)
                'Chart1.Legends(0).Enabled = True
            End If
        End Sub

    Below is the result of this demo:

    Best Regard,

    Yang Shen

    Monday, December 2, 2019 6:05 AM
  • User705223197 posted

    Many thanks for your reply but adding a isnull to the query doesn't seem to make a difference.

    For completeness:

    Here is the chart section:

     <asp:Chart ID="Chart1" runat="server" Height="400px" Width="1000px" BackColor="Silver" BorderlineColor="LightGray">
        <Titles>
            <asp:Title Name="Items" Text="Created IM by subcode" />
        </Titles>
        <Legends>
            <asp:Legend Alignment="Center" Docking="Right" IsTextAutoFit="True" Name="Default"
                LegendStyle="column" />
        </Legends>
        <ChartAreas>
            <asp:ChartArea Name="ChartArea1" BorderWidth="1" />
        </ChartAreas>
    </asp:Chart>

    Here is the full code behind:

    
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
            If Not Me.IsPostBack Then
                'Fetch the data from database.
                Dim query As String = "SELECT distinct(ISNULL(TickC,0)) as TickC, CODE, Week FROM someview order by code desc, week"
                Dim dt As DataTable = GetData(query)
    
                'Get the DISTINCT subcodes.
                Dim codes As List(Of String) = (From p In dt.AsEnumerable()
                                                Select p.Field(Of String)("CODE")).Distinct().ToList()
    
                'Loop through the subcodes.
                For Each subcode As String In codes
    
                    'Get the weekno for each subcode.
                    Dim x As Integer() = (From p In dt.AsEnumerable()
                                              Where p.Field(Of String)("CODE") = subcode
                                              Order By p.Field(Of Integer)("Week")
                                              Select p.Field(Of Integer)("Week")).ToArray()
    
                        'Get the Total of tickes for each subcode.
                        Dim y As Integer() = (From p In dt.AsEnumerable()
                                              Where p.Field(Of String)("CODE") = subcode
                                              Order By p.Field(Of Integer)("Week")
                                              Select p.Field(Of Integer)("TickC")).ToArray()
    
                        'Add Series to the Chart.
                        Chart1.Series.Add(New Series(subcode))
                        Chart1.Series(subcode).IsValueShownAsLabel = False
                    Chart1.Series(subcode).BorderWidth = 2
                    Chart1.Series(subcode).ChartType = SeriesChartType.StackedColumn
                        Chart1.Series(subcode).Points.DataBindXY(x, y)
                        Chart1.Series(subcode).ToolTip = "#SERIESNAME(#VALY)"
                    'Chart1.AlignDataPointsByAxisLabel()
    
                    'Chart1.AlignDataPointsByAxisLabel()
                    'Chart1.DataManipulator.Sort(PointSortOrder.Ascending, "X", subcode)
                    'Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, subcode)
                    'Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, subcode)
                    Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, 1, IntervalType.Number, subcode)
    
                Next
                'Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, 0, IntervalType.Number, Series As codes)
    
                Chart1.Legends(0).Enabled = True
    
    
                'For Each s As Series In Chart1.Series
                'Chart1.DataManipulator.InsertEmptyPoints(1, IntervalType.Number, 0, IntervalType.Number, s)
                'Next
    
    
            End If
        End Sub
    
        Private Shared Function GetData(ByVal query As String) As DataTable
            Dim constr As String = ConfigurationManager.ConnectionStrings("Connection").ConnectionString
            Using con As SqlConnection = New SqlConnection(constr)
                Using sda As SqlDataAdapter = New SqlDataAdapter(query, con)
                    Dim dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    Return dt
                End Using
            End Using
        End Function

    This is an example of the query data:

    Count   Code            Week
    1 ACCESS ISSUE 1 6 APPLICATION ISSUE 1 3 ACCESS ISSUE 2 6 APPLICATION ISSUE 2 1 CONFIGURATION 2 1 T123 - CONNECTIVITY 2 1 T123 - PERFORMANCE 2 2 ACCESS ISSUE 3 6 APPLICATION ISSUE 3 1 CONFIGURATION 3 1 CONNECTIVITY 3 1 PERFORMANCE 3 1 T123 - ACCESS ISSUE 3 2 T123 - CONNECTIVITY 3

    As you can see; it's not just a simple case of isnulling to 0 codes that have zero. In the sql table; there isn't an entry at all for codes with no ticket counts.

    Monday, December 2, 2019 8:31 AM
  • User-719153870 posted

    Hi darkgen,

    darkgen

    it's not just a simple case of isnulling to 0 codes that have zero. In the sql table; there isn't an entry at all for codes with no ticket counts.

    Yes you are right and actually i'm right too, let me explain.

    First of all, the reason that caused the weird performance of your chart. It is not entirely caused by your code but also the asp.net Chart control. Press F12 and you will see all those data are provided but some of them just disappear in the chart, like below:

    This is wierd and sorry that i'm not sure the reason why. 

    However, the most important thing is how you can fix it. As i mentioned before, the solution is to set the null value to 0 and if you don't even have the CODE for specific Week, create it dynamically and set it to 0. You can refert to below code (it's not database related but with the sample datatable you provided so i assume you should get the point):

    aspx:

    <%@ Register Assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Namespace="System.Web.UI.DataVisualization.Charting" TagPrefix="asp" %>
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:Chart ID="Chart1" runat="server" Height="594px" Width="1000px" BackColor="Silver" BorderlineColor="LightGray">
                    <Titles>
                        <asp:Title Name="Items" Text="Created IM by subcode" />
                    </Titles>
                    <Legends>
                        <asp:Legend Alignment="Center" Docking="Right" IsTextAutoFit="True" Name="Default"
                            LegendStyle="column" />
                    </Legends>
                    <ChartAreas>
                        <asp:ChartArea Name="ChartArea1" BorderWidth="1" />
                    </ChartAreas>
                </asp:Chart>
            </div>
        </form>
    </body>
    </html>

    vb:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            If Not Me.IsPostBack Then
                'Fetch the data from database.
                'Dim query As String = "SELECT distinct(ISNULL(TickC,0)) as TickC, CODE, Week FROM someview order by code desc, week"
                Dim dt As DataTable = New DataTable()
                dt.Columns.AddRange(New DataColumn(2) {New DataColumn("CODE"), New DataColumn("Week", Type.GetType("System.Int32")), New DataColumn("TickC", Type.GetType("System.Int32"))})
                dt.Rows.Add("ACCESS ISSUE", 1, 1)
                dt.Rows.Add("APPLICATION ISSUE", 1, 6)
                '----------------------
                'dt.Rows.Add("CONFIGURATION", 1, 0)
                'dt.Rows.Add("CONNECTIVITY", 1, 0)
                'dt.Rows.Add("PERFORMANCE", 1, 0)
                'dt.Rows.Add("T123 - ACCESS ISSUE", 1, 0)
                'dt.Rows.Add("T123 - CONNECTIVITY", 1, 0)
                'dt.Rows.Add("T123 - PERFORMANCE", 1, 0)
    
                dt.Rows.Add("ACCESS ISSUE", 2, 3)
                dt.Rows.Add("APPLICATION ISSUE", 2, 6)
                dt.Rows.Add("CONFIGURATION", 2, 1) '--
                dt.Rows.Add("T123 - CONNECTIVITY", 2, 1) '--
                dt.Rows.Add("T123 - PERFORMANCE", 2, 1)
                '----------------------
                'dt.Rows.Add("CONNECTIVITY", 2, 0)
                'dt.Rows.Add("PERFORMANCE", 2, 0)
                'dt.Rows.Add("T123 - ACCESS ISSUE", 2, 0)
    
                dt.Rows.Add("ACCESS ISSUE", 3, 2)
                dt.Rows.Add("APPLICATION ISSUE", 3, 6)
                dt.Rows.Add("CONFIGURATION", 3, 1)
                dt.Rows.Add("CONNECTIVITY", 3, 1) '--
                dt.Rows.Add("PERFORMANCE", 3, 1) '--
                dt.Rows.Add("T123 - ACCESS ISSUE", 3, 1)
                dt.Rows.Add("T123 - CONNECTIVITY", 3, 2)
                '----------------------
                'dt.Rows.Add("T123 - PERFORMANCE", 3, 0)
    
                'Get the DISTINCT subcodes.
                Dim codes As List(Of String) = (From p In dt.AsEnumerable()
                                                Select p.Field(Of String)("CODE")).Distinct().ToList()
    
                'Loop through the subcodes.
                For Each subcode As String In codes
    
                    'Get the weekno for each subcode.
                    Dim x1 As Integer() = (From p In dt.AsEnumerable()
                                           Where p.Field(Of String)("CODE") = subcode
                                           Order By p.Field(Of Integer)("Week")
                                           Select p.Field(Of Integer)("Week")).ToArray()
    
                    Dim x As Integer() = (From p In dt.AsEnumerable() Select p.Field(Of Integer)("Week") Distinct).ToArray()
    
                    Dim y As Integer() = (From p In dt.AsEnumerable()
                                          Where p.Field(Of String)("CODE") = subcode
                                          Order By p.Field(Of Integer)("Week")
                                          Select p.Field(Of Integer)("TickC")).ToArray()
                    For Each xi As Integer In x
                        If Not x1.Contains(xi) Then
                            Array.Resize(y, y.Length + 1)
                            For i = y.Length - 1 To xi Step -1
                                y(i) = y(i - 1)
                            Next
                            y(xi - 1) = 0
                        End If
                    Next
    
                    Chart1.Series.Add(New Series(subcode))
                    Chart1.Series(subcode).IsValueShownAsLabel = False
                    Chart1.Series(subcode).BorderWidth = 2
                    Chart1.Series(subcode).ChartType = SeriesChartType.StackedColumn
    
                    Chart1.Series(subcode).Points.DataBindXY(x, y)
    
                    Chart1.Series(subcode).ToolTip = "#SERIESNAME(#VALY)"
                Next
                Chart1.Legends(0).Enabled = True
            End If
        End Sub
    
        'Private Shared Function GetData(ByVal query As String) As DataTable
        '    Dim constr As String = ConfigurationManager.ConnectionStrings("Connection").ConnectionString
        '    Using con As SqlConnection = New SqlConnection(constr)
        '        Using sda As SqlDataAdapter = New SqlDataAdapter(query, con)
        '            Dim dt As DataTable = New DataTable()
        '            sda.Fill(dt)
        '            Return dt
        '        End Using
        '    End Using
        'End Function

    And below is the result:

    Hope this could help.

    Best Regard,

    Yang Shen

    Tuesday, December 3, 2019 8:28 AM
  • User705223197 posted

    I've think I've got it with that additional code enumeration. Sample code works as expected so I'm almost there.

    Dim x As Integer() = (From p In dt.AsEnumerable() Select p.Field(Of Integer)("Week") Distinct).ToArray()
    
    For Each xi As Integer In x
                        If Not x1.Contains(xi) Then
                            Array.Resize(y, y.Length + 1)
                            For i = y.Length - 1 To xi Step -1
                                y(i) = y(i - 1)
                            Next
                            y(xi - 1) = 0
                        End If
                    Next
    [IndexOutOfRangeException: Index was outside the bounds of the array.]
       ctxapp2.IM_Group_RES.Page_Load(Object sender, EventArgs e) in path2code.aspx.vb:12
       System.Web.UI.Control.OnLoad(EventArgs e) +95
       System.Web.UI.Control.LoadRecursive() +59
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +678

    Unfortunately when I pull data from the sql query it throws the above error! I'll continue to play and hopefully get there! Really appreciate your support and patience with this.

    e; interestingly, if i group the data by month; it renders without an error.

    e2: originally I had grouped the data by party before then subgrouping between week or month. Using that grouped data, it returns 150 records from the sql table which renders without issue. If I remove the grouping by party but keep it grouped by month, that returns 255 records and the "Index was outside the bounds of the array" occurs. Are there maximums at play here when trying to do this dynamically?

    Tuesday, December 3, 2019 9:04 AM
  • User705223197 posted

    Many thanks; I finally understand and I'm almost there.

    'Get the weekno for each subcode.
                    Dim x1 As Integer() = (From p In dt.AsEnumerable()
                                           Where p.Field(Of String)("CODE") = subcode
                                           Order By p.Field(Of Integer)("Week")
                                           Select p.Field(Of Integer)("Week")).ToArray()
    
    
                    Dim x As Integer() = (From p In dt.AsEnumerable() Select p.Field(Of Integer)("Week") Distinct).ToArray()
    
                    'Get the Total of tickes for each subcode.
                    Dim y As Integer() = (From p In dt.AsEnumerable()
                                          Where p.Field(Of String)("CODE") = subcode
                                          Order By p.Field(Of Integer)("Week")
                                          Select p.Field(Of Integer)("TickC")).ToArray()
    
                    For Each xi As Integer In x
                        If Not x1.Contains(xi) Then
                            Array.Resize(y, y.Length + 1)
                            For i = y.Length - 1 To xi Step -1
                                y(i) = y(i - 1)
                            Next
                            y(xi - 1) = 0
                        End If
                    Next

    I get the additional code which enumerates where there is no series, creates one and sets it to 0. However; I'm now up against another behavior which I think is based on maximums. With the query as is, which is based a grouping by party. It is currently set to group by week, which returns 395 rows of data. This throws the following error:

    [IndexOutOfRangeException: Index was outside the bounds of the array.]
       ctxapp2.IM_Group_RES.Page_Load(Object sender, EventArgs e) in path2\IM_Group_RES.aspx.vb:12
       System.Web.UI.Control.OnLoad(EventArgs e) +95
       System.Web.UI.Control.LoadRecursive() +59
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +678

    If I change the grouping to month; this then returns 150 rows of data and the chart renders correctly. Whether based on week or month; there are no null or duplicated rows so I'm at a loss. Could it be due to maximums; is this method limited to x number of enumerations?

    Really appreciate your patience and support with this!

    Many thanks!

    E: So nothing to do with maximums; the error occurs with the following dataset. 

    TickC   Code            Week
    2 ACCESS ISSUE 44 6 APPLICATION ISSUE 44 2 PERFORMANCE 44 1 T4 - PERFORMANCE 44 9 ACCESS ISSUE 45 1 APPLICATION ERROR 45 27 APPLICATION ISSUE 45 8 CONNECTIVITY 45 9 PERFORMANCE 45 5 T4 - ACCESS ISSUE 45 4 T4 - CONNECTIVITY 45 1 T4 - PERFORMANCE 45 13 ACCESS ISSUE 46 23 APPLICATION ISSUE 46 1 CONFIGURATION 46 1 CONNECTIVITY 46 1 CONSULTANCY 46 6 PERFORMANCE 46 6 T4 - ACCESS ISSUE 46 2 T4 - CONFIGURATION 46 4 T4 - CONNECTIVITY 46 2 T4 - PERFORMANCE 46 12 ACCESS ISSUE 47 25 APPLICATION ISSUE 47 5 CONFIGURATION 47 4 CONNECTIVITY 47 5 CONSULTANCY 47 6 PERFORMANCE 47 2 T4 - ACCESS ISSUE 47 3 T4 - CONFIGURATION 47 5 T4 - CONNECTIVITY 47 15 ACCESS ISSUE 48 20 APPLICATION ISSUE 48 1 LICENSE 48 3 PERFORMANCE 48 1 PROFILE 48 1 T4 - ACCESS ISSUE 48 1 T4 - CONFIGURATION 48 2 T4 - CONNECTIVITY 48 1 T4 - PERFORMANCE 48 1 ACCESS ISSUE 49 7 APPLICATION ISSUE 49 1 PERFORMANCE 49 1 T4 - ACCESS ISSUE 49 1 T4 - CONFIGURATION 49 1 T4 - PERFORMANCE 49

    Tuesday, December 3, 2019 10:26 AM
  • User-719153870 posted

    Hi darkgen,

    darkgen

    So nothing to do with maximums; the error occurs with the following dataset. 

    Yes and sorry you met the problem because i took a shortcut in the code i provided.

    In the early sample datatable you provided, the Week value was 1,2 and 3 thus i took it as the counter which you can see it as xi in the code.

    For Each xi As Integer In x
                        If Not x1.Contains(xi) Then
                            Array.Resize(y, y.Length + 1)
                            For i = y.Length - 1 To xi Step -1
                                y(i) = y(i - 1)
                            Next
                            y(xi - 1) = 0
                        End If
                    Next

    In the newest datatable, the week could be 45,46 and so on, so the counter xi would break.

    To solve this problem, all you need to do is to add a fresh counter in the code bove. Please refer to below demo:

    aspx:

    <%@ Register Assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Namespace="System.Web.UI.DataVisualization.Charting" TagPrefix="asp" %>
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:Chart ID="Chart1" runat="server" Height="594px" Width="1000px" BackColor="Silver" BorderlineColor="LightGray">
                    <Titles>
                        <asp:Title Name="Items" Text="Created IM by subcode" />
                    </Titles>
                    <Legends>
                        <asp:Legend Alignment="Center" Docking="Right" IsTextAutoFit="True" Name="Default"
                            LegendStyle="column" />
                    </Legends>
                    <ChartAreas>
                        <asp:ChartArea Name="ChartArea1" BorderWidth="1" />
                    </ChartAreas>
                </asp:Chart>
            </div>
        </form>
    </body>
    </html>
    

    cs:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            If Not Me.IsPostBack Then
                'Fetch the data from database.
                'Dim query As String = "SELECT distinct(ISNULL(TickC,0)) as TickC, CODE, Week FROM someview order by code desc, week"
                Dim dt As DataTable = New DataTable()
                dt.Columns.AddRange(New DataColumn(2) {New DataColumn("CODE"), New DataColumn("Week", Type.GetType("System.Int32")), New DataColumn("TickC", Type.GetType("System.Int32"))})
                dt.Rows.Add("ACCESS ISSUE", 44, 2)
                dt.Rows.Add("APPLICATION ISSUE", 44, 6)
                dt.Rows.Add("PERFORMANCE", 44, 2)
                dt.Rows.Add("T4-PERFORMANCE", 44, 1)
    
                dt.Rows.Add("ACCESS ISSUE", 45, 9)
                dt.Rows.Add("APPLICATION ERROR", 45, 1)
                dt.Rows.Add("APPLICATION ISSUE", 45, 27)
                dt.Rows.Add("CONNECTIVITY", 45, 8)
                dt.Rows.Add("PERFORMANCE", 45, 9)
                dt.Rows.Add("T4 - ACCESS ISSUE", 45, 5)
                dt.Rows.Add("T4 - CONNECTIVITY", 45, 4)
                dt.Rows.Add("T4 - PERFORMANCE", 45, 1)
    
                dt.Rows.Add("ACCESS ISSUE", 46, 13)
                dt.Rows.Add("APPLICATION ISSUE", 46, 23)
                dt.Rows.Add("CONFIGURATION", 46, 1)
                dt.Rows.Add("CONNECTIVITY", 46, 1)
                dt.Rows.Add("CONSULTANCY", 46, 1)
                dt.Rows.Add("PERFORMANCE", 46, 6)
                dt.Rows.Add("T4 - ACCESS ISSUE", 46, 6)
                dt.Rows.Add("T4 - CONFIGURATION", 46, 2)
                dt.Rows.Add("T4 - CONNECTIVITY", 46, 4)
                dt.Rows.Add("T4 - PERFORMANCE", 46, 2)
    
                dt.Rows.Add("ACCESS ISSUE", 47, 12)
                dt.Rows.Add("APPLICATION ISSUE", 47, 25)
                dt.Rows.Add("CONFIGURATION", 47, 5)
                dt.Rows.Add("CONNECTIVITY", 47, 4)
                dt.Rows.Add("CONSULTANCY", 47, 5)
                dt.Rows.Add("PERFORMANCE", 47, 6)
                dt.Rows.Add("T4 - ACCESS ISSUE", 47, 2)
                dt.Rows.Add("T4 - CONFIGURATION", 47, 3)
                dt.Rows.Add("T4 - CONNECTIVITY", 47, 5)
    
                dt.Rows.Add("ACCESS ISSUE", 48, 15)
                dt.Rows.Add("APPLICATION ISSUE", 48, 20)
                dt.Rows.Add("LICENSE", 48, 1)
                dt.Rows.Add("PERFORMANCE", 48, 3)
                dt.Rows.Add("PROFILE", 48, 1)
                dt.Rows.Add("T4 - ACCESS ISSUE", 48, 1)
                dt.Rows.Add("T4 - CONFIGURATION", 48, 1)
                dt.Rows.Add("T4 - CONNECTIVITY", 48, 2)
                dt.Rows.Add("T4 - PERFORMANCE", 48, 1)
    
                dt.Rows.Add("ACCESS ISSUE", 49, 1)
                dt.Rows.Add("APPLICATION ISSUE", 49, 7)
                dt.Rows.Add("PERFORMANCE", 49, 1)
                dt.Rows.Add("T4 - ACCESS ISSUE", 49, 1)
                dt.Rows.Add("T4 - CONFIGURATION", 49, 1)
                dt.Rows.Add("T4 - PERFORMANCE", 49, 1)
    
                'Get the DISTINCT subcodes.
                Dim codes As List(Of String) = (From p In dt.AsEnumerable()
                                                Select p.Field(Of String)("CODE")).Distinct().ToList()
    
                'Loop through the subcodes.
                For Each subcode As String In codes
    
                    'Get the weekno for each subcode.
                    Dim x1 As Integer() = (From p In dt.AsEnumerable()
                                           Where p.Field(Of String)("CODE") = subcode
                                           Order By p.Field(Of Integer)("Week")
                                           Select p.Field(Of Integer)("Week")).ToArray()
    
                    Dim x As Integer() = (From p In dt.AsEnumerable() Select p.Field(Of Integer)("Week") Distinct).ToArray()
    
                    Dim y As Integer() = (From p In dt.AsEnumerable()
                                          Where p.Field(Of String)("CODE") = subcode
                                          Order By p.Field(Of Integer)("Week")
                                          Select p.Field(Of Integer)("TickC")).ToArray()
    
                    Dim a As Integer = 1
                    For Each xi As Integer In x
                        If Not x1.Contains(xi) Then
                            Array.Resize(y, y.Length + 1)
                            For i = y.Length - 1 To a Step -1
                                y(i) = y(i - 1)
                            Next
                            y(a - 1) = 0
                            a += 1
                        Else
                            a += 1
                        End If
                    Next
                    Chart1.Series.Add(New Series(subcode))
                    Chart1.Series(subcode).IsValueShownAsLabel = False
                    Chart1.Series(subcode).BorderWidth = 2
                    Chart1.Series(subcode).ChartType = SeriesChartType.StackedColumn
    
                    Chart1.Series(subcode).Points.DataBindXY(x, y)
    
                    Chart1.Series(subcode).ToolTip = "#SERIESNAME(#VALY)"
                Next
                Chart1.Legends(0).Enabled = True
            End If
        End Sub
    
        'Private Shared Function GetData(ByVal query As String) As DataTable
        '    Dim constr As String = ConfigurationManager.ConnectionStrings("Connection").ConnectionString
        '    Using con As SqlConnection = New SqlConnection(constr)
        '        Using sda As SqlDataAdapter = New SqlDataAdapter(query, con)
        '            Dim dt As DataTable = New DataTable()
        '            sda.Fill(dt)
        '            Return dt
        '        End Using
        '    End Using
        'End Function

    Below is the result of above demo:

    Hope this could help.

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 4, 2019 5:34 AM
  • User705223197 posted

    Fantastic. much much appreciated. Your knowledge, responsiveness and patience has helped me enormously. I will spend many hours now going through this; my next objective will be to include grouping by year in order to show last twelves ordered correctly by year, week no. It's ordered nicely as is as we've happened to be in December! :) Another column "year" to add!

    Thank you for this head-start.

    Wednesday, December 4, 2019 7:45 AM