Vertical Graphing of Database Data, Part 2By Jim Rudnick
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
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.
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
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!
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!