• Content count

  • Joined

  • Last visited

About JerryCRH

  • Rank
    Junior Member
  1. Multi-Series chart using SQL query

    Here is what I have so far. The error is "invalid data" Dim connectionString10 As String Dim ds10 As New DataSet Dim stringxml10 As New StringBuilder Dim stringxml11 As New StringBuilder Dim stringxml12 As New StringBuilder connectionString10 = "Data Source=server;Initial Catalog=database;User ID=User;Password=password" stringxml10.Append("<graph caption='Discrepancies by Drug by Month' logoURL='logo_crh.gif' logoposition='CC' logoalpha='20' logoscale='150' xAxisName = 'Drug' showDivLineSecondaryValue='1' showSecondaryLimits='1' showValues='0' animate='1' bgcolor='#006600,#FFFDDD' bgratio='0,100' canvasbgAlpha='0' >") Using conn10 As New SqlConnection(connectionString10) conn10.Open() Dim qry10 As New SqlCommand("select distinct(datepart(month,tdate)) TheMonth9 " & _ "from floorstock_discrepancy " & _ "where tdate >= getdate()-180 group by drug, datepart(month,tdate)", conn10) Dim rst10 As SqlDataReader = qry10.ExecuteReader() stringxml10.Append("<categories> ") While rst10.Read() stringxml10.Append("<category label='{0}'/>", rst10("TheMonth9").ToString()) ' & "' />") End While rst10.Close() conn10.Close() stringxml10.Append("</categories>") conn10.Open() Dim qry11 As New SqlCommand("select count(drug) DrugCount, drug Drug2, convert(varchar,datename(month,tdate)) TheMonth2 " & _ "from floorstock_discrepancy " & _ "where tdate >= getdate()-180 group by drug, convert(varchar,datename(month,tdate))", conn10) Dim rst11 As SqlDataReader = qry11.ExecuteReader() While rst11.Read() stringxml10.AppendFormat("<dataset seriesName='{0}'>", rst11("Drug2").ToString()) stringxml10.AppendFormat("<set value='{0}'/>", rst11("drugcount").ToString()) End While rst11.Close() conn10.Close() stringxml10.Append("</dataset>") stringxml10.Append("<styles> <definition> <style name='B' type='animation' easing='bounce' duration='1' /> <style name='Bevel1' type='bevel' angle='0' /> <style name='Anim1' type='animation' param='_xScale' start='0' duration='3' /> <style name='Anim2' type='animation' param='_alpha' start='0' duration='3' easing='bounce' /> </definition> <application> <apply toObject='CANVAS' styles='Anim1, Anim2, Bevel1' /> ") stringxml10.Append("<apply toObject='DATAPLOT' styles='Anim1, Anim2, Bevel1, B' />") stringxml10.Append("</application> </styles>") stringxml10.Append("</graph>") conn10.Close() Literal4.Text = FusionCharts.RenderChart("FusionCharts/MSBar2D.swf", "", stringxml10.ToString(), "TheMonth9", "500", "300", _ False, True) End Using
  2. Does anyone have an example of a multi0series chart using a SQL query as a data source?