|
Part 1: Creating a Chart from a Database Dynamically
In this first part, we take a step-by-step approach to automatically generate a bar (or pie) chart from data stored in a database.
In the second part, we dynamically add hot spots to the bars/slices in our chart and link them to other pages.
This may seem like a tall order, but it can be relatively easily accomplished with the right tools and approach.
Download
Before we start, we need to download two ZIP files:
IntrChart - a component that produces charts (IntrChartXXe.zip), and
IntrChart Database example (IntrChartDB.zip).
Both ZIP files can be downloaded from http://www.compsysaus.com.au/intrchart (for free evaluation).
Set Up IntrChart
Once you have downloaded IntrChartXXe.zip (XX= version) and IntrChartDB.zip you should unzip them both into a suitable directory (e.g., c:\InetPub\IntrChart). I'll refer to this as the IntrChart directory.
Details of the installation can be found in Install.htm, however, I'll review the main points:
- Configure the IntrChart directory to be a virtual Web directory. This can be done using either Internet Information Server (IIS) or Personal Web Server (PWS). I'll assume that you know how to do this, or know someone who does.
- IntrChart has the ability to output JPEG files to disk, or create the JPEG in memory. However, in this example we'll be outputting the file, so you'll need to ensure that IUSR_Machinename has suitable write permissions on the Images directory.
- Ensure that IntrChart.dll and IJL11.dll are copied to [drive]:\[windir]\system32.
- Register IntrChart using the following command:
regsvr32 [drive]:\[windir]\system32\intrchart.dll
(Check that you get a successful confirmation message.)
- Note that you do not need to register IJL11.dll.
Test IntrChart Functions
At this time it is advisable to test your basic installation to ensure that IntrChart is functioning correctly.
Run the supplied examples from the Index page (e.g., //machineName/Intrchart/index.htm), and check that you get a chart produced.
Tip: If you are not getting a chart, try the "file" output setting. This setting allows IntrChart to output a diagnostic if an error occurs. A list of common problems can be found at http://www.compsysaus.com.au/intrchart under Frequently Asked Questions.
Set Up Database
Next we need to set up a database so that IntrChart can access the data to produce the chart. In this article, I use MS Access. Obviously SQL Server or any other ODBC-compliant database could be used. This process is further explained in ExampleDB.asp (part of the downloaded IntrChartDB.zip).
Using the ODBC (Data Sources) Administrator (found in the Control Panel, or Control Panel/Administrative Tools in Win 2000)
- Add a new system DSN,
- Select the MS Access driver,
- Call the DSN "IntrChartDB," and
- Select ExampleDB.mdb located in the IntrChart directory.
Test Database Chart
You should now be able to run the database example (ExampleDB.asp) directly or from ExampleIndex.asp.
Your result should look similar to the example below:
If you open ExampleDB.mdb (using MS Access) and change some of the values in the tblChartData table, you should see corresponding changes in the graph.
While you have the database open, you may want to try adjusting one of the first few colors in tblColor to see how you can change the segment colors from a database.
Database ASP Code
Now that everything appears to be working as we expect, we can get to the nuts and bolts. How does it all work? The secrets are in the ChartDB.asp file, and we'll go through each section here.
The first 15 lines are headings, etc., and are not directly related to the production of the chart, so we'll ignore them, other than to say that any valid HTML can be used.
You may notice the inclusion of IntrUtil.asp. This file is here to include any common IntrChart functions, such as NullToVal, a function that returns a designated value if "null" is present.
The real code starts with the opening of the database connection and reading the chart values and labels from tblChartData:
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "DSN=IntrChartDB; UID=admin"
sQ = ""
sQ = sQ & "Select ChartValue, ChartLabel from tblChartData "
Set RsChartData = Connection.Execute(sQ)
I now read the colors from tblColor. The table only has 16 colors. Later in the code you'll see what I have done to handle when more than 16 values are used. It would probably be better to create a function (or add to the table) to uniformly move through the standard 256 colors, however, this is beyond the scope of this article.
sQ = ""
sQ = sQ & "Select ColorCode from tblColor "
sQ = sQ & "Order by ColorOrder"
Set RsColor = Connection.Execute(sQ)
Next, we must set up our directory to which the chart image will be written.
For this we use the Server.MapPath method that returns the physical directory of the virtual Images directory:
sCurDir = Server.MapPath("images")
Now we create the instance of the IntrChart object:
Set BChrt = Server.CreateObject("IntrChart.Chart")
As a test I normally output the version number using the Version property. This confirms what version I'm using and ensures that I have IntrChart running correctly:
Response.Write "<h3><font face=Arial>IntrChart " & BChrt.Version & " Database Example</font></h3>"
The function below sets the chart properties, etc. I have included the explanation here for clarity, although you'll notice that the function appears elsewhere in the actual example:
DrawIntrChart(BChrt)
'--------------------------------------------------
Sub DrawIntrChart(BChrt)
As part of the example, I output the database values in HTML so that we can see what has been read and match it to the plotted chart. The line below outputs a message to this effect:
Response.write "<hr><h3>Details from database...</h3><table>"
Read through the recordset until you get to the end:
Do While Not RsChartData.EOF
For each record read, output an HTML line that lists the Label, Value, and Color:
(This lists the values in the database; it is not required for the example to actually work.)
Response.write "<tr><td>Chart Label = " & RSChartData("ChartLabel") & "</td>"
Response.write "<td>Value = " & RSChartData("ChartValue") & "</td><td>Color = ""
Response.write "" & RSColor("ColorCode") & "</td></tr>"
Set the IntrChart Value, Color, and Label. A number of other parameters are available, however, they are not set in this example. Full details of all properties can be found in Help.htm.
BChrt.ChartValue RSChartData("ChartValue"), RSColor("ColorCode"), RSChartData("ChartLabel")
Read the next value to be charted:
RsChartData.MoveNext
Here is the trick mentioned earlier for when we might run out of colors. Note that we read the next color, and then immediately close and reopen the color recordset if we have run out of colors. Obviously this will mean that we'll reuse the colors, but it will suffice in this example. You can easily add more colors to the table if 16 are not enough.
RSColor.MoveNext
If RSColor.EOF Then
RSColor.Close
Set RsColor = Connection.Execute(sQ)
End If
We need to loop to read the next value:
Loop
If we have reached this point, then all the values have been read and set in IntrChart. We must end our table that was listing our chart values in HTML:
Response.write "</table><hr>"
The next section of code relates to chart formatting. Without exception, all of these properties in SetDBParams.asp can be left out, although you'll get a fairly plain chart. Take a copy and then delete them in your example to see what you get as they are removed, if you wish. Most of these properties are self-explanatory. Full details are available in Help.htm.
TemplateName = request("TemplateName")
<!--#include file="Utilities/SetDBParams.asp"-->
This Include file sets the chart formatting properties, based on a template. If you open the ExampleDb.mdb and look at the table called tblTemplateProperties, you will see each of properties listed.
I have not included the code for SetDBParams because it is self-explanatory. Read a set of values from a database and then set them in IntrChart. However, I have included a set of properties below that may be used if a database template is not wanted:
BChrt.BackColor = "white"
BChrt.BorderColor = "blue"
BChrt.GraphicBorders = "none"
BChrt.ChartWidth = 400
BChrt.ChartHeight = 300
BChrt.Compression = 90
BChrt.LineWidth = 3
BChrt.LineType = "Line"
BChrt.DotPoints = True
BChrt.GridLinesVertical = False
BChrt.GridLinesHorizontal = True
BChrt.GridLineType = "Solid"
BChrt.LabelBold = True
BChrt.LabelX = "Months"
BChrt.LabelY = "Sales"
BChrt.ShowSegmentValues = False
BChrt.ValueTextX = True
BChrt.ValueTextXBold = False
BChrt.ThousandSeparators = True
BChrt.HasLegend = True
BChrt.LNumbers = True
BChrt.LPercentages = True
BChrt.LPercentAccuracy = 2
BChrt.LBold = True
BChrt.LItalic = False
BChrt.LFont = "Arial"
BChrt.LSize = 8
BChrt.LUnderline = False
BChrt.OffSetPieMax = False
BChrt.PieBorderHeight = 30
BChrt.PieEllipse = False
BChrt.TBold = True
BChrt.TFont = "Arial"
BChrt.TUnderline = False
BChrt.TItalic = False
BChrt.TSize = 12
BChrt.TText = "Database Example Chart"
BChrt.TAlignment = "center"
BChrt.TColor = "black"
BChrt.Layout = "R"
BChrt.Shadow = True
BChrt.ShadowColor = "Grey"
' End of Chart formatting ----------------------
The next few lines are often found confusing, so I'll be brief in my explanation. What we are trying to do here is create a filename and ensure that it is (more or less) unique.
However, I think using the "memory" method in a production version is preferable.
sBuff = DatePart("n", Now()) & DatePart("s", Now())
sFilePath = sCurDir & "\Chart1" & sBuff & ".jpg"
BChrt.FilePath = sFilePath
We are now ready to generate our chart using the CreateChart method. Notice that we have populated cType from SetDBParams.
If we wanted to, we could change this line to:
vMsg = BChrt.CreateChart("bar") or vMsg = BChrt.CreateChart("pie")
vMsg = BChrt.CreateChart(cType)
The next line will output any error messages that may have occurred while processing the chart.
If vMsg <> "" Then
Response.Write vMsg
End If
End Sub
'----------------------------------------------------
The next two lines simply set up the path for the Chart Image to be displayed by inserting "Images/":
iPos = Instr(BChrt.FilePath, "Chart1")
sChartFile = "Images/" & Mid(BChrt.FilePath, iPos)
The only code not covered so far is the DeleteChartFile function. This deletes the created chart file and is self-explanatory:
Sub DeleteChartFile
'---This procedure is for cleaning up the images directory.
' If 5 seconds is not enough delay,
' you may need to increase dPause.
Const dPause = 5
dEnd = DateAdd("s", dPause, Now())
bEndNow = False
While Not bEndNow
If DateDiff("s", Now(), dEnd) <= 0 Then
bEndNow = True
End If
Wend
BChrt.Delete BChrt.FilePath
End Sub
The rest of the code is simple HTML that displays the resulting image.
I hope that you can use what you have read here and easily create your own charts from any data source.
If you are interested in creating hot spots on charts, please take a look at the second part of this article.
Part 2: Adding Hot Spots to Your Charts
In this part, we take a step-by-step approach to automatically adding hot spots to a chart that has been created from a database.
The main objective is to describe how IntrChart, a component that produces charts, can be used to produce charts that have other pages linked to the chart bars/slices via hot spots. Moreover, I illustrate how this can be done without intervention by creating the hot spots from data retrieved from a database.
Included in the previously downloaded file (IntrChartDB.zip) is a file called HotChartDB.asp. It is this file that forms the basis of our discussion here. (The ZIP file can be downloaded from http://www.compsysaus.com.au/intrchart for free evaluation.) We will take the code apart and explain what it does.
Initial Code
As in Part 1, the first 20 odd lines are headings, etc., and not directly related to this discussion.
The next 20-odd lines open the database and request the chart template as described in Part 1, so we'll ignore it here.
We are first interested in what happens in the line below.
<!--#include file="Utilities/SetDBVals.asp"-->
This line calls an Include file that sets the chart values from a database. The code is shown below:
' Get the Chart Values
' Obviously this is a simple data example,
'however, the data could come from any table/query
' as long as it returns a value (and optionally a label)
sQ = ""
sQ = sQ & "Select ChartValue, ChartLabel, HotSpotLink from tblChartData "
Set RsChartData = Connection.Execute(sQ)
' Get the Color record set
' Note that I have deliberately done this last so that I have sQ available if
' 16 colors is not enough and there is an EOF
sQ = ""
sQ = sQ & "Select ColorCode from tblColor "
sQ = sQ & "Order by ColorOrder"
Set RsColor = Connection.Execute(sQ)
These three lines set up the references that will be use to store the linked pages that relate to each chart value. We'll need them later.
Dim sHyperlink(10)
i = 1
Do While Not RsChartData.EOF
BChrt.ChartValue RSChartData("ChartValue"), RSColor("ColorCode"), RSChartData("ChartLabel")
The next code that is different from Part 1 is shown below. This stores the link in our array.
sHyperlink(i) = RSChartData("HotSpotLink")
i = i + 1
RsChartData.MoveNext
RSColor.MoveNext
If RSColor.EOF Then
RSColor.Close
Set RsColor = Connection.Execute(sQ)
End If
Loop
Set RsChartData = Nothing
Set RSColor = Nothing
Next we get our database parameters. The code from the file below is self-explanatory. In fact, you can remove the line completely and get a default chart style.
<!--#include file="Utilities/SetDBParams.asp"-->
After we have finished processing our data, the first thing we notice is that this example uses either File or Memory output:
If Request("OutputType") = "File" Then
The next few lines set up the path and filename
'---Set filepath.
sCurDir = Server.MapPath("images")
sBuff = DatePart("n", Now()) & DatePart("s", Now())
sFilePath = sCurDir & "\Graph" & sBuff & ".jpg"
BChrt.FilePath = sFilePath
Now we can write the chart file. Note that the CreateChart method has a few extra properties (i.e., HotSpotArray, NumberSpots, CoOrds) in this example. Also we use a session variable here (for the chart type), Normally we keep away from session variables, but sometimes (such as here) they are useful.
vMsg = BChrt.CreateChart(Session("cType"), HotSpotArray, NumberSpots, CoOrds)
We handle the errors and set up the path in the normal way.
If vMsg <> "" Then
'---IntrChart Diagnostics
Response.Write vMsg
End If
'---Set virtual filepath
iPos = Instr(BChrt.FilePath, "Graph")
sChartFile = "Images/" & Mid(BChrt.FilePath, iPos)
If the selected output is Memory, then:
Else
The ChartMemDB file is an interesting one. If you open it, you'll notice that database processes SetDBVals and SetDBParams are repeated, even though they have been previously done. This redundancy is by design - as the ChartMemDB file is only used for binary output it must process the chart independently.
sChartFile = "ChartMemDB.asp"
vMsg = BChrt.GetMemHotSpotArray(Session("cType"), HotSpotArray, NumberSpots, CoOrds)
If vMsg <> "" Then
'---IntrChart Diagnostics
Response.Write vMsg
End If
End If
Now we need to build the hot spots. In creating our chart we asked IntrChart to return to us the coordinates of each of the drawn areas. The following line calls the function to translate this information into HTML that can be used in our page.
Response.Write BuildMapString(Request("cType"), HotSpotArray, NumberSpots, CoOrds, sChartFile)
Let's look at the BuildMapString function.
Function BuildMapString(cType, HotSpotArray, NumberSpots, CoOrds, sChartFile)
This code simply finds out if we are plotting a pie chart, in which case we'll need a polygon-shaped hot spot:
Select Case UCase(cType)
Case "BAR"
SpotShape = "rect"
Case Else
SpotShape = "polygon"
End Select
The next line starts the HTML map:
sHTML = "<map name=" & """" & "TestMap" & """" & ">"
Now we need to create the HTML map areas for each chart segment area. The code below is a little cryptic. However, for the purpose of this article, it is safe to assume that you can use the code "as is" to get the hot spots for your own charts.
For i = 1 To NumberSpots
If HotSpotArray(i, 1) <> "" Then
sHTML = sHTML & "<area href=" & """" & sHyperlink(i)& """"
sHTML = sHTML & " shape=" & """" & SpotShape & """"
sHTML = sHTML & " coords=" & """"
For j = 1 To CoOrds
If HotSpotArray(i, j) <> "" Then
sHTML = sHTML & HotSpotArray(i, j) & ", "
End If
Next
sHTML = Left(sHTML, Len(sHTML) - 2) & """" & ">"
End If
Next
sHTML = sHTML & "</map>"
sHTML = sHTML & "<img border=" & """" & "0" & """"
sHTML = sHTML & "src=" & """" & sChartFile & """"
sHTML = sHTML & "usemap=" & """" & "#TestMap" & """" & ">"
BuildMapString = sHTML
End Function
About the Author
Mark Mathieson is managing director and a partner of Compsys Australia Pty Ltd, the company that owns IntrChart.
Born in Zimbabwe, Mark and his family immigrated to Sydney, Australia, in 1989 and set up Compsys in 1993.
Mark has been developing software since 1982 and has been involved in Internet development for the past six years. Specializing in system architecture, Mark has wide experience in many fields, including data modelling, programming, and software development structure and management.
In early 2000, realizing that no easy and inexpensive components existed to create charts in an Internet environment, Mark lead the IntrChart project. Released in November 2000, IntrChart has seen sustained growth both in sales and development over the past eight months.
Contact Mark at markm@compsysaus.com.au.
|
