Published: Wednesday, February 14, 2001
Vertical Graphing of Database Data, Part 2
By Jim Rudnick
Read Part 1
In Part 1 we looked at the beginnings of our ASP graphing script - snarfing
the data from the database. In this part we'll look at how to convert this raw data into a pretty
graph that all managers are guaranteed to enjoy! :-)
Displaying Vertical Graph Bars
I was able at first to create a wonderfully looking HORIZONTAL
graph of the different Divisions percentages, but that's not what I needed. Car hoists work vertically, so my
original thinking was incorrect.
To enable this to work vertically, I realized that I'd have to create a "grid" of 10 x 10 cells in my table, and
fill them from
a top-to-bottom procedure that would write the proper BLANK spaces, the correct CAR image, and then the remaining
number of PIPE
images to create a truely reasonable facsimile of a car hoist. This proved to be difficult too. I worked at it
on and off for over
a week, and got little closer, till I realized that part of my problem was that I wasn't 'nesting' my tables
correctly. Note, in
the code below, that I run each new table (there are 4 of them all together) in the first <TR>
of the first TABLE. This works in
this case, and as it was something I'd never tried before, it was a surprise to me that it did allow me to line up
my 10 x 10 grid so easily...once that worrisome sweating was over!
You can see in the code, that once I've moved through the recordset, I then have to set variables to equal what
was found. I set the
divisional percentage for that division at the first and only run-through of the recordset. This is
fairly straightforward and you'll note that each time I pass through the field, I add that integer to the matching array created
to hold the Divisions. This was done, after much trial and error on my part, to allow me to then later when
building the 10 x 10 grid, to just call up the correct image for each cell, via polling of that array. Seems easy
now, but it
did have me stymied for a week or so as I just couldn't 'wrap' my head around the necessary methodology of populating the grid
with a top-down style.
<%
' VAR land...
'--------------------------------------------------------------------
'set up the percentages to be used for each division...
'
'divisionNum = var to hold the region being shown...
' grab each regions percentage numbers...and put 'em in an array...
If Not RecordSet.BOF Then
RecordSet.MoveFirst
Do Until RecordSet.EOF
'region being shown...
divisionNum = RecordSet.Fields("DIVISION")
arrDivisions(divisionNum) = RecordSet.Fields("PERCENTAGE")
RecordSet.MoveNext
Loop
End If
'--------------------------------------------------------------------
%>
<table border="1" height="350" cellpadding="0" cellspacing="0">
<tr>
<td valign="top">
<table border="1" bordercolor="white" cellpadding="0" cellspacing="0">
<tr><td align="center" valign="top" bgcolor="black" NOWRAP>
<b><font color="white">%</font></b>
</td></tr>
<tr height="35"><td align="center" valign="bottom"><b>100%</b></td></tr>
<tr height="35"><td align="center" valign="bottom"><b>90%</b></td></tr>
<tr height="35"><td align="center" valign="bottom"><b>80%</b></td></tr>
<tr height="35"><td align="center" valign="bottom"><b>70%</b></td></tr>
<tr height="35"><td align="center" valign="bottom"><b>60%</b></td></tr>
<tr height="35"><td align="center" valign="bottom"><b>50%</b></td></tr>
<tr height="35"><td align="center" valign="bottom"><b>40%</b></td></tr>
<tr height="35"><td align="center" valign="bottom"><b>30%</b></td></tr>
<tr height="35"><td align="center" valign="bottom"><b>20%</b></td></tr>
<tr height="35"><td align="center" valign="bottom"><b>10%</b></td></tr>
</table>
</td>
<td>
<table border=0 cellpadding="0" cellspacing="0">
<tr>
<td align=center valign=top bgcolor=red NOWRAP>
<b><font color="white" face="Arial">DIVISION 1</font></B>
</td>
</tr>
<tr>
<td valign=bottom>
<% hoistSmith (arrDivisions(1) / 10) %>
</td>
</tr>
</table>
<td>
<table border=0 cellpadding="0" cellspacing="0">
<tr>
<td align=center valign=top bgcolor=red NOWRAP>
<B><font color="white" face="Arial">DIVISION 2</font></B>
</td>
</tr>
<tr>
<td valign=bottom>
<% hoistSmith (arrDivisions(2) / 10) %>
</td>
</tr>
</table>
<td>
<table border=0 cellpadding="0" cellspacing="0">
<tr>
<td align=center valign=top bgcolor=red NOWRAP>
<B><font color="white" face="Arial">DIVISION 3</font></B>
</td>
</tr>
<tr>
<td valign=bottom>
<% hoistSmith (arrDivisions(3) / 10) %>
</td>
</tr>
</table>
</td>
</tr>
</table>
|
So, now that my array has a matching set of integers, the percentages must be cut down by a factor of 10 to
show properly on my 10 cell high grid, so that was accomplished via the simple piece of code that called
both a SUB and passed along the now modified percentage as a factor of 10 via the line:
hoistSmith (arrDivisions(1) / 10). This is also where I built the table
using nested tables on my 10 x 10 grid outline too. Note, that I'm using an arbitrary sizing for each cell at
60 x 35 (width, height) for the images to sit and look fine at, as that's what their original sizing was in
PhotoShop.
Sub hoistSmith (workingNum)
Select Case (workingNum)
Case (10)
carShow (10)
Case ( 9 )
carShow (9)
Case ( 8 )
carShow (8)
Case ( 7 )
carShow (7)
Case ( 6 )
carShow (6)
Case ( 5 )
carShow (5)
Case ( 4 )
carShow (4)
Case ( 3 )
carShow (3)
Case ( 2 )
carShow (2)
Case ( 1 )
carShow (1)
End Select
end Sub
|
This SUB, hoistSmith, then moved through each of the 10 possibilities that each Division could be
from 100 down to 10%, and passed
that off via a SELECT CASE statement to the actual SUB that controlled the drawing of the images in
that 10 x 10 cell table, called
carShow, and it included in an argument called numRows that passed along just how many
of each type of image to draw in what cell.
This was also a little buggy at first, with me not as yet quite understanding just how to size things, and I must
admit that my
images looked squished and stretched out absolutely awful...though it now looks okay, bad car art notwithstanding!
Sub carShow (numRows)
' where passed var numRows = decreasing rows for 10 to 1...
'----------------------------------------------------------
' put in blanks first...
for bCount = 1 to (10 - numRows)
' counter to show blanks above the car image...
Response.Write("<tr height=35><td align=center Valign=bottom>" & _
"<img src=blank.jpg></td></tr>")
Next
'----------------------------------------------------------
' put in car next...
Response.Write("<tr><td align=center Valign=bottom>" & _
"<img src=car1.jpg></td></tr>")
'----------------------------------------------------------
' put in blanks last
for pCount = 1 to (numRows - 1)
' counter to add pipes for each row below the car...
Response.Write("<tr height=35><td align=center Valign=bottom>" & _
"<img src=pipe.jpg></td></tr>")
Next
end Sub
|
Of course, you can do more. You can change the image of each car perhaps via a stored cartype in the dbase. You could make the
grid a real 100 cells high too, to get a more accurate look at the real percentages that each division used rather than my limited
10 cell grid layout. You could also make each car itself, a "drill-down" so that when a user clicked on same, it would show a new
Web page that might outline details of that division's specific performance over a set time period (something my own client jumped
for later on) or you might even change the whole 'car hoist' paradigm itself. Hot air balloons or blimps, perhaps or what about
salmon swimming upstream or a stack of books or cash? The choice is yours, but the code can help you chart your client's
success and while the "look" of the graph would be ably helped via a better set of cars, other than that...it works!
Happy Programming!
By Jim Rudnick
Attachments:
Download the database and support files (in ZIP format)
Visit the Graphing with ASP Article Index
