Charting the Internet--tutorial.jcwcn.com

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: 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: 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: 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

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.

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. 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


		<!--#include file="Utilities/SetDBVals.asp"-->


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 ----------------------


sCurDir = Server.MapPath("images")


   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)

discuss this topic to forum

[Print][Close][Bookmark:digg+,reddit+,del.icio.us+,dzone+]

Charting the Internet

relation tutorial

Category

New

Hot