Today you'll learn how to add graph displays of your data to your database programs. By creating a simple graphing library that uses the graph control that ships with Visual Basic Professional, you can easily create solid visual displays of your database.
You'll also learn how to use SQL SELECT statements for creating data sets to use as the basis for your graphs. These SQL statements can be built into your code or stored as QueryDef objects in your database.
You'll also learn how to save the generated graphs to disk as bitmap files, how to share your graphs with other programs by placing them on the Windows Clipboard, and how to send the completed graphs to the printer.
And finally, when you complete this chapter, you'll have a graphing library that you can use in all your future Visual Basic projects. As an example, you'll add a set of default graphs to the CompanyMaster project you started last week.
Although generating data graphs is not, strictly speaking, a database function, almost all good database programs provide graphing. Visual representations of your data are much easier for users to understand than tables or lists. Providing graphs in your database programs also gives users the chance to look at the data in more than one way. Often, users will discover important information in their data simply by looking at it from another angle.
Providing graphs also gives your programs an added polish that users appreciate. Quite often users want more than a simple data entry program with a few list reports. Many times, users will take data that was created with a Visual Basic program and export it to another Windows application in order to develop graphs and charts. Using the techniques you'll learn today, you can provide your users with all the graphing tools they will need to develop graphs and charts without using other programs!
The graph control has a multitude of properties that you can manipulate in order to customize the graph display. I will not cover all of the options here. You can review the Visual Basic documentation for detailed information on the properties of the graph control. I will, however, cover the most commonly used properties, showing you how to use them to control the way graphs appear on your forms. Here is a list of the property settings covered:
I'll also show you how to use the DrawMode property to send the completed graph to a printer, save it as a file, or copy it to the Windows Clipboard.
NOTE: Visual Basic ships with up to two graphing OCXs: GRAPH16.OCX and GRAPH32.OCX. Both OCXs have the same features. However, if you do not have the 32-bit version of Visual Basic, you won't have the GRAPH32.OCX. In this book, the graph control refers to either the 16-bit or the 32-bit versions of the graph control.
Before you can start using the graph control tool, you have to make sure it is loaded into the Visual Basic toolbox. To do this, load Visual Basic and select Tools | Custom Controls from the Visual Basic main menu. In the list of available controls, locate Pinnacle-BPS Graph Control (see Figure 12.1). Click the checkbox to add the control to your toolbox and then click the OK button to exit the form.
Adding the graph control to your toolbox.
TIP: If you want to add the graph tool to all your future projects, open the AUTO16LD.VBP (or the AUTO32LD.VBP) project file and follow the steps outlined in the preceding paragraph. After you save the Autoload project, the graph tool will automatically appear in the toolbox of all your new Visual Basic projects.
It's very easy to create a good-looking graph using the graph tool. All you need to do is add the control to your form and fill it with data; the graph control will do the rest. Let's create a simple graph to illustrate this point.
NOTE: If you have not already loaded Visual Basic and added the graph control to the current project, review the previous section and perform the required steps.
Add the graph control to a blank form by double-clicking the graph control icon in the toolbox. You'll see that the graph control automatically displays a two-dimensional bar graph with some data. Now stretch the control so that your form looks like the one shown in Figure 12.2.
Adding the graph control to your form.
This is random data that the control automatically generates to help you get an idea of how the graph will look in your finished program. When you add your real data to the graph control, this random data will disappear.
You determine the type of graph Visual Basic will display by setting the GraphType property. You can do this using the properties window during design time or through Visual Basic code at runtime. Because you already have the graph up on your form, move to the Properties window and locate the GraphType property. Set the property to display a three-dimensional pie chart by clicking the property in the window and then pulling down the list box. Find and select the 3D Pie option. Your screen should look like the one shown in Figure 12.3.
Changing the GraphType property at design time.
The graph control can display 11 different types of graphs including bar charts, pie charts, line and area graphs, Log/Lin graphs, Gantt charts, scatter graphs, polar graphs, and high/low/close graphs. Not all of them are covered here, but you will work with the three most commonly used formats: bar, pie, and line graphs.
Before you can display data, you have to load it into the graph control. But, before you even load it into the control, you need to know how the graph control expects to see the data. The graph control requires that you give it all the data organized in sets and points. The graph control needs to know how many points of data there are in each set you want to graph. Usually, you will have a single set of data with multiple points. For example, if you want to graph company sales figures for the last 12 months, you would have a single data set (company sales figures) with 12 points (one for each month). If you want to create a graph that compares the actual monthly sales figures with the budgeted figures for the last 12 months, you would have two sets of data (actual and budget figures), each with 12 points (one for each month).
You can use the NumSets and NumPoints properties to inform the graph control how the data is to be organized. You'll now create a graph like the one just described. In design mode, use the Property box to set the NumPoints property to 12 and the NumSets property to 1. You have just told the graph control that it should prepare for one set of data containing 12 individual points.
Now add 12 data items at design time so that you can see how the graph will look. Locate the GraphData property in the Property box. It should be set to 0. Now type 1 and press the Enter key. You have just added one of the expected 12 data points for the set. Continue to add data by entering 2, 3, and so on until you have entered values up to 12.
Save the form now as GRAPHDES.FRM, and save the project as GRAPHDES.VBP. When you run the project, your graph should now look something like the one shown in Figure 12.4.
Adding data in design mode.
You can perform the same task at runtime using Visual Basic code. Now add a command button to this form, setting its Name property to cmdSales and its Caption property to Sales. Then add the code in Listing 12.1 in the cmdSales_Click event:
Listing 12.1. Adding code to the cmdSales_Click event.
Private Sub cmdSales_Click()
Dim x As Integer ' for loop counter
'
Graph1.DataReset = gphAllData 'reset all properties
Graph1.GraphType = gphBar3D ' set to 3d bar
'
Graph1.NumSets = 1 ' only one set of data
Graph1.NumPoints = 12 ' one for each month
'
' now add the data items for each point
'
Graph1.ThisSet = 1 ' this is our only set
For x = 1 To 12
Graph1.ThisPoint = x ' which point
Graph1.GraphData = x ' data item to load
Next x
'
Graph1.DrawMode = gphDraw ' show the completed graph
'
End Sub
In Listing 12.1, you do a few things. First, you clear out any data that might already be stored in the graph control. Next, you set the GraphType property to show a three-dimensional bar graph. Also, you set the NumSets and NumPoints properties to 1 and 12, respectively, and then add the data points. Notice that the graph control must be told which set you want filled (using the ThisSet property). Next, you go through a loop—first, setting the ThisPoint property and then adding the data item. Finally, you set the DrawMode property to gphDraw to force Visual Basic to redraw the graph with the new data.
Save and run the project. When you click the Sales button, your form will look similar to the one shown in Figure 12.5.
Creating a graph using Visual Basic code.
Although this method works well, there is an alternative method that is faster. By setting the AutoInc property of the graph control to 1, the graph control will automatically increment the NumPoints property. This can simplify and speed up your code. Add a new button to the form, setting its Name property to cmdAutoSales and its Caption property to &AutoSales. Then add the code in Listing 12.2 in the cmdAutoSales_Click event:
Listing 12.2. Adding code to the CmdAutoSales_Click event.
Private Sub cmdAutoSales_Click()
Dim x As Integer ' for loop counter
'
Graph1.DataReset = gphAllData 'reset all properties
Graph1.GraphType = gphBar3D ' set to 3d bar
'
Graph1.NumSets = 1 ' only one set of data
Graph1.NumPoints = 12 ' one for each month
'
' now add the data items for each point
'
For x = 1 To 12
Graph1.GraphData = x ' data item to load
Next x
'
Graph1.DrawMode = gphDraw ' show the completed graph
'
End Sub
Save and run the project. When you press the AutoSales button, you'll see the same graph that was generated with the Sales button. Notice that, in this example, you left out the lines of code that set the ThisSet and the ThisPoint properties. These values were handled by the graph control using the AutoInc property. This might not seem like a code savings, but it really is. Single set data is relatively easy to graph. Multiple sets get pretty confusing. It's much easier to use the AutoInc property because it automatically updates the ThisSet property, too. There is yet another way to add data to a graph control: using the QuickData property.
You can use the QuickData property to add graph data in a single command at runtime. The QuickData property accepts a single character string that contains all the data sets and points. Each data set must be separated by a carriage return/line feed pair. Each data point must be separated by a tab character. This is known as tab-delimited data. When you use the QuickData property to load graph data, you do not have to set any of the properties that deal with points or sets. You also do not have to force your Visual Basic code to process any For...Next loops.
Add another command button to the form, setting its Name property to cmdQuickSales and its Caption property to &QuickSales. Then add the Listing 12.3 in the cmdQuickSales_Click event window:
Listing 12.3. Adding code to the cmdQuickSales_Click event.
Private Sub cmdQuickSales_Click()
Dim CrLf As String
Dim cTab As String
Dim cData As String
'
CrLf = Chr(13) + Chr(10) ' create CR/LF string
cTab = Chr(9) ' create tab string
'
' build three sets of data, each with four points
'
cData = "1" + cTab + "2" + cTab + "3" + cTab + "4" + CrLf
cData = cData + "5" + cTab + "4" + cTab + "3" + cTab + "2" + CrLf
cData = cData + "6" + cTab + "8" + cTab + "10" + cTab + "4" + CrLf
'
Graph1.GraphType = gphLine
Graph1.DataReset = gphAllData
Graph1.QuickData = cData
'
Graph1.DrawMode = gphDraw
'
End Sub
You'll notice that you created a list of data that contained three sets of four points each. When you use the QuickData property, the graph control is able to determine the total number of sets and the number of points in each set without using the NumSets and NumPoints properties. Save and run this project. Your screen should look like the one shown in Figure 12.6.
Adding graph data using the QuickData property.
The real advantage of using the QuickData property is that it can accept data from most spreadsheets via Windows cut and paste operations. By placing tab-delimited data on the Windows Clipboard, you can use that data as the input for the QuickData property.
NOTE: Because you will be working with data tables, the QuickData property will not be used to transfer data sets to the graph control. You can refer to the Visual Basic documentation for more information on using the Windows Clipboard and QuickData.
In addition to loading data and setting the graph type, you can also set graph titles, labels to the data points, and legends for the graph.
Now add another button to the project to illustrate these features of the graph control. Set its Name property to cmdTitles and its Caption property to &Titles. Add Listing 12.4 to the cmdTitles_Click event window:
Listing 12.4. Adding code to the cmdTitles_Click event.
Private Sub cmdTitles_Click()
Dim x As Integer
'
' add titles
'
Graph1.GraphTitle = "Graph Title"
Graph1.BottomTitle = "Bottom Title"
Graph1.LeftTitle = "Left Title"
'
' add legends
'
Graph1.AutoInc = 1 ' turn on incrementing
For x = 1 To 12
Graph1.LegendText = "L" + Trim(Str(x))
Next x
'
' add lables
'
Graph1.AutoInc = 1 ' turn it on again
For x = 1 To 12
Graph1.LabelText = "X" + Trim(Str(x))
Next x
'
Graph1.DrawMode = gphDraw ' redraw graph
'
End Sub
In Listing 12.4, you initialize the three titles and then add legends and labels for the data points. Notice that you used the AutoInc property when adding the legends and labels. Notice, too, that you did not add legends and labels within the same For...Next loop. If you use the AutoInc property, you can only update one element type at a time. When you have more than one element array to update (data, legends, and labels), you must use separate loops for each element array.
NOTE: It is very unlikely that you would use both a legend and data point labels in the same graph. You did this here to illustrate the unique behavior of the AutoInc property.
Save and run the project. You can apply the text features of the graph control to any graph. After clicking a button to produce a graph, click the Titles button to add the text to the graph. Your screen should look like the one shown in Figure 12.7.
Adding text features to a graph.
You can also send the completed graph to a file, to the Windows Clipboard, or to your printer. All those options are covered in the next section. For now, you'll add a button that writes the completed graph to a disk file as a bitmap image.
Add one more command button to the form. Set its Name property to cmdWrite and its Caption property to &Write. Add Listing 12.5 to the cmdWrite_Click event window:
Listing 12.5. Adding code to the cmsWrite_Click event.
Private Sub cmdWrite_Click()
'
Graph1.ImageFile = App.Path + "\GRAPHDES.BMP" ' set file name
Graph1.DrawMode = gphBlit ' set for bitmap mode
Graph1.DrawMode = gphWrite ' force to file
Graph1.DrawMode = gphDraw ' redraw control
'
End Sub
In Listing 12.5, you first set the name of the file that will be created. Then you set the drawing mode to bitmap. You then force the creation of the graph file and finally redraw the graph on-screen.
Save and run the project. You'll see the screen flicker when the redraw occurs. If you check your disk drive, you'll find the data file you created. You can load this file using Microsoft Paint or any other program that can read bitmap images.
Now that you have learned the basic techniques of using the graph control, you are ready to build your database graph routine library. This library consists of a single form that contains a graph control and a menu of graphing options. It also has a module file that contains a routine that loads the form, sets the graphing values using your data set, and displays the results. You'll be able to pass any valid Visual Basic recordset object to the graph routine and display any single-set, multipoint data set without any further modification of this library.
First, start a new Visual Basic project.
NOTE: Make sure the Pinnacle-BPS graph control is on your Visual Basic toolbox. If not, refer back to the "Loading the Graph Control into the Visual Basic Toolbox" section for instructions on how to add it to your project's toolbox.
Add the graph control to your form. Also add the CommonDialog control to the form. You'll use this control to add file and print capabilities to the graphing library. You'll also need to add a menu to the form. Refer to Figure 12.8 and Tables 12.1 and 12.2 as guides for laying out this form.
Figure 12.8. Laying out the graph library form.
Table 12.1. The control table for the graph library form.
| Control | Property | Setting |
|---|---|---|
| Form | Name Caption Height Left Top Width | frmGraph Graph Data 3375 2145 1710 5280 |
| Graph | Name Height Left Top Width BorderStyle | Graph1 2415 120 120 4935 1 - Fixed Single |
| CommonDialog | Name | CommonDialog1 |
Table 12.2. The Menu table for the graph library form.
| Caption | Menu |
|---|---|
| &File | mnuFile |
| —&Save... | mnuFileSave |
| —&Copy | mnuFileCopy |
| —- | mnuFileSpace0 |
| mnuFilePrint | |
| —Print Set&Up | mnuFilePrintSetup |
| —- | mnuFileSpace1 |
| —E&xit | mnuFileExit |
| &View | mnuView |
| —&Pie Chart | mnuViewPie |
| —&Bar Graph | mnuViewBar |
| —&Line Chart | mnuViewLine |
| —&Area Graph | mnuViewArea |
You need to add some code to this form. But first, save this form as FRMGRAPH.FRM and use the project name GRAPHEX.VBP. Now, add Listing 12.6 to the Form_Resize event that allows users to resize the graph by resizing the form:
Listing 12.6. Adding code to the Form_Resize event.
Private Sub Form_Resize()
Graph1.Left = Me.ScaleLeft + 60
Graph1.TOP = Me.ScaleTop + 60
Graph1.Width = Me.ScaleWidth - 120
Graph1.Height = Me.ScaleHeight - 120
End Sub
Listing 12.7 goes in the mnuFileSave_Click event. This code prompts the user for a filename and saves the current graph to that filename:
Listing 12.7. Adding code to the mnuFileSave_Click event.
Private Sub mnuFileSave_Click()
Dim cFile As String
'
' set dialog properties
'
CommonDialog1.DefaultExt = ".bmp"
CommonDialog1.DialogTitle = "Save Graph to File"
CommonDialog1.Filter = "Bitmap (*.bmp)|*.bmp"
CommonDialog1.ShowSave
cFile = CommonDialog1.filename
'
If Len(Trim(cFile)) <> 0 Then
Graph1.DrawMode = gphBlit ' set to bitmap mode
Graph1.ImageFile = cFile ' set the file name
Graph1.DrawMode = gphWrite ' write the file
Graph1.DrawMode = gphDraw ' set to draw mode
End If
End Sub
Listing 12.8 copies the current graph (as a bitmap image) to the Windows Clipboard object. You can then paste this image of the graph from the Clipboard to any other Windows program that allows image cut and paste operations (Microsoft Write, for example). Add Listing 12.8 to the mnuFileCopy_Click event:
Listing 12.8. Adding code to the mnuFileCopy_Click event.
Private Sub mnuFileCopy_Click()
Graph1.DrawMode = gphBlit ' set to bitmap mode
Graph1.DrawMode = gphCopy ' copy to clipboard
Graph1.DrawMode = gphDraw ' set to draw mode
End Sub
In order to print the graph, you'll use the PrintForm method. This method prints an exact copy of whatever is on the current form. The size of the form on-screen affects the size of the printer output. A maximized form on a 640[ts]480 resolution screen will produce a graph that covers about one-half of a standard 8-1/2 by 11 sheet of paper. Add the following code to the mnuFilePrint Click event:
Private Sub mnuFilePrint_Click()
PrintForm ' send form to default printer
End Sub
The next code line initiates the Printer Setup dialog box. Add this code to the mnuFilePrintSetup Click event window:
Private Sub mnuFilePrintSetup_Click()
CommonDialog1.ShowPrinter ' run printer setup dialog
End Sub
The last File menu item to code is the Exit item. Add this single line to the mnuFileExit Click event:
Private Sub mnuFileExit_Click()
Unload Me ' unload and exit
End Sub
Listings 12.9 through 12.12 allow users to change the GraphType property used to display the data. The first code snippet converts the on-screen display to a pie chart. Add this code to the mnuViewPie_Click event.
Listing 12.9. Adding code to the mnuViewPie_Click event
Private Sub mnuViewPie_Click()
Graph1.GraphType = gphPie3D
Graph1.DrawMode = gphDraw
End Sub
Listing 12.10 converts the display to a set of bars. Add this code to the mnuViewBar_Click event.
Listing 12.10. Adding code to the mnuViewBar_Click event.
Private Sub mnuViewBar_Click()
Graph1.GraphType = gphBar3D
Graph1.DrawMode = gphDraw
End Sub
The code snippet in Listing 12.11 converts the display to a set of lines. Add this code to the mnuViewLine_Click event.
Listing 12.11. Adding code to the mnuViewLine_Click event.
Private Sub mnuViewLine_Click()
Graph1.GraphType = gphLine
Graph1.DrawMode = gphDraw
End Sub
The code in Listing 12.12 converts the display into an area graph. Add this code to the mnuViewArea_Click event.
Listing 12.12. Adding code to the mnuViewArea_Click event.
Private Sub mnuViewArea_Click()
Graph1.GraphType = gphArea
Graph1.DrawMode = gphDraw
End Sub
That's all the code you need for the form. Save this form now. Next, you'll create the routine that calls this form.
In order to display the form you just created, you need a single routine that takes a few parameters, sets all the graph control properties, and then calls the form up on-screen. That routine will be called ShowGraph. It accepts four parameters:
This is a simple graph tool that is capable of displaying a single-set, multipoint data set in the most commonly used graph types. Modifications can be made to this routine to add additional labeling, legends, and text. You could also add options in order to graph more than one set of data per graph. For now, just keep the project simple. When you complete this project, you can add your own modifications.
First, add a module to the current project (GRAPHEX.VBP) by selecting Insert | Module from the Visual Basic main menu. Set the module name to modLibRec. Add Listing 12.13 to the module:
Listing 12.13. Creating the ShowGraph function.
Sub ShowGraph(nGphType As Integer, rsData As Recordset,
cFldPoint As String, cTitle As String)
'
' displays a graph form
'
' inputs:
' nGphType type of graph to display
' (see VB docs for valid types)
' rsData populated recordset object
' cFldSet field of recordset to use as graph set
' cTitle Graph Title
'
'
Dim nPoints As Integer
Dim x As Integer
'
rsData.MoveLast
nPoints = rsData.RecordCount
'
Load frmGraph ' load the form w/o showing it
frmGraph.Graph1.GraphType = nGphType
frmGraph.Graph1.GraphTitle = cTitle
frmGraph.Graph1.NumSets = 1
frmGraph.Graph1.NumPoints = nPoints
frmGraph.Graph1.AutoInc = 1
'
rsData.MoveFirst
For x = 1 To nPoints
frmGraph.Graph1.GraphData = rsData.Fields(cFldPoint)
rsData.MoveNext
Next x
'
frmGraph.Graph1.DrawMode = gphDraw
frmGraph.Show 1
'
End Sub
The first thing the code module in Listings 12.9 through 12.13 does is to get an accurate count of the total number of records in the recordset by using the MoveLast method to force the record pointer to the end of the set. Then the graph form is loaded without being shown, the initial graph control properties are set, and the data is loaded into the graph using the AutoInc property and a For...Next loop. Finally, the completed graph is updated and the form is shown on-screen. Save this module as LIBGRAPH.BAS.
That's all there is to it. You now have a reusable data graphing code library. In the next section, you'll test this library with a simple example.
You need to build a short program to test your new library. Suppose you have just been told that the marketing department needs a tool to display the year-to-date book sales by sales representative. The data already exists in a database, but there is no easy way to turn that data into a visual display that upper-level management can access on a regular basis. You have been asked to quickly put together a graphing front end for the sales data.
In order to complete the job, you need to open the database, create a snapshot recordset of the sales data using an SQL SELECT statement that will sum the sales by sales rep, and then pass the resultant data set to the graph library to display the graph. From there, users can select various graph styles and, if they wish, save the graph to disk, send it to the printer, or copy it to the Clipboard to paste in other documents.
Because you already have the completed graph library, you can complete your assignment with less than 15 lines of Visual Basic code.
First, if you don't have it up right now, start Visual Basic and create a new project. Add the graph control, if needed (see the "Loading the Graph Control into the Visual Basic Toolbox" section), and then add the FRMGRAPH.FRM and the LIBGRAPH.BAS files to the project using the File | Add File menu item from the Visual Basic main menu.
NOTE: Be sure to add the Pinnacle-BPS graph control and the Microsoft common dialog control to the project before you add the FRMGRAPH.FRM file to the project. If you forget to add these two controls, Visual Basic returns an error message because it cannot find the controls in the Toolbox.
Add a single button to a blank form. Set its Name property to cmdRepSales and its Caption property to &RepSales. Add Listing 12.14 behind the button:
Listing 12.14. Adding code to the cmdRepSales_Click event.
Private Sub cmdRepSales_Click()
Dim dbFile As DATABASE
Dim rsFile As Recordset
Dim cDBName As String
Dim cRSName As String
Dim nResult As Integer
'
Dim cGphField As String
Dim cGphTitle As String
'
cDBName = App.Path+"\GRAPHDAT.MDB"
cRSName = "SELECT SalesRep, SUM(Units) as UnitsSold
FROM BookSales GROUP BY SalesRep"
cGphField = "UnitsSold"
cGphTitle = "Units Sold by Sales Rep"
'
Set dbFile = OpenDatabase(cDBName) ' open the database
Set rsFile = dbFile.OpenRecordset(cRSName, dbOpenSnapshot) ' create dataset
ShowGraph gphBar3D, rsFile, cGphField, cGphTitle
End Sub
This code example opens the database, populates a recordset using an SQL SELECT...GROUP BY statement, and then calls the ShowGaph routine. That's all there is to it! Save this form as GRAPHEX.FRM and run the project. After you click the single command button, you'll see the graph displayed on-screen. Your screen should look something like the one shown in Figure 12.9.
A graph of book sales data by sales rep.
You have just completed your first database graphing project. Before you end your work on this library routine, add a few optional arguments to the ShowGraph procedure.
Visual Basic 4.0 allows you to declare optional arguments for your Sub and Function procedures, which means you don't have to pass all the values in the argument list each time you call the Sub or Function. You declare an argument optional by preceding it with the Optional keyword in the argument list. For example, to create a routine that has one required argument and one optional argument, you could write the following:
Sub MySub(cRequired as string, Optional cMaybe as variant)
Notice that the optional argument is at the end of the list. All optional arguments must appear at the end of the argument list. Also notice that the type declaration of the optional argument is Variant. Visual Basic requires that all optional arguments be declared as Variant type variables.
You can test to see whether an optional argument was passed to the procedure by using the IsMissing() function. The IsMissing function returns TRUE if the argument was not passed to the procedure. For example, to check to see whether the optional argument cMaybe was passed, you could use the following code:
If IsMissing(cMaybe) then cMaybe="Default" End if
Take advantage of this new feature by adding four optional arguments to the ShowGraph routine:
In order to add these new features, you need to modify the argument list at the start of the ShowGraph routine and then add the code to the routine to handle the four new arguments. The following is a listing of the modified ShowGraph routine. Add the new sections of code in Listing 12.15 to your existing routine.
Listing 12.15. Adding optional parameters to the ShowGraph function.
Sub ShowGraph(nGphType As Integer, rsData As Recordset, cFldPoint As String,
cTitle As String, Optional cFldLegend As Variant,
Optional cFldLabel As Variant, Optional cLeftTitle As Variant,
Optional cBottomTitle As Variant)
'
' displays a graph form
'
' inputs:
' nGphType type of graph to display
' (see VB docs for valid types)
' rsData populated recordset object
' cFldPoint field of recordset to use as graph set
' cTitle Graph Title
'
' optional args:
' cFldLegend field of recordset to use for legend set
' cFldLabel field of recordset to use for label set
' cLeftTitle title for the left of the graph
' cBottomTitle title for the bottom of the graph
'
'
On Error Resume Next ' skip any error trapping
'
Dim nPoints As Integer
Dim x As Integer
'
rsData.MoveLast
nPoints = rsData.RecordCount
'
Load frmGraph ' load the form w/o showing it
frmGraph.Graph1.GraphType = nGphType
frmGraph.Graph1.GraphTitle = cTitle
frmGraph.Graph1.NumPoints = nPoints
frmGraph.Graph1.NumSets = 1
frmGraph.Graph1.AutoInc = 1
'
' load data
'
rsData.MoveFirst
For x = 1 To nPoints
frmGraph.Graph1.GraphData = rsData.Fields(cFldPoint)
rsData.MoveNext
Next x
'
' load legends
'
If IsMissing(cFldLegend) = False Then
rsData.MoveFirst
frmGraph.Graph1.AutoInc = 1 'reset incrementing
For x = 1 To nPoints
frmGraph.Graph1.LegendText = rsData.Fields(cFldLegend)
rsData.MoveNext
Next x
End If
'
' load labels
'
If IsMissing(cFldLabel) = False Then
rsData.MoveFirst
frmGraph.Graph1.AutoInc = 1 'reset incrementing
For x = 1 To nPoints
frmGraph.Graph1.LabelText = rsData.Fields(cFldLabel)
rsData.MoveNext
Next x
End If
'
' add titles if they are passed
If IsMissing(cLeftTitle) = False Then
frmGraph.Graph1.LeftTitle = cLeftTitle
End If
'
If IsMissing(cBottomTitle) = False Then
frmGraph.Graph1.BottomTitle = cBottomTitle
End If
'
' draw it and display form
'
frmGraph.Graph1.DrawMode = gphDraw
frmGraph.Show 1
'
End Sub
Notice that you added the new arguments to the declaration line and then added sections of code that test to see whether the parameter was passed and, if the argument exists, perform the requested task. Now, modify the cmdRepSales_Click event to pass the optional labels and the left and bottom titles to the ShowGraph routine. All you need to do is modify the line that calls ShowGraph. Replace the existing line with this:
ShowGraph gphBar3D, rsFile, cGphField, cGphTitle, cFldLabel:= "SalesRep", cLeftTitle:=cGphField, cBottomTitle:="Sales Reps"
Notice that you actually told Visual Basic what optional argument you are passing (cFldLabel:="SalesRep"). This is optional as long as you are passing arguments in the same order as they are declared. In this case, because you are omitting the cFldLegend argument, you use the argument names to help Visual Basic figure out which optional parameters you want to use.
TIP: You can use named arguments for all your routines. When you use named arguments, you can pass parameters in any order. Refer to Visual Basic documentation for more information on the use of named and optional arguments.
Save and run the GRAPHEX.VBP project. When you click the SalesRep button, your screen should look like the one shown in Figure 12.10.
The results of passing optional arguments to ShowGraph.
Now that you've completed your graph library routines, you can add some graphs to the CompanyMaster project you started last week.
For the last project of the day, you'll add three graphs to the CompanyMaster project:
First you'll have to add a new menu item to the CompanyMaster form that calls the graphs. Then you need to construct SQL statements that select the desired data and feed it to the graph library form.
Adding the graph menu items is pretty easy. First, load the MASTER.VBP project from the CHAP12\MASTER subdirectory. Add the graph control to the project and then add the FRMGRAPH.FRM and LIBGRAPH.BAS files to the project.
Use Table 12.3 as a guide for adding the following menu items to the CompanyMaster menu.
Table 12.3. Added menu items for the CompanyMaster main menu.
| Caption | Menu |
|---|---|
| &Graphs | mnuGraphs |
| —Sales by &Region | mnuGraphsRegionSales |
| —Sales by &Month | mnuGraphsMonthSales |
| —Sales by &Customer | mnuGraphsCustSales |
Now you need to add code to the form to make the calls to ShowGraph. For your first graph, you want to create a pie chart showing the total year's sales by region. The fields you have to work with in the SalesData table are CompanyName, Year, Month, Amount, and Region. The database contains records for each month for each customer, along with budget values for the year. These budget records are stored with a CompanyName of Budget.
To get the total customer sales by region, you would use the following SQL SELECT statement:
SELECT Region, SUM(Amount) AS SalesTotal FROM SalesData WHERE CompanyName<>'Budget' GROUP BY Region
This is the SQL statement you'll use to generate the snapshot object that is passed to the graph library. Place Listing 12.16 in the mnuGraphsRegionSales_Click event window:
Listing 12.16. Adding the code to the mnuGraphRegionSales_Click event.
Private Sub mnuGraphsRegionSales_Click()
Dim rsFile As Recordset ' rs object
Dim dbFile As DATABASE ' db object
Dim cSQL As String ' SQL statement
Dim cField As String ' graph data field
Dim cTitle As String ' graph title
Dim cLegend As String ' graph legend field
Dim cLabel As String ' graph label field
'
' set the variables
'
cSQL = "SELECT Region, SUM(Amount) AS SalesTotal FROM
SalesData WHERE CompanyName<>'Budget' GROUP BY Region;"
cField = "SalesTotal" ' graph data
cLegend = "Region" ' graph legend
cLabel = "" ' no labels
cTitle = "Sales By Region" ' graph title
'
' set db objects
'
Set dbFile = OpenDatabase(App.Path + "\master.mdb")
Set rsFile = dbFile.OpenRecordset(cSQL, dbOpenSnapshot)
'
' run graph
'
ShowGraph gphPie3D, rsFile, cField, cTitle,
cFldLegend:=cLegend, cFldLabel:=cLabel
'
End Sub
Notice that you used the SQL statement you defined earlier to create the snapshot object. The rest of the code should be familiar by now: You set several variables that are required for the database and/or the graph library. Next, you opened the database and created a snapshot data object using the SQL statement, and then you called the graph routine. Now, save and run the project. When you select Graph | Sales by Region from the main menu, you should see a graph like the one shown in Figure 12.11.
Displaying the Sales by Region graph.
You can resize the form and the graph will resize as well. You can also use the menu on the graph to print, save, or copy the graph to the Clipboard.
TIP: It is a good idea to use snapshot data objects for all your graphs and reports. Except in instances where the size of the result set is very large, snapshot objects are the fastest data object you can use for reporting and graphing.
Add the Sales by Month graph. This time, you need a line graph that will show the total sales by month. First, you need to construct the SQL statement. It should look like the following:
SELECT Month, SUM(Amount) AS SalesTotal FROM SalesData WHERE CompanyName<>'Budget' GROUP BY Month;
Now open the mnuGraphsMonthSales_Click event and add the code in Listing 12.17
TIP: Most of the code in this routine is identical to the code in the mnuGraphsRegionSales_Click event. If you are very careful, you can paste the code from the region sales routine into the month sales routine and then make minor modifications.
Listing 12.17. Adding the code for the mnuGraphsMonthSales_Click event.
Private Sub mnuGraphsMonthSales_Click()
'
Dim rsFile As Recordset
Dim dbFile As DATABASE
Dim cSQL As String
Dim cField As String
Dim cTitle As String
Dim cLegend As String
Dim cLabel As String
'
cSQL = "SELECT Month, SUM(Amount) AS SalesTotal FROM SalesData
WHERE CompanyName<>'Budget' GROUP BY Month;"
cLegend = ""
cLabel = "Month"
cField = "SalesTotal"
cTitle = "Sales By Month"
'
Set dbFile = OpenDatabase(App.Path + "\master.mdb")
Set rsFile = dbFile.OpenRecordset(cSQL, dbOpenSnapshot)
'
ShowGraph gphLine, rsFile, cField, cTitle,
cFldLegend:=cLegend, cFldLabel:=cLabel
'
End Sub
The only real difference here is the new SQL statement and the settings for the titles, labels, and legends. Save and run this code. Check your resultant graph with the one shown in Figure 12.12.
The results of the Sales by Month graph.
Finally, add the Sales by Company bar graph to the CompanyMaster project. Here is the SQL statement you need to produce a data set that contains the year-to-date sales figures by company:
SELECT CompanyName, SUM(Amount) AS SalesTotal FROM SalesData WHERE CompanyName<>'Budget' GROUP BY CompanyName;
Now add Listing 12.18 to the mnuGraphsCustSales_Click event:
Listing 12.18. Adding code to the mnuGraphsCustSales_Click event.
Private Sub mnuGraphsCustSales_Click()
'
Dim rsFile As Recordset
Dim dbFile As DATABASE
Dim cSQL As String
Dim cField As String
Dim cTitle As String
Dim cLegend As String
Dim cLabel As String
'
cSQL = "SELECT CompanyName, SUM(Amount) AS SalesTotal FROM SalesData
WHERE CompanyName<>'Budget' GROUP BY CompanyName;"
cField = "SalesTotal"
cLegend = "CompanyName"
cLabel = "SalesTotal"
cTitle = "Sales By Company"
'
Set dbFile = OpenDatabase(App.Path + "\master.mdb")
Set rsFile = dbFile.OpenRecordset(cSQL, dbOpenSnapshot)
'
ShowGraph gphBar3D, rsFile, cField, cTitle,
cFldLegend:=cLegend, cFldLabel:=cLabel
'
End Sub
Again, the only real differences are in the SQL statement and the titles, labels, and legends. Save and run the project. Your Sales by Company graph should look like the one in Figure 12.13.
The results of the Sales by Company graph.
Today you have learned how to use the graph control that ships with Visual Basic in order to create visual displays of your data tables. You have learned how to add the control to your project and how to load the graph control with data points, titles, legends, and labels.
Also, you built a graph library that you can use to display virtually any data set in a variety of graph formats. This library lets you save the graph to disk, send the graph to the printer, or copy the graph to the Windows Clipboard for placement in other Windows programs via the Paste Special operation.
While building the graph library, you learned how to declare and use optional parameters for your Visual Basic Sub and Function procedures.
Finally, you used the new graph library to add three graphs to the CompanyMaster project.
Graph1.GraphType = graphBar3d
Graph1.GraphTitle = "Sales for October"
GphBlit gphCopy gphDraw
Assume that you are an analyst for your regional airport. The Manager of Operations wants information on passenger activity throughout the year. He is an extremely busy individual who does not understand database applications. In order to help him perform his job better, you have decided to create some graphs for him to review.
Perform the following steps in completing this project:
| Airline | Month | Passengers |
|---|---|---|
| ABC | 1 | 2562 |
| ABC | 2 | 4859 |
| ABC | 3 | 4235 |
| ABC | 4 | 4897 |
| ABC | 5 | 5623 |
| ABC | 6 | 4565 |
| ABC | 7 | 5466 |
| ABC | 8 | 2155 |
| ABC | 10 | 5454 |
| ABC | 11 | 5488 |
| ABC | 12 | 5456 |
| ABC | 9 | 5468 |
| LMN | 1 | 1956 |
| LMN | 2 | 2135 |
| LMN | 3 | 5221 |
| LMN | 4 | 2153 |
| LMN | 5 | 2154 |
| LMN | 6 | 5125 |
| LMN | 7 | 2135 |
| LMN | 8 | 5465 |
| LMN | 9 | 5555 |
| LMN | 10 | 2536 |
| LMN | 11 | 2153 |
| LMN | 12 | 2168 |
| XYZ | 1 | 10251 |
| XYZ | 2 | 12123 |
| XYZ | 3 | 10258 |
| XYZ | 4 | 12000 |
| XYZ | 5 | 21564 |
| XYZ | 6 | 21321 |
| XYZ | 7 | 14564 |
| XYZ | 8 | 12365 |
| XYZ | 9 | 21356 |
| XYZ | 10 | 21357 |
| XYZ | 11 | 21321 |
| XYZ | 12 | 12365 |
