Take Your Web Site to New Levels

Formatting the Output of the Database Results Wizard into a Horizontal Bar Chart

Sometimes, a visual presentation of your data can be more convincing than simple numbers. Often, online polls use a horizontal bar graph to really bring out the differences between various data categories. While this may appear quite complex, it is not overly difficult to accomplish.

On our website, www.MountainNature.com, we put together a bear safety test. As more people completed the test, we wanted to create a visual results pages so that the media could get a moment by moment look at how people were doing. Here is an example of how we displayed the results.

In August, many of the berries of the Canadian Rockies ripen. Canadian buffaloberry (also known as soapberry or soopolallie) is the single most important food for black and grizzly bears during this period. Of the following images, identify this critical plant. Berry 1 Berry 2 Berry 3 Berry 4 Here is how our respondents performed. Berry 1 19.6%   Berry 2 40.1% (Correct Answer) Berry 3 29.6%   Berry 4 10.7%   In order to build up their fat layers for winter, Bears need foods that are incredibly plentiful, highly nutritious and easily digestible. Since salmon don't make it to the eastern slopes, buffaloberry becomes the most important food to fill all three of these characteristics. You must be able to confidently identify this plant if you will be hiking in the eastern slopes during August and early September when the bears are actively feeding on these berries. Click Here to learn more about buffaloberry

How did we accomplish this nice visual bar graph? It is actually quite simple. Using a modified image tag you can have a simple graphic resize itself to represent the amount of data in a numeric field. In this case, the most difficult part of the example was creating the query in order to calculate the percentage of respondents that selected each answer. This required a custom SQL statement with a complex sub-query. We'll discuss that in another tutorial.

For this example though, you can display ANY numeric value using a bar graph. In most cases, if you are looking to graph a value, you are likely trying to illustrate some aggregate function such as a count of how many people purchased one product over another. Here are some other situations where a bar chart may be useful.

• comparing how many products come from each of your suppliers
• displaying a comparison of the sales volume of each of your regions, sales departments or sales people
• showing the results of a poll, survey or test

We've already looked at how to create a count query. These queries will filter your data and group it based upon a particular field and then count the number of values in each category. You can read the tutorial here. In that example, we created a query that would count the number of products in each product category. We then displayed the results as follows:

Wouldn't it be nice if we could add a little bar chart to this to make it more visually distinctive? In order to accomplish this, you'll first need to complete the tutorial on creating a count query and insert your Database Results Wizard below. If you followed our example, your DRW will look like the following:

To add the graph, we'll place a special image tag between the <<CategoryName>> and <<CountOfCategoryName>> fields. This tag will resize according to the value of the <<CountOfCategoryName>> field.

The syntax is as follows:

<img border="0" src="barchartimage.gif" width="<%=fp_rs("CountOfCategoryName")%>" height="10">

This tells it to get an image called barchartimage.gif from the images folder and adjust the width based on the value of the <<CountOfCategoryName>> field. The height has been set at 10 pixels.

Don't forget to paste the above code into Notepad, then copy it again before pasting it into FrontPage.

To create your image, just create a 1 pixel by 1 pixel gif file of any colour, or copy this image . Save it as barchartimage.gif in the same directory as your asp page.

Now, place your cursor between the two fields on your DRW. Click on the HTML pane and then paste the code. When you switch back to normal view, you'll see the following:

Save your page and preview it in your browser. You should get the following results:

Beverages  (10)
Condiments  (9)
Confections  (16)
Dairy Products  (11)
Grains/Cereals  (7)
Meat/Poultry  (7)
Produce  (5)
Seafood  (12)

Depending on your data, you can make the bar appear longer by using a little math. Change the following code:

<img border="0" src="barchartimage.gif" width="<%=fp_rs("CountOfCategoryName")%>" height="10">

and add a multiplier before the <%=fp_rs such as <%=3*fp_rs. You could use any multiplier that seems appropriate. Here we multiplied the value of the <<CountOfCategoryName>> by 3 to add a little length to the graph. This would change our example to look like the following:

Beverages  (10)
Condiments  (9)
Confections  (16)
Dairy Products  (11)
Grains/Cereals  (7)
Meat/Poultry  (7)
Produce  (5)
Seafood  (12)

You can also adjust the thickness of the bar by adjusting the height property. This allows you to alter the bar chart based upon your needs...and all it took was a little FrontPage Magic.

Vertical Bar Graphs

Vertical Bar Graphs are more difficult. You CAN make them work, but only in certain circumstances. In our first example, the individual bars on the graph represented different values for the same database record. If you are using the graph to display different records in the database, then you can create a vertical graph instead.

Here is an example:

1	2	3	4	5	6	7	8	9	10

In this case, I created a database with 10 different fields with a single value for each record. The record names were the numbers 1 to 10.The process is not dramatically different than in the first example. In this case, you create a Database Results Wizard that displays each of the ten records. In Step , deselect the Show Header Row With Column Labels AND Expand Table to width of page. This will create a DRW that looks like the following:

As you see below, the data displays a simple row of data values with no labels.

The next step involves adding a blank row below the main data row in the DRW. Select the entire row, right clik and then select Insert-Rows. Drag the data values into the upper row.

On the bottom row, type your labels, in this case the numbers 1 to 10. It will now look like the following:

Finally, we need to replace the data fields with the image tags. In this case, we'll have the height value change to reflect the data. Here is how the image tag will be formatted for the first record value:

<img border="0" src="Images/barchartimage.gif" width="10" height="<%=fp_rs("1")%>">

In this case, you'll see the height value is:

height="<%=fp_rs("1")%>

The number "1" is the name of our first record. To set your image tag, paste the above code into Windows Notepad and then copy it again. Click on the name of the field value (e.g. <<1>>) and then click on the HTML pane. You'll see the field code highlighted. Click the delete button to remove it and then paste the image tag into the same spot. This will essentially replace the database value with the graph line image.

Once you've got it working for your first record value, then you need to do each of the remaining records, one at a time, changing the number value to reflect the individual field names. Here is the final result:

1	2	3	4	5	6	7	8	9	10

Let's Add Some Alt Tags to the Images

One of the most important aspects of using images in websites is the addition of Alternate (Alt) text. This allows you to add descriptive text to an image. For visually impaired users this is critical since they can at least have access to the text description. In this case, we want to add alt text that displays the value of the field. This makes the data interactive, allowing you to hover your mouse over the graph line and see the actual graph value.

To do this, right click on the image and select properties. Then click on the General tab.

In the Text field type the following:

<%=FP_FieldVAL(fp_rs,"GraphFieldName")%>%

This will tell the image to display the following:

<<GraphFieldName>>%

This will be translated into the following:

42.7%

Give it a try. All the graph lines in this tutorial will display this interactive data. All it took was a little FrontPage Magic.