ChartDirector 6.0 (ASP/COM/VB Edition)

Pass Data as HTTP Query Parameters




In this example, we will introduce a technique where the database query is performed in the containing HTML page, and the data is passed to the ChartDirector script as HTTP GET query parameters. Using this method, it is possible to perform one database query and feed the data to multiple charts.

The user will select a year from a HTML form and press OK. The web server will query a database to obtain the necessary data, and return a web page containing the bar chart and the line chart for the selected year.

The code for producing the HTML form is listed below.

The code outputs a drop down select list to allow the user to select a year. Based on the selected year, it queries the database and read the data into a DBTable object. The data are then serialized into comma delimited strings using the VBScript "Join" function . Two <IMG> tags are used to invoke two scripts for charts generation, where the data are passed in as HTTP GET query parameters using the comma delimited strings.

[Web Version (in ASP)] aspdemo\dbdemo2.asp
<%@ language="vbscript" %>
<%
Set cd = CreateObject("ChartDirector.API")


' The currently selected year
selectedYear = Request("year")
If Request("year") = "" Then
    selectedYear = 2001
Else
    selectedYear = CInt(Request("year"))
End If

' SQL statement to get the monthly revenues for the selected year.
SQL = "Select Software, Hardware, Services From revenue Where Year(TimeStamp) = " & selectedYear & _
    " Order By TimeStamp"

'
' Connect to database and read the query result into arrays
'

Set rs = CreateObject("ADODB.RecordSet")
Call rs.Open(SQL, "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
    Server.MapPath("sample.mdb"))
Set dbTable = cd.DBTable(rs)
rs.Close()

software = dbTable.getCol(0)
hardware = dbTable.getCol(1)
services = dbTable.getCol(2)

' Serialize the data into a string to be used as HTTP query parameters
httpParam = "year=" & selectedYear & "&software=" & Join(software, ",") & "&hardware=" & Join( _
    hardware, ",") & "&services=" & Join(services, ",")

'
' The following code generates the <option> tags for the HTML select box, with the <option> tag
' representing the currently selected year marked as selected.
'

ReDim optionTags(2001 - 1990)
For i = 1990 To 2001
    If i = selectedYear Then
        optionTags(i - 1990) = "<option value='" & i & "' selected>" & i & "</option>"
    Else
        optionTags(i - 1990) = "<option value='" & i & "'>" & i & "</option>"
    End If
Next
selectYearOptions = Join(optionTags, "")
%>
<html>
<body style="margin:5px 0px 0px 5px">
<div style="font-size:18pt; font-family:verdana; font-weight:bold">
    Database Integration Demo (2)
</div>
<hr style="border:solid 1px #000080" />
<div style="font-size:10pt; font-family:verdana; width:600px">
This example demonstrates creating a chart using data from a database. The database
query is performed in the containing HTML page. The data are then passed to the chart
generation pages as HTTP GET parameters.
<ul>
    <li><a href="viewsource.asp?file=<%=Request("SCRIPT_NAME")%>">
        View containing HTML page source code
    </a></li>
    <li><a href="viewsource.asp?file=dbdemo2a.asp">
        View chart generation page source code for upper chart
    </a></li>
    <li><a href="viewsource.asp?file=dbdemo2b.asp">
        View chart generation page source code for lower chart
    </a></li>
</ul>
<form>
    I want to obtain the revenue data for the year
    <select name="year">
        <%=selectYearOptions%>
    </select>
    <input type="submit" value="OK">
</form>
</div>

<img src="dbdemo2a.asp?<%=httpParam%>">
<br><br>
<img src="dbdemo2b.asp?<%=httpParam%>">
</body>
</html>

The <IMG> tags in the above code invoke "dbdemo2a.asp" and "dbdemo2b.asp" for charts generation. In "dbdemo2a.asp" and "dbdemo2b.asp", the data is retrieved from the HTTP GET query parameters and deserialized back into arrays by using the VBScript "Split" function. The data are then used to generate the charts.

[Web Version (in ASP)] aspdemo\dbdemo2a.asp
<%@ language="vbscript" %>
<%
Set cd = CreateObject("ChartDirector.API")

'
' Retrieve the data from the query parameters
'

selectedYear = Request("year")

software = Split(Request("software"), ",")
hardware = Split(Request("hardware"), ",")
services = Split(Request("services"), ",")

'
' Now we obtained the data into arrays, we can draw the chart using ChartDirector
'

' Create a XYChart object of size 600 x 300 pixels, with a light grey (eeeeee) background, black
' border, 1 pixel 3D border effect and rounded corners.
Set c = cd.XYChart(600, 300, &Heeeeee, &H000000, 1)
Call c.setRoundedFrame()

' Set the plotarea at (60, 60) and of size 520 x 200 pixels. Set background color to white (ffffff)
' and border and grid colors to grey (cccccc)
Call c.setPlotArea(60, 60, 520, 200, &Hffffff, -1, &Hcccccc, &Hccccccc)

' Add a title to the chart using 15pt Times Bold Italic font, with a light blue (ccccff) background
' and with glass lighting effects.
Call c.addTitle("Global Revenue for Year " & selectedYear, "timesbi.ttf", 15).setBackground( _
    &Hccccff, &H000000, cd.glassEffect())

' Add a legend box at (70, 32) (top of the plotarea) with 9pt Arial Bold font
Call c.addLegend(70, 32, False, "arialbd.ttf", 9).setBackground(cd.Transparent)

' Add a line chart layer using the supplied data
Set layer = c.addLineLayer2()
Call layer.addDataSet(software, &Hff0000, "Software").setDataSymbol(cd.CircleShape, 9)
Call layer.addDataSet(hardware, &H00ff00, "Hardware").setDataSymbol(cd.DiamondShape, 11)
Call layer.addDataSet(services, &Hffaa00, "Services").setDataSymbol(cd.Cross2Shape(), 11)

' Set the line width to 3 pixels
Call layer.setLineWidth(3)

' Set the x axis labels. In this example, the labels must be Jan - Dec.
labels = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec")
Call c.xAxis().setLabels(labels)

' Set the y axis title
Call c.yAxis().setTitle("USD (Millions)")

' Set axes width to 2 pixels
Call c.xAxis().setWidth(2)
Call c.yAxis().setWidth(2)

' Output the chart in PNG format
Response.ContentType = "image/png"
Response.BinaryWrite c.makeChart2(cd.PNG)
Response.End
%>

[Web Version (in ASP)] aspdemo\dbdemo2b.asp
<%@ language="vbscript" %>
<%
Set cd = CreateObject("ChartDirector.API")

'
' Retrieve the data from the query parameters
'

selectedYear = Request("year")

software = Split(Request("software"), ",")
hardware = Split(Request("hardware"), ",")
services = Split(Request("services"), ",")

'
' Now we obtained the data into arrays, we can draw the chart using ChartDirector
'

' Create a XYChart object of size 600 x 300 pixels, with a light grey (eeeeee) background, black
' border, 1 pixel 3D border effect and rounded corners.
Set c = cd.XYChart(600, 300, &Heeeeee, &H000000, 1)
Call c.setRoundedFrame()

' Set the plotarea at (60, 60) and of size 520 x 200 pixels. Set background color to white (ffffff)
' and border and grid colors to grey (cccccc)
Call c.setPlotArea(60, 60, 520, 200, &Hffffff, -1, &Hcccccc, &Hccccccc)

' Add a title to the chart using 15pt Times Bold Italic font, with a dark green (006600) background
' and with glass lighting effects.
Call c.addTitle("Global Revenue for Year " & selectedYear, "timesbi.ttf", 15, &Hffffff _
    ).setBackground(&H006600, &H000000, cd.glassEffect(cd.ReducedGlare))

' Add a legend box at (70, 32) (top of the plotarea) with 9pt Arial Bold font
Call c.addLegend(70, 32, False, "arialbd.ttf", 9).setBackground(cd.Transparent)

' Add a stacked area chart layer using the supplied data
Set layer = c.addAreaLayer2(cd.Stack)
Call layer.addDataSet(software, &H40ff0000, "Software")
Call layer.addDataSet(hardware, &H4000ff00, "Hardware")
Call layer.addDataSet(services, &H40ffaa00, "Services")

' Set the area border color to the same as the fill color
Call layer.setBorderColor(cd.SameAsMainColor)

' Set the x axis labels. In this example, the labels must be Jan - Dec.
labels = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec")
Call c.xAxis().setLabels(labels)

' Set the y axis title
Call c.yAxis().setTitle("USD (Millions)")

' Set axes width to 2 pixels
Call c.xAxis().setWidth(2)
Call c.yAxis().setWidth(2)

' Output the chart in PNG format
Response.ContentType = "image/png"
Response.BinaryWrite c.makeChart2(cd.PNG)
Response.End
%>