CODEWITHSUNDEEP
sql-servervb.netcharts
Elhendriks
Elhendriks

Reputation: 119

Creating Pie Chart from SQL Table

I'm trying to build a pie-chart with data provided from an SQL Table. The result of my query is like following:

Total  A    B    C     D     E     F
11005  0  1485  3667  2665  2579  609

The problem is that I have many 'series' that contains only one data for each. I think that I have to build only one 'serie' to be able to display a pie chart. My code is:

 Dim RequeteByTpe As String = "SELECT COUNT(*) as Total,
Sum(CASE
  WHEN [Type] = 'A' THEN 1
   ELSE 0
    END) A,
  Sum(CASE
   WHEN [Type] = 'B' THEN 1
    ELSE 0
     END) B,
  Sum(CASE
  WHEN [Type] = 'C' THEN 1
   ELSE 0
    END) C, 
  Sum(CASE
  WHEN [Type] = 'D' THEN 1
   ELSE 0
    END) D,
 Sum(CASE
  WHEN [Constructeur] = 'E' THEN 1
   ELSE 0
    END) E, 
 Sum(CASE
  WHEN [Constructeur] = 'F' THEN 1
   ELSE 0
    END) F          
 From DBase.dbo.MyTable"  

    Dim DA As New SqlDataAdapter(RequeteByType, ConnexionMyBase)
    Dim DS As New DataSet()
    DAConstC.Fill(DS, "ByType")

    Dim ChartType As New Chart

    Dim ChartArea2 As New ChartArea()

    ChartConstC.ChartAreas.Add("ChartArea2")

    ChartConstC.ChartAreas(0).Area3DStyle.Enable3D = True

    Dim Series21 As Series = New Series("A")
    Dim Series22 As Series = New Series("B")
    Dim Series23 As Series = New Series("C")
    Dim Series24 As Series = New Series("D")
    Dim Series25 As Series = New Series("E")
    Dim Series26 As Series = New Series("F")

    Series21.ChartArea = "ChartArea2"
    Series22.ChartArea = "ChartArea2"
    Series23.ChartArea = "ChartArea2"
    Series24.ChartArea = "ChartArea2"
    Series25.ChartArea = "ChartArea2"
    Series26.ChartArea = "ChartArea2"

    ChartByType.DataSource = DS.Tables("ByType")

    ChartByType.Series.Add(Series21)
    ChartByType.Series.Add(Series22)
    ChartByType.Series.Add(Series23)
    ChartByType.Series.Add(Series24)
    ChartByType.Series.Add(Series25)
    ChartByType.Series.Add(Series26)

    ChartByType.Series(0).YValueMembers = "A"
    ChartByType.Series(1).YValueMembers = "B"
    ChartByType.Series(2).YValueMembers = "C"
    ChartByType.Series(3).YValueMembers = "D"
    ChartByType.Series(4).YValueMembers = "E"
    ChartByType.Series(4).YValueMembers = "F"

    ChartByType.Titles.Add("Type proportions")
    ChartByType.Titles(0).Font = New Font("Arial", 12, FontStyle.Regular)

    ChartByType.Titles(0).Alignment = System.Drawing.ContentAlignment.TopCenter

    ChartByType.Location = New System.Drawing.Point(15, 45)

    ChartByType.Size = New System.Drawing.Size(760, 260)

    Me.Controls.Add(ChartByType)

I don't know if I must change my sql query to get an other type of data or if there is a possibility to fix this with some data attribution method or else. Can anyone please help?

Upvotes: 1

Views: 8284

Answers (1)

WhiteHat
WhiteHat

Reputation: 61222

instead of columns, you need rows...

rather than putting A, B, C in their own column,
total each by row

example sql... 

select [Type], count(*) as Total From DBase.dbo.MyTable group by [Type]

i noticed there are two columns in the case statement,
you could union the results together if needed... 

select [Type] as Label, count(*) as Total From DBase.dbo.MyTable group by [Type]
union all
select [Constructeur] as Label, count(*) as Total From DBase.dbo.MyTable group by [Constructeur]

Upvotes: 2

Related Questions