charting 1

chinedu · May 19, 2003

Hello gurus,
I need an urgent / serious help.
I am looking for a chart/graph or even a pie chart that calculates how many tasks an completes in a month.
So the graph will be looking at taskname, employee name, number of tasks completed and the month.
Eventually, this will be tallied to get the total for a year but we are not worried about that now, although it won't hurt if it is available.
Thanks very much

chinedu · May 22, 2003

hi Tarwn,
matter of fact, your recommended solution was first thing I tried last night when I received that error.
When I made the change, it resulted to this error:
Microsoft VBScript runtime (0x800A0009)
Subscript out of range: '1'
line 92 which is this line:
'Now lets add the data to the correct location
aChartData(cityCount, monthCount) = rs_data("sales_amount&quot

Tarwn · May 22, 2003

Argh

Ok, I'm going to make the database abnd fix the sample code real quick, I'll repost with a tested version of it

For the record it's 7:58 AM EST, lets see how long this takes

-Tarwn

_{01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101}
^{29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19}
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

Tarwn · May 22, 2003

Ok, heres a fixed version, the 3 or 4 changes are commented and marked with #'s. These are changes that were made after the above changes. This code works for me:

Code:

<%
Option Explicit
Response.Expires = -1000 ' Make browser not cache pg.
Response.Buffer = True ' Buffer content.

Dim strConnect
strConnect = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
				 &quot;Data Source=F:\Data\UnitSales.mdb;&quot; & _
				 &quot;Persist Security Info=False&quot;

Dim sql_data, rs_data, conn
sql_data = &quot;SELECT sales_month, sales_city, sales_amount FROM UnitSales ORDER BY sales_month, sales_city&quot;

Set conn = Server.CreateObject(&quot;ADODB.Connection&quot;)
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=&quot;jpsutilityabbr.asp&quot;-->
<!--#include file=&quot;jpschart.asp&quot;-->
<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(&quot;sales_month&quot;) Then
      'if this is the first month, then keep track of it for later
      If tMonth = &quot;&quot; Then fMonth = rs_data(&quot;sales_month&quot;)
      monthCount = monthCount + 1
      tMonth = rs_Data(&quot;sales_month&quot;)
   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(&quot;sales_month&quot;) = 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 = &quot;&quot;
'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(&quot;sales_month&quot;) 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(&quot;sales_month&quot;) <> fMonth Then monthCount = monthCount + 1
      aChartXAxisDataLabel(monthCount) = rs_data(&quot;sales_month&quot;)
      'we need to reset the cityCount to 0 for later use as data array index
      cityCount = 0
      tMonth = rs_data(&quot;sales_month&quot;)	'#### 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(&quot;sales_month&quot;) Then
      aChartLegendSeriesLabel(cityCount) = rs_data(&quot;sales_city&quot;)
   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(&quot;sales_amount&quot;))

   '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 = &quot;center&quot; ' Optional.
objjpsvbChart.TitleLine1 = &quot;Unit Sales&quot; ' Optional.
objjpsvbChart.TitleLine2 = &quot;1/1/2001 - 4/30/2001&quot; ' Optional.
objjpsvbChart.YAxisLabel = &quot;Sales&quot; ' Optional.
objjpsvbChart.XAxisDataLabelArray = aChartXAxisDataLabel ' Optional.
objjpsvbChart.DataArray = aChartData
objjpsvbChart.DataNumberPrefix = &quot;$&quot; ' Optional.
objjpsvbChart.DataNumberUseCommaSeparator = True ' Optional.
objjpsvbChart.DataNumberDecimalPlaces = 0 ' Optional.
objjpsvbChart.LegendSeriesLabelArray = aChartLegendSeriesLabel ' Optional.
objjpsvbChart.FooterLine1 = &quot;Month&quot; ' Optional.
objjpsvbChart.FooterLine2 = &quot;Latest preliminary figures&quot; ' Optional.
Call objjpsvbChart.Execute()
Set objjpsvbChart = Nothing
%>

Also, a note to BullShmidt, you should add some sort of data conversion or type check into the jpschart script, otherwise if the data is not a numeric type you will get a type error on line 316

Hmm...8:24 EST, I must be slowing down

Although that does include makind the db and downloading the files...oh well
-Tarwn

_{01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101}
^{29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19}
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

onpnt · May 22, 2003

show off!!!

as usual impressed and worth but another star on your record [smile]

_________________________________________________________
_{$str = "sleep is good for you. sleep gives you the energy you need to function";

$Nstr = ereg_replace("sleep","coffee",$str); echo $Nstr;}

^{http://www.onpntwebdesigns.com
[/sub]}

chinedu · May 22, 2003

what talent!, wow!!!
it works for me too!
I tip my heart for you.
how come it is only pulling data for one city, though?
I have 4 cities with data from 01/01 - 04/30 but it is only pulling data for one of them.
I must be doing something wrong.

Tarwn · May 22, 2003

errr...I don't know, mine is pulling for all cities...
Here is my sample data:

sales_id sales_month sales_city sales_amount
1 01/2003 Omaha 400
2 01/2003 Tulsa 500
3 01/2003 Raleigh 600
4 02/2003 Omaha 600
5 02/2003 Tulsa 500
6 02/2003 Raleigh 500
7 03/2003 Omaha 600
8 03/2003 Tulsa 550
9 03/2003 Raleigh 425
10 04/2003 Omaha 442.32
11 04/2003 Tulsa 123.45
12 04/2003 Raleigh 654.32

As you can see I decided to do a custom date format, just mm/yyyy, but this shouldn't make a difference.
Ignore the ordering of the data, it got reordered correctly to for my bar chart.

-Tarwn

_{01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101}
^{29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19}
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

chinedu · May 22, 2003

Please ignore last post.
As I said, I must be doing something wrong and I was.
It is fixed now.
You are a BIG star!!!!!
Thanks a million!

Tarwn · May 22, 2003

No problem, it was fun to see if I could still manipulate classes I had never really seen before

I apologiz for all of the earlier errors, I wrote the code entirely on the fly in the post response box, so I got a few errors from writing quickly without color coding or testing

Glad everything worked out for you,

-Tarwn

Bullschmidt · May 22, 2003

Tarwn,

<<
Also, a note to BullShmidt, you should add some sort of data conversion or type check into the jpschart script, otherwise if the data is not a numeric type you will get a type error on line 316

>>

Here's the old "bug is a feature" syndrome. At least if I keep the code as is, the developer can see there is a problem instead of just having non-numeric data get converted to 0. But I can see what you mean especially for user-generated data.

By the way, way to go on the code and explaining things - that's the first I've really seen of that.

Best regards,
J. Paul Schmidt - Freelance ASP Web Developer

http://www.Bullschmidt.com

- Creating "dynamic" Web pages that read and write from databases...

chinedu · May 22, 2003

hey onpnt, you are good too.
Bullschmidt, you are the original inventor, you deserve lots of kudos.
Tarwn, if I have a talent like yours, I would show off every chance I get.
You guys are great and all your efforts to share your talents with little guys like us are TRULY appreciated.

Tarwn · May 22, 2003

Bullschmidt:
have you considered using the Err object to create some custom error messages?
I probably wouldn't want to convert the data for the user, but perhaps do an isNumeric check on the data, Err.raise an error if you hit anything non-numeric, otherwise cDbl it just to be sure it is a numeric string.

I dunno, I doubt I would go back and make the laterations if it was my code, once projects reach a done state I hate to mess with them, but it might be worth considering.

-Tarwn

_{01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101}
^{29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19}
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

chinedu · May 22, 2003

Tarwn, while we are still on this discussion, let me ask you if you don't mind.
If one wishes to extend the functionality of this chart by trying to determine, for instance, which tourist attraction grosses the biggest amount for a city, how would that be handled?
Eample, we just dealt with monthly sales for each city.
If we want to know whether arts and entertainment, sports or museums is our biggest money grosser.
We want to include the city that has that big sales grosser.

Tarwn · May 22, 2003

Well, you could do attarctions across the bottom instead of months, gross on the veryical, and cities like they are now, that would do I'd think.

Dunno, it's basically up to whoever your giving it too how they want the chart, once thats decided then you get the fun part of trying to make the right sql statements and code changes

-Tarwn

_{01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101}
^{29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19}
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

charting 1

chinedu

Technical User

chinedu

Technical User

Tarwn

Programmer

Tarwn

Programmer

onpnt

Programmer

chinedu

Technical User

Tarwn

Programmer

chinedu

Technical User

Tarwn

Programmer

Bullschmidt

Programmer

chinedu

Technical User

Tarwn

Programmer

chinedu

Technical User

Tarwn

Programmer

Similar threads

Part and Inventory Search

Sponsor