hi experts!
I searched through this forum looking for a barChart and found a helpful thread I really need to adopt.
What is lacking on this code is the interactivity just as it is on What I mean by interactivity is the ability to make changes on the page - to have the ability to change date ranges, change units sold and prices and cities.
I would really, really appreciate any help on getting this accomplished.
Here is the current working I found on this forum by Tarwn.
<%
Option Explicit
Response.Expires = -1000 ' Make browser not cache pg.
Response.Buffer = True ' Buffer content.
Dim strConnect
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=F:\Data\UnitSales.mdb;" & _
"Persist Security Info=False"
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()
Dim aChartData()
Dim aChartLegendSeriesLabel()
'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
ReDim aChartXAxisDataLabel(monthCount-1)
ReDim aChartData(cityCount-1,monthCount-1)
ReDim 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
'initialize the tMonth variable to null
tMonth = ""
'empty the counts
cityCount = 0
monthCount = 0
Do Until rs_data.EOF
'first check if we need to add a month label
If tMonth <> rs_data("sales_month" Then
'#### Need to incrememnt month before assigning the label, also need to not incrememnt the month if this is the first one
If rs_data("sales_month" <> fMonth Then monthCount = monthCount + 1
aChartXAxisDataLabel(monthCount) = rs_data("sales_month"
'we need to reset the cityCount to 0 for later use as data array index
cityCount = 0
tMonth = rs_data("sales_month" '#### need to update the tMonth to reflect current set of dates
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
'Now lets add the data to the correct location
' ###make sure the data type is numeric or will cause error in chart object
aChartData(cityCount, monthCount) = cDbl(rs_data("sales_amount")
'we increment the city count ouside of the if statement because we will need it for the index of the data array
'#### this is done after adding the data so that we have a 0 to x range instead of 1 to x+1
cityCount = cityCount + 1
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
%>
I searched through this forum looking for a barChart and found a helpful thread I really need to adopt.
What is lacking on this code is the interactivity just as it is on What I mean by interactivity is the ability to make changes on the page - to have the ability to change date ranges, change units sold and prices and cities.
I would really, really appreciate any help on getting this accomplished.
Here is the current working I found on this forum by Tarwn.
<%
Option Explicit
Response.Expires = -1000 ' Make browser not cache pg.
Response.Buffer = True ' Buffer content.
Dim strConnect
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=F:\Data\UnitSales.mdb;" & _
"Persist Security Info=False"
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()
Dim aChartData()
Dim aChartLegendSeriesLabel()
'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 ThencityCount = cityCount + 1
End If
rs_data.MoveNext
Loop
'Now we redim the arrays to the correct size
ReDim aChartXAxisDataLabel(monthCount-1)
ReDim aChartData(cityCount-1,monthCount-1)
ReDim 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
'initialize the tMonth variable to null
tMonth = ""
'empty the counts
cityCount = 0
monthCount = 0
Do Until rs_data.EOF
'first check if we need to add a month label
If tMonth <> rs_data("sales_month"
Then'#### Need to incrememnt month before assigning the label, also need to not incrememnt the month if this is the first one
If rs_data("sales_month"
<> fMonth Then monthCount = monthCount + 1aChartXAxisDataLabel(monthCount) = rs_data("sales_month"
'we need to reset the cityCount to 0 for later use as data array index
cityCount = 0
tMonth = rs_data("sales_month"
'#### need to update the tMonth to reflect current set of datesEnd if
'if this is the first month we will need to add the city labels
If fMonth = rs_data("sales_month"
ThenaChartLegendSeriesLabel(cityCount) = rs_data("sales_city"
End If
'Now lets add the data to the correct location
' ###make sure the data type is numeric or will cause error in chart object
aChartData(cityCount, monthCount) = cDbl(rs_data("sales_amount"
)'we increment the city count ouside of the if statement because we will need it for the index of the data array
'#### this is done after adding the data so that we have a 0 to x range instead of 1 to x+1
cityCount = cityCount + 1
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
%>