Remove From My Forums

Dynamically created series using StackedColumn - Overlap / gaps between series.

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

0

Sign in to vote
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

0

Sign in to vote

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

Answered by:

Dynamically created series using StackedColumn - Overlap / gaps between series.

Question

Answers

darkgen

All replies

darkgen

darkgen