To read the article online, visit http://www.4GuysFromRolla.com/webtech/021401-1.2.shtml

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


  • Article Information
    Article Title: Vertical Graphing of Database Data, Part 2
    Article Author: Jim Rudnick
    Published Date: Wednesday, February 14, 2001
    Article URL: http://www.4GuysFromRolla.com/webtech/021401-1.2.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers