[RESOLVED] Generating reports

[monstez88]

I'm using microsoft chart control to generate reports.

I'm using this to generate reports based on the result I get from a survey. Each survey contains a number of questions and each question has 4 options. What I want is a chart for each question where the x axis is the options and the y axis is the total no. of user that chose that option.

So what I've done is loop through the survey to get the question id, then within that, I have another loop to get the option id, inside the get option id loop, I try to plot the graph.

However, the graph is wrong, it only display the result of the last option despite looping through correctly.

Any help?

[yamaharuss]

Can't help if we don't see your code.

Try Google Charts.. very easy to work with and pass dynamic data to.

[monstez88]

Imports System.Data.SqlClient
Imports System.Web.UI.DataVisualization.Charting

Imports System.Data




Partial Public Class ReportingMain
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

End Sub

Protected Sub btnGenerate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnGenerate.Click
Dim totalQns As Integer
Dim surveyID As Integer

surveyID = ddlSurvey.SelectedValue

'To get total qns for the survey
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim reader As SqlDataReader

conn = New SqlConnection("Data Source=HUIYIWAN2008\SQLEXPRESS;Integrated Security=True;Initial Catalog=HealthLandSystem;")

cmd = New SqlCommand("select TotalQns from Tbl_Survey where SurveyID = " & surveyID)

cmd.Connection = conn
cmd.Connection.Open()
reader = cmd.ExecuteReader()

While reader.Read()

totalQns = reader("TotalQns")

End While

cmd.Connection.Close()

'To get qns id, opt id, question, option
Dim i As Integer
Dim j As Integer
Dim qnsID As String
Dim optID As String
Dim qns As String
Dim opt As String
Dim totalOpt As String

i = 0


Dim conn2 As SqlConnection
Dim conn3 As SqlConnection
Dim cmd2 As SqlCommand
Dim cmd3 As SqlCommand
Dim reader2 As SqlDataReader
Dim reader3 As SqlDataReader





While (i < totalQns)


conn2 = New SqlConnection("Data Source=HUIYIWAN2008\SQLEXPRESS;Integrated Security=True;Initial Catalog=HealthLandSystem;")

'cmd2 = New SqlCommand("select top 4 q.QnsID, o.OptionID, o.[Option], q.Picture, q.Question from Tbl_SurveyQuestions s, Tbl_Questions q, Tbl_Options o Where s.SurveyID = 1 And q.QnsID = o.QnsID And s.QnsID =q.QnsID and s.id Not in (select top " & i & " id from Tbl_SurveyQuestions)order by s.id")
cmd2 = New SqlCommand("select q.QnsID, q.Question from Tbl_SurveyQuestions s, Tbl_Questions q where s.SurveyID = 1 and s.QnsID = q.QnsID order by s.id")
cmd2.Connection = conn2
cmd2.Connection.Open()
reader2 = cmd2.ExecuteReader()


While reader2.Read()

qnsID = reader2("QnsID")
'optID = reader2("OptionID")
qns = reader2("Question")
' opt = reader2("Option")

j = 1

Dim ChartArea1 As ChartArea = New ChartArea()
Dim Legend1 As Legend = New Legend()
Dim Series1 As Series = New Series()
Dim Series2 As Series = New Series()
Dim Series3 As Series = New Series()
Dim Series4 As Series = New Series()
Dim Chart1 = New Chart()
Me.Controls.Add(Chart1)

ChartArea1.Name = "ChartArea1"
Chart1.ChartAreas.Add(ChartArea1)

Legend1.Name = "Legend1"
Chart1.Legends.Add(Legend1)

' Chart1.Location = New System.Drawing.Point(13, 13)
' Chart1.Name = "Chart1"
Series1.ChartArea = "ChartArea1"
Series1.Legend = "Legend1"
Series1.Name = "Series1"
Chart1.Series.Add(Series1)
Chart1.Series.Add(Series2)
Chart1.Series.Add(Series3)
Chart1.Series.Add(Series4)
'Chart1.Size = New System.Drawing.Size(800, 400)
Chart1.TabIndex = 0
' Chart1.Text = "Chart1"

'Count option id
conn3 = New SqlConnection("Data Source=HUIYIWAN2008\SQLEXPRESS;Integrated Security=True;Initial Catalog=HealthLandSystem;")
cmd3 = New SqlCommand("Select OptionID, [option] from Tbl_Options where QnsID = " & qnsID)
'cmd3 = New SqlCommand("Select Count(optionID) as totalOpt from Tbl_UserAns where optionID = " & optID)
'cmd3 = New SqlCommand("Select Count(u.optionID) as totalOpt, u.optionID from Tbl_Questions q, Tbl_UserAns u where u.optionID = " & optID & " and q.QnsID = " & qnsID & " group by u.optionID")
cmd3.Connection = conn3
cmd3.Connection.Open()
reader3 = cmd3.ExecuteReader()

While reader3.Read()

optID = reader3("OptionID")
opt = reader3("Option")
'Label9.Text = reader3("optionID")


Dim strConn As String = "Data Source=HUIYIWAN2008\SQLEXPRESS;Integrated Security=True;Initial Catalog=HealthLandSystem;"

Dim conn4 As New SqlConnection(strConn)

Dim sqlChart As String = "Select Count(u.optionID) as totalOpt, u.optionID from Tbl_Questions q, Tbl_UserAns u where u.optionID = " & optID & " and q.QnsID = " & qnsID & " group by u.optionID"
Dim da As New SqlDataAdapter(sqlChart, conn4)
Dim ds As New DataSet()
da.Fill(ds, "Total")


Chart1.DataSource = ds.Tables("Total")
Chart1.Series("Series" & j).XValueMember = "optionID"
Chart1.Series("Series" & j).YValueMembers = "totalOpt"


j = j + 1

End While
cmd3.Connection.Close()

i = i + 1

End While

cmd2.Connection.Close()
End While



End Sub
End Class



Thats the code I have so far.

The reason for not using google chart is because the data source that I set has no way to allow the chart to populate.

Thanks for your help!

[yamaharuss]

Looks like your code is .NET. This is the ASP forum.

As for Google Charts, if you can output your data, you can populate a chart.