Bar Charts using the FrontPage Database Results Wizard

Introduction

The FrontPage Database Results procedure presented here provides a simple way to display survey information in a horizontal bar chart. Here's what an example bar chart might look like;

Agencies/Organizations/Programs	(55)
Environmental Terms	(28)
Information Systems	(28)
Legislation/Regulations/Legal Terms	(14)
General Terms	(8)
Management/Administrative Terms	(3)
Computer Terms	(5)

You can modify the results to suit your needs.

Technique

The technique uses the FrontPage Database Results Wizard to create a bar chart. The procedure takes advantage of the fact that the HTML image tag allows you to set the height and width of the image, regardless of the images actual size.  So, if you start with a 1x1 pixel image, you can resize it to anything you want when the page displays.

Adding a custom query to the Database Results Wizard

The SQL aggregate function is used to determine the width of the bar . The information is grouped by category and the image width is calculated as a percentage.  Here's how to create this custom query in the DRW;

1. Start the Database Results Wizard and . 

2. Add a custom query in Step 2 of 5 ;
   
   SELECT Category, 
   CInt(Count([ID])/(SELECT Count(ID) FROM TableName)*100) AS [Percent], 
   Count(ID) AS [Count]
   FROM TableName
   GROUP BY Category;
   
   (substitute your tablename and category field - this SQL statement assumes that you also have a field called ID)

This query create three results, 1) the category used to group the results, the number (count) of records in each category and the percent of the total for each category. (The count is only necessary if you want to display it in the results.  It can be removed from the results in step 3 of 5 in the DRW.)

Adding the Image to the DRW

1. In step 4 of 5 of the DRW, choose to display the database results as an HTML table.
2. Create a 1 x 1 image or use this one ( IE - Right Click... Save Picture As... ).
3. Place the image tag in the table cell just before the <<Count>> field (Insert...Advanced...HTML). Here's the image tag HTML you should use;
   
   <img border="0" src="teal.gif" width="<%=fp_rs("Percent")%>" height="10">
   
   You can increase the relative width of the bars by any factor (e.g. 5) like this; <%=5*fp_rs("Percent")%>
   
   Your database results region should then look like this;