JerryCRH

Multi-Series chart using SQL query

Recommended Posts

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

Edited by JerryCRH

Share this post


Link to post
Share on other sites

Hi

 

You can check out this example for the same: https://github.com/fusioncharts/vb-net-wrapper/blob/master/Samples/DBExample/index.aspx.vb

 

Please make sure you are creating XML or JSON data according to FusionCharts format. You can use below JSFiddle samples for reference:

- JSON: http://jsfiddle.net/fusioncharts/X9xPM/

- XML: http://jsfiddle.net/sikrigagan/X9xPM/161/

 

Also, according to code you sent earlier. It seems that you are using flash version of FusionCharts, and flash charts are deprecated now. I would suggest you to upgrade to latest version of FusionCharts and FusionCharts VB wrapper.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now