ASP.NET 100% Stacked Bar Chart data format?

Keith Westberg
Keith Westberg used Ask the Experts™
on
Data source currently looks as follows:

name    date      total
A            04/16   18  
B            04/16   242
C            04/16   36
D            04/16   98
A            04/17   5
B            04/17   25
C            04/17   19
etc...

I need to render this data as a 100% stacked bar chart using the charting control from asp.net 4.0.  

Chart should have the date as the axis-X and the axis-Y should be 0 to 100%.  
Example: The first bar (04/16) would have A, B, C and D with the appropriate % of space taken in the bar.

Once I can get this to render, the only issue I can see that would change this data landscape would be if a NAME did not have a value for that date.  But I can cross that bridge when it arrives.

Thank you for your time..
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Robert SchuttSoftware Engineer

Commented:
Hi,

I've been playing around with this, sorry for not wanting to post before I had something working.

What I haven't found yet is changing X/Y direction, that is what you wanted, right?

This is what I came up with so far:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<%@ Register Assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
    Namespace="System.Web.UI.DataVisualization.Charting" TagPrefix="asp" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Chart ID="Chart2" runat="server" DataSourceID="SqlDataSource1">
            <Series>
                <asp:Series Name="Series1" ChartType="StackedBar100" XValueMember="date" 
                    YValueMembers="Column1">
                </asp:Series>
                <asp:Series Name="Series2" ChartType="StackedBar100" XValueMember="date" 
                    YValueMembers="Column2">
                </asp:Series>
                <asp:Series Name="Series3" ChartType="StackedBar100" XValueMember="date" 
                    YValueMembers="Column3">
                </asp:Series>
                <asp:Series Name="Series4" ChartType="StackedBar100" XValueMember="date" 
                    YValueMembers="Column4">
                </asp:Series>
            </Series>
            <ChartAreas>
                <asp:ChartArea Name="ChartArea1">
                </asp:ChartArea>
            </ChartAreas>
        </asp:Chart>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:eeConnectionString %>" SelectCommand="SELECT [date], 
100.0*(select SUM(total) FROM [ee].[dbo].[tblQ_27729744] t2 WHERE t2.[date]=t.[date] and [name]='A')/(select SUM(total) FROM [ee].[dbo].[tblQ_27729744] t2 WHERE t2.[date]=t.[date]) as Column1, 
100.0*(select SUM(total) FROM [ee].[dbo].[tblQ_27729744] t2 WHERE t2.[date]=t.[date] and [name]='B')/(select SUM(total) FROM [ee].[dbo].[tblQ_27729744] t2 WHERE t2.[date]=t.[date]) as Column2, 
100.0*(select SUM(total) FROM [ee].[dbo].[tblQ_27729744] t2 WHERE t2.[date]=t.[date] and [name]='C')/(select SUM(total) FROM [ee].[dbo].[tblQ_27729744] t2 WHERE t2.[date]=t.[date]) as Column3, 
100.0*(select SUM(total) FROM [ee].[dbo].[tblQ_27729744] t2 WHERE t2.[date]=t.[date] and [name]='D')/(select SUM(total) FROM [ee].[dbo].[tblQ_27729744] t2 WHERE t2.[date]=t.[date]) as Column4
FROM [ee].[dbo].[tblQ_27729744] t
GROUP BY [date]
ORDER BY [date]"></asp:SqlDataSource>
    </div>
    </form>
</body>
</html>

Open in new window

Robert SchuttSoftware Engineer

Commented:
Here's the output I get with the example data, as you see, missing values are not a problem. Scalability is a bit of a problem, if you need the number of different names to be variable, maybe the series should be added programmatically after doing a SELECT DISTINCT [date]...
chart output
Robert SchuttSoftware Engineer

Commented:
Oh, just tried the StackedColumn100 type for the series and that changes the X/Y axis around.
chart output columns(also added: IsValueShownAsLabel="True")
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Keith WestbergDeveloper

Author

Commented:
Sorry for delay Robert.

I see what you did, and thank you.  My problem is the number of pc's/columns are dynamic.  Could be 8, could be 12+.  I was hoping for a code based solution that would take the dump as provided above and generate the chart as you have rendered it... which is perfect.
Software Engineer
Commented:
Ah, that does make a difference. One solution (maybe not the best but the quickest) is to generate all possible entries from a "SELECT DISTINCT [name] ..." query. Maybe it's possible to generate all at once (pivot query?) but I don't see it yet.

Here's the "quick & dirty" solution: delete the fixed Series and SelectCommand from the aspx:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<%@ Register Assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
    Namespace="System.Web.UI.DataVisualization.Charting" TagPrefix="asp" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Chart ID="Chart2" runat="server" DataSourceID="SqlDataSource1">
            <Series></Series>
            <ChartAreas>
                <asp:ChartArea Name="ChartArea1">
                </asp:ChartArea>
            </ChartAreas>
        </asp:Chart>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:eeConnectionString %>" ></asp:SqlDataSource>
    </div>
    </form>
</body>
</html>

Open in new window


And fill them in the Page Load:
Imports System.Data.OleDb

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Using cn As New OleDbConnection(String.Format("Provider={0}; {1}", "SQLOLEDB", System.Web.Configuration.WebConfigurationManager.ConnectionStrings("eeConnectionString").ConnectionString))
            cn.Open()
            Dim strSqlDistinct As String = "SELECT DISTINCT [name] FROM [ee].[dbo].[tblQ_27729744] ORDER BY [name]"
            Dim cmd As New OleDbCommand(strSqlDistinct, cn)
            Dim rdr As OleDbDataReader = cmd.ExecuteReader()
            Dim strSql As String = "SELECT [date]"
            Dim iColumn As Integer = 0
            While rdr.Read()
                iColumn += 1
                strSql &= ", 100.0*(select SUM(total) FROM [ee].[dbo].[tblQ_27729744] t2 WHERE t2.[date]=t.[date] and [name]='" & rdr.GetString(0).Replace("'", "''") & "')/(select SUM(total) FROM [ee].[dbo].[tblQ_27729744] t2 WHERE t2.[date]=t.[date]) as Column" & iColumn
                With Me.Chart2.Series.Add("Series" & iColumn)
                    .ChartType = DataVisualization.Charting.SeriesChartType.StackedColumn100
                    .XValueMember = "date"
                    .YValueMembers = "Column" & iColumn.ToString()
                    .IsValueShownAsLabel = True
                End With
            End While
            rdr.Close()

            strSql &= " FROM [ee].[dbo].[tblQ_27729744] t " & _
                "GROUP BY [date] " & _
                "ORDER BY [date]"
            Me.SqlDataSource1.SelectCommand = strSql
        End Using
    End Sub
End Class

Open in new window


Sorry for the messy connectstring but it wouldn't work otherwise and I felt it wasn't important to the actual solution...
Keith WestbergDeveloper

Author

Commented:
I see where your going with this... Ill try to jump in this weekend and give it a run.  Thank you Robert... ill let you know how it pans out.

cheers
Robert SchuttSoftware Engineer

Commented:
Cleaning up my monitored questions list I found this, did you ever get anywhere with this?
Keith WestbergDeveloper

Author

Commented:
Thank you Robert.. i to lost track.  =/

Worked great... thumbs up x 2

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial