Pretend we have 1 Table called UnitSales (this should look familiar)
This imaginary table has many fields, among them are:
sales_month, sales_city, sales_amount
Now in order to create the graph we will need to get all of the data back into a recordset, figure out the bounds or the arrays, and then populate the arrays for the chart.
Here we go:[code]
<%
Option Explicit
Response.Expires = -1000 ' Make browser not cache pg.
Response.Buffer = True ' Buffer content.
Dim strConnect
strConnect = "you connection string"
Dim sql_data, rs_data, conn
sql_data = "SELECT sales_month, sales_city, sales_amount FROM UnitSales ORDER BY sales_month, sales_city"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnect
Set rs_data = conn.Execute sql_data
'normally at this point I would create an array using GetRows and get rid of my ADO objects for quicker more efficient code, but I will stick with the Recordset in the interest of keeping complexity to a minimum
'Now we need to create the variables for the chart and start the page
%>
<!--#include file="jpsutilityabbr.asp"-->
<!--#include file="jpschart.asp"-->
<html>
<head>
<title>Bar Chart Short Example</title>
</head>
<body>
<%
' Dim var.
Dim objjpsvbChart
Dim aChartXAxisDataLabel(3)
Dim aChartData(2, 3)
Dim aChartLegendSeriesLabel(2)
'Now we are going to either need to determine the bounds and redim the arrays, or we could strt looping through the recordset adding data and only redim if we need more space, I oprefer to count once, redim once, then populate because I am allergic to redim statements inside loops
'we are going to assume there is data in the recordset, normally you would check for EOF first
Dim cityCount, monthCount
Dim fMonth, tMonth
rs_Data.MoveFirst
Do Until rs_data.EOF
'check if this is the same month as the last loop, if not then count it
If tMonth <> rs_data("sales_month") Then
'if this is the first month, then keep track of it for later
If tMonth = "" Then fMonth = rs_data("sales_month")
monthCount = monthCount + 1
tMonth = rs_Data("sales_month")
End If
'city is more complicated as there will be a set for each month, we will just be reading the first month and assuming that all cities will have figures for all months. Normally we wouldn't make this assumption.
'basically we will just count all the entries in this month, assuming each one is a differant city
If rs_data("sales_month") = fMonth Then
cityCount = cityCount + 1
End If
rs_data.MoveNext
Loop
'Now we redim the arrays to the correct size
Dim aChartXAxisDataLabel(monthCount-1)
Dim aChartData(cityCount-1,monthCount-1)
Dim aChartLegendSeriesLabel(cityCount-1)
'Now we need to fill out the data
' to do this I am going to combine all three sections so we can do it in a single loop
rs_data.MoveFirst
'empty the tMonth variable, keep the fMonth variable
tMonth = ""
'empty the counts
cityCount = 0
monthCount = 0
Do Until rs_data.EOF
'first check if we need to add a city label
If tMonth <> rs_data("sales_month") Then
aChartXAxisDataLabel(monthCount) = rs_data("sales_month")
monthCount = monthCount + 1
'we need to reset the cityCount to 0 for later use as data array index
cityCount = 0
End if
'if this is the first month we will need to add the city labels
If fMonth = rs_data("sales_month") Then
aChartLegendSeriesLabel(cityCount) = rs_data("sales_city")
End If
'we increment the city count ouside of the if statement because we will need it for the index of the data array
cityCount = cityCount + 1
'Now lets add the data to the correct location
aChartData(cityCount, monthCount)
rs_data.MoveNext
Loop
'now we just output the other information for the chart, the database portion is done so we can close out our ADO objects
Set rs_data = Nothing
conn.Close
Set conn = Nothing
Create, use, destroy chart object.
' (To print, set browser to print background colors.)
Set objjpsvbChart = New jpsvbChart
objjpsvbChart.ChartDataAreaHeight = 200 ' Optional.
objjpsvbChart.ChartDataAreaWidth = 400 ' Optional.
objjpsvbChart.ChartAlignOnPage = "center" ' Optional.
objjpsvbChart.TitleLine1 = "Unit Sales" ' Optional.
objjpsvbChart.TitleLine2 = "1/1/2001 - 4/30/2001" ' Optional.
objjpsvbChart.YAxisLabel = "Sales" ' Optional.
objjpsvbChart.XAxisDataLabelArray = aChartXAxisDataLabel ' Optional.
objjpsvbChart.DataArray = aChartData
objjpsvbChart.DataNumberPrefix = "$" ' Optional.
objjpsvbChart.DataNumberUseCommaSeparator = True ' Optional.
objjpsvbChart.DataNumberDecimalPlaces = 0 ' Optional.
objjpsvbChart.LegendSeriesLabelArray = aChartLegendSeriesLabel ' Optional.
objjpsvbChart.FooterLine1 = "Month" ' Optional.
objjpsvbChart.FooterLine2 = "Latest preliminary figures" ' Optional.
Call objjpsvbChart.Execute()
Set objjpsvbChart = Nothing
%>
![[lol]](/data/assets/smilies/lol.gif "[lol] [lol]")
