|
Introduction
Graphs contribute greatly to the interpretation and understanding of your data. One graph can summarize many pages of data. The raw numbers and statistics don't pack the punch that a good graph presents to the user.
In most cases, adding graphs to your Active Server Pages (ASP) involves using an ActiveX Data Objects or a third-party product such as Seagate Software Inc.'s Info. However, by harnessing the power of basic HTML, Cascading Style Sheets (CSS), and ASP you can create some impressive graphs that will make your data stand out.
I will demonstrate this idea by creating a column graph showing year-to-year hospital census data for the current month, or a month of the user's choosing. Please note that this is optimized for use with Internet Explorer 4.0.
Here is the goal: We want to display census data for the current month and any past years for that same month.
Figure 1
The Code
The first thing that needs to be done is to access the data. I have a query called e_trends that prepares the data for the use in a graph. The e_trends query is held in a Microsoft Access database and performs a cross tabulation on the data to transform the data into rows of months and days and columns of years. This allows a direct comparison of year to year data to show a trend.
The e_trends SQL statement is:
TRANSFORM Avg(IIf([sumofbeds]=0,0,[sumofoccupied]/[sumofbeds]))
AS Pct SELECT Month([date]) AS Month, Day([date])
AS Day
FROM Etotal GROUP BY Month([date]), Day([date])
PIVOT Year([date]);
Figure 1
You can notice in the SQL statement I fix any divide-by-zero errors by checking if any denominators are zero.
I then make an ADODB connection to my data. My data is updated daily by a legacy system with the updated census numbers.
<% Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "census"%>
Because I allow the user to change the month, day, and year of the data for the graph, I first must handle from where those parameters came. By default, I have today's data graphed. I incorporate a client-side script that allows the user to change those parameters. I placed that interface at the bottom of the page.
<%Option Explicit%>
<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 3.0">
<title>Census Trend</title>
</head>
<body>
<p align="center">
Dim m, conn, sql, rs, rsa, lngm, i, y, d, v, strm
if request.querystring("date")="" then
d=date()
else
d=request.querystring("date")
end if
m=month(d)
y=year(d)%>
Now that we have the date parameters, we can "query" the query to create a recordset of the needed data to create the graph. This is done to allow the end users to change the month of the data to produce the graph.
<%sql="select * from e_trends where month="&m
Set RS = Conn.Execute(sql)%>
A select case statement is used to write out the month to create a title for the graph.
<%select case m
case 1
strm="January"
case 2
strm="February"
case 3
strm="March"
case 4
strm="April"
case 5
strm="May"
case 6
strm="June"
case 7
strm="July"
case 8
strm="August"
case 9
strm="September"
case 10
strm="October"
case 11
strm="November"
case 12
strm="December"
end select%>
I use an "if statement" to determine if any records were returned from the request.
The obligatory error message is
<%if rs.eof then
<big><kbd><big><font color="#FF0000"><strong><big>Sorry there is no
data available for the date you indicated.</big></strong></font></big></kbd></big></p>
<%else%>
The variable strm is then used to create the graph's title.
<br> <strong><font face="Arial">
<big>Census <em>Year to Year</em>
Trend for the month of <%=strm%>
, <%=y%></big></font>
</strong></p>
<div align="left">
The use of a table ensures consistency and makes it easy to edit later. The first thing to determine is how tall you want your data; this will be a key factor in later calculations. I chose 300 pixels for the height of my chart. This size should accommodate most screen resolutions.
The first column of the graph will be for the y-axis and the data scale.
Please notice the use of style information to ensure the graph will look the same on every browser.
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td height="300" valign="bottom" width="20"
style="border: medium solid" align="center">
<small><small>
<small><strong>
100%</strong></small></small>
</small><p> </p>
<p> </p>
<p> </p>
<p><small><small><small>
<strong>
50%</strong></small></small>
</small></p>
<p> </p>
<p> </p>
<p> </p>
<p><small><small><small><strong>
0%</strong></small>
</small></small></td>
Because the data that is returned has the first two columns occupied with date data (month and day) we move to the third column to gather our census data by year. All the columns beyond the second column could be data, so we need to use the count attribute of the fields collection to ensure we get all the fields. We loop through the recordset field by field.
<%
do while not rs.eof%>
<% For i = 2 to RS.Fields.Count - 1%>
Check to see if data is returned to avoid an error. If there is no data, set the variable to zero.
<%if isnull(rs(i)) then
v=0
else
v=rs(i)
end if %>
Because the data returned is a percentage, we can multiply the data by 300 (the height of the table) to show the correct column height for the bar on the graph. Be sure to have your bar aligned to the bottom of the table's column.
To represent the bars on the graph you can use graphic or the <hr> tag, adjusting its height and setting its width. I chose to use an image of a bar. You can use any image that will look good if it is stretched or shrunk. I created several images with varying colors so one image is light and the next dark. This will allow for easy reading of the graph. The same could be accomplished with the <hr> tag, and this would be a better solution, but you would have to understand hexadecimals well enough to be able to have the system calculate a light or dark color depending on the previous color choice.
Continuing from the above loop, this code will create the left-most column containing our data scale and the y-axis. I also included the census data value to be displayed above the bar. Bear in mind, there needs to be enough room to display both the bar and the data label in the height of the column.
<td height="300" valign="bottom" width="20"
style="border-top: medium solid; border-bottom: medium solid">
<p align="center"><small><small>
<%=round(v,2)*100&"%"%>
</small></small>
<img src="images/chart<%=i%>.jpg" width="8" height="<%=v*300%>"
alt="chart.jpg"></td>
<% Next %>
I named each image "chart" with a number at the end (chart1.jpg). This way, as we loop through the data, each census year will have a different image.
This section is used a put a placeholder on the graph where there is no data yet. I colored a pipe symbol white so it would not show up visually, but would hold the place of the census data that doesn't exist yet.
<td height="300" valign="bottom" width="1"
style="border-left: 1px dotted; border-top: medium solid; border-bottom: medium solid"><font
color="#FFFFFF">|</font></td>
<%rs.movenext
loop%>
This creates the right-most end of the graph.
<td height="300" valign="bottom" width="2"
style="border-right: medium solid; border-top: medium solid; border-bottom: medium solid"><font
color="#FFFFFF">|</font></td>
</tr>
<tr>
<td align="center"><small><small>Day </small></small><br>
<small><small>of</small></small>
<br>
<small><small>Month</small>
</small></td>
The first row is now complete. We have just created the y-axis and the column bars. We now move to create the x-axis and its data labels, in this case the days of the month. Since we have already looped through the data once, we need to move to the first record before going through it again.
<%rs.movefirst
do while not rs.eof
%>
Because each day of the month can have several years of census data, we need to span as many columns as there is data. In this case we need all the fields minus those fields that hold the month and day, or one column for every year of data.
<td colspan="<%=(RS.Fields.Count - 2)%>"
style="border-left: thin solid; border-top: thin solid; border-bottom: thin solid"><small><p
align="center"><%=rs("day")%></small></td>
<td width="1"
style="border-right: thin solid; border-top: thin solid; border-bottom: thin solid"><font
color="#FFFFFF">|</font></td>
<%
rs.movenext
loop%>
</tr>
</table>
</div><div align="center"><center>
The following code will create the legend at the bottom of the graph.
<table border="1" cellpadding="0" cellspacing="0">
<tr>
<td><p align="center"><strong><small>
Legend <%For i = 2 to RS.Fields.Count - 1
%></small><%=rs(i).name%><small>
<img
src="images/chart<%=i%>.jpg" width="8" height="10"> <%next%></small></strong>
</td>
<small><strong>
</tr>
</table>
</center></div></strong></small>
This creates the form elements so the end user can choose to view a different month of census data. This uses client-side scripting to avoid posting to another page.
Figure 3
<p align="center"><strong><small>
;Click on the day of the month to see the detail.
</small><br>
<a name="another"><em>
Please type in the date to see its census information.</em></a></strong>
</p>
<form ACTION="ecensus.asp?date=" METHOD="POST"
NAME="Census">
<div align="center"><center><p><input type="text" name="Month" size="2" maxlength="2"> / <input
type="text" name="day" size="2" maxlength="2"> / <input type="text" name="year" size="4"
maxlength="4"> <input LANGUAGE="VBScript" TYPE="button" VALUE="Process"
ONCLICK="Window.location.href ="ecensus.asp?date="&month.value&"/"&day.value&"/"&year.value"
NAME="B1"><br>
<em><strong>Ex 01/01/1999</strong></em></p>
</center></div>
</form>
<p><%end if%></p>
</body>
</html>
This is only one application of a graph and only one particular type of graph. Through the power of ASP and CSS you could create some amazing graphs. You could also create other types of graphs. These could include an XY scatter, bar, area, stock, and stacked column or bar graphs. This article demonstrates that with a little persistence you can create some amazing Web applications.
About the Author
John has always enjoyed creating client/server applications and the Web was the next step. In 1995 he made the leap from the world of databases to the Web. Currently he is the Webmaster for the Elliot Hospital's Intranet in Manchester, NH. His main focus is developing business tools using ASP, SQL, and legacy systems. Married with two children, he has a BA and MA in sociology and has a black belt in Shaolin Kempo karate. His current project is to create a repository for on-line financial reports for the hospital, as well as improve existing business functions. he can be reached at jlofaro@optima.org
|
