Chapter Eleven

• Displaying Your Data with Graphs
  • The Advantages of Graphing Your Data
  • Loading and Using the Graph Control
    • Loading the Graph Control into the Visual Basic Toolbox
      • Figure 11.1.
    • Adding the Graph Control to Your Form
      • Figure 11.2.
    • Setting the Graph Type
      • Figure 11.3.
    • How the Graph Control Organizes Your Data
    • Adding Data in Design Mode
      • Figure 11.4.
    • Adding Data at Runtime
  • Listing 11.1. Adding code to the cmdSales_Click event.
    • Figure 11.5.
  • Listing 11.2. Adding code to the CmdAutoSales_Click event.
    • Adding Data Using the QuickData Property
  • Listing 11.3. Adding code to the cmdQuickSales_Click event.
    • Figure 11.6.
  • Listing 11.4. Reading a disk file and filling a graph.
    • Adding Titles, Labels, and Legends
      • Figure 11.7.
  • Listing 11.5. Adding code to the cmdTitles_Click event.
    • Figure 11.8.
  • Display Options
  • Listing 11.6. Adding code to the cmdWrite_Click event.
    • Figure 11.9.
  • Creating Your Data Graph OLE Server
    • Building the Data Graph Form
      • Figure 11.10.
  • Listing 11.7. Coding the Form_Load event.
  • Listing 11.8. Adding code to the Form_Resize event.
  • Listing 11.9. Coding the mnuFileItem_Click event.
  • Listing 11.10. Coding the mnuViewItem_Click event.
  • Listing 11.11. This code copies the graph to the Windows Clipboard.
  • Listing 11.12. Coding the GraphSave method.
    • Building the DataGraph Class Object
  • Listing 11.13. Setting up local storage for the class object.
  • Listing 11.14. Coding the Public properties of the DataGraph.
  • Listing 11.15. Coding the Class_Initialize event.
  • Listing 11.16. Coding the ShowGraph method.
  • Listing 11.17. Coding the OpenDB method.
  • Listing 11.18. Coding the InitGraph method.
  • Listing 11.19. Coding the LoadGraphData method.
    • Testing the Graph OLE Server
  • Listing 11.20. Adding code to the cmdRepSales_Click event.
    • Figure 11.11.
  • Adding More Properties to the DataGraph Object
  • Listing 11.21. Adding local storage variables for the new properties.
  • Listing 11.22. Code for the new properties.
  • Listing 11.23. Updating the Class_Initialize event.
  • Listing 11.24. Coding the AddLegends method.
  • Listing 11.25. Coding the AddLabels method.
  • Listing 11.26. Adding the left and bottom titles to the graph.
  • Listing 11.27. The updated ShowGraph method uses the new properties.
  • Listing 11.28. Updating the cmdSalesRep_Click event.
    • Figure 11.12.
  • Adding Graphs to the CompanyMaster Project
    • Adding the Graph Menu Option
      • Figure 11.13.
  • Listing 11.29. Adding the code to the mnuGraphsItem_Click event.
    • Figure 11.14.
  • Listing 11.30. Adding the code for the mnuGraphsMonthSales_Click event.
    • Figure 11.15.
  • Listing 11.31. Modifying the mnuGraphsItem_click event.
    • Figure 11.16.
  • Summary
  • Quiz
  • Exercises

Chapter Eleven

Displaying Your Data with Graphs

Today you learn how to add graph displays of your data to your database programs. By creating a simple graphing OLE Server that uses the graph control that ships with Visual Basic Professional Edition, you can easily create visual displays of your database.

You also learn how to use SQL SELECT statements for creating datasets 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 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.

Finally, when you complete this chapter you'll have a graphing OLE Server that you can use in all your future Visual Basic projects. As an example, you add a set of default graphs to the CompanyMaster project you started last week.

---

WARNING:: The OCX control used for this chapter is GRAPH32.OCX. This control ships with the professional version of Visual Basic 5, but is not installed during the normal installation process. If you have already installed Visual Basic 4, you have the GRAPH32.OCX control installed on your machine. However, if your machine only has Visual Basic 5 installed, you need to load the GRAPH32.OCX control from the Tools\Controls folder on the CD. Also, if you experience errors loading the control on your form, you need to re-register the GRAPH32.OCX control using the vbctrl.reg file that is found in the same folder. Just double-click the file to re-register your GRAPH32.OCX control.

---

The Advantages of Graphing Your Data

Although generating data graphs is not, strictly speaking, a database function, almost all good database programs provide graphing capabilities. Visual representations of data are much easier to understand than tables or lists. Providing graphs in your database programs also gives users the chance to look at data in more than one way. Often, users 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 take data created with a Visual Basic program and export it to another Windows application in order to develop graphs and charts. Using the techniques you learn today, you can provide your users with all the graphing tools they need to develop graphs and charts without using other programs!

Loading and Using the Graph Control

The graph control has a multitude of properties that you can manipulate in order to customize the graph display. Only the most commonly used options are covered here. (You can review the Visual Basic documentation for detailed information on all the properties of the graph control.) In this lesson, you learn how to use graph control properties to manipulate the way graphs appear on your forms by:

• Setting the graph type
• Adding graph data using the NumPoints, NumSets, AutoInc, GraphData, and QuickData properties
• Adding titles and legends

You also learn 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.

Loading the Graph Control into the Visual Basic Toolbox

---

NOTE: Visual Basic 5 does not install the GRAPH32.OCX control by default. If you cannot locate the GRAPH32.OCX control in your version of Visual Basic 5, you need to copy it from the Tools\Controls folder of the Visual Basic 5 installation CD. You may also need to register the control using the vbctrls.reg file found in that same folder.

---

Before you can begin to use 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 Project | Components from the Visual Basic main menu. In the list of available controls, locate Pinnacle-BPS Graph Control (see Figure 11.1). Click the checkbox to add the control to your toolbox and then click the OK button to exit the form.

Figure 11.1. Adding the graph control to your toolbox.

Adding the Graph Control to Your Form

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 does 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 preceding 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 see that the graph control automatically displays a two-dimensional bar graph with some data. Stretch the control so that your form looks like the one shown in Figure 11.2.

Figure 11.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 disappears.

Setting the Graph Type

You determine the type of graph Visual Basic displays 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 11.3.

Figure 11.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. The three most commonly used formats are covered in this lesson: bar, pie, and line graphs.

How the Graph Control Organizes Your Data

Before you can display data, you have to load it into the graph control. But, before you load it, you need to know how the graph control expects to see the data. The graph control requires that all the data be organized in sets and points. The graph control needs to know how many points of data are in each set you want to graph. Usually, you 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 dataset (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. 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.

Adding Data in Design Mode

Now add 12 data items at design time so that you can see how the graph looks. 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 as FRMGRAPHDESIGN.FRM, and save the project as GRAPHDESIGN.VBP. When you run the project, your graph should look something like the one shown in Figure 11.4.

Figure 11.4. Adding data in design mode.

Adding Data at Runtime

You can perform the same task at runtime using Visual Basic code. 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 11.1 in the cmdSales_Click event:

Listing 11.1. Adding code to the cmdSales_Click event.

In Listing 11.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 looks similar to the one shown in Figure 11.5.

Although this method works well, there is a faster method. By setting the AutoInc property of the graph control to 1, the graph control automatically increments 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 11.2 in the cmdAutoSales_Click event.

Figure 11.5. Creating a graph using Visual Basic code.

---

WARNING:: If you are using Visual Basic 5's new "code-complete" option, notice that the editor suggests the properties "gphOff" or "gphOn" as valid values for the AutoInc property. In our version of Visual Basic 5, these values returned an error. Be sure to enter a value of 1 for the AutoInc property of the GRAPH32.OCX control.

---

Listing 11.2. Adding code to the CmdAutoSales_Click event.

Save and run the project. When you press the AutoSales button, you 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 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. Not only is this approach a bit easier, it is also faster. The fewer lines of code you need to execute within the For...Next loop, the faster your program runs.

There is yet another way to add data to a graph control: by using the QuickData property.

Adding Data 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 datasets and points. Each dataset 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 code in Listing 11.3 in the cmdQuickSales_Click event window.

---

NOTE: If you're using "code-complete," you notice that the QuickData property is missing from the list of valid selections in the pop-up window. Don't let Visual Basic 5 fool you! The QuickData property is a valid property for the GRAPH32.OCX control. It's just missing from the list.

---

Listing 11.3. Adding code to the cmdQuickSales_Click event.

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

Figure 11.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 through 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 are working with data tables, you won't use the QuickData property to transfer datasets to the graph control. You can refer to the Visual Basic documentation for more information on using the Windows Clipboard and QuickData.

---

One of the handier uses of the QuickData property is the ability to read a tab-delimited text file and produce a quick graph of the data. You can use Microsoft Notepad, WordPad, or any other text editor to put tab-delimited values into a disk file and then read that file from your Visual Basic program. You can even program Microsoft Word to read data from a Word table and produce a tabbed list of numbers for input into the graph control.

To demonstrate this ability, add a new button to the form. Set its Name property to cmdLoadData and its Caption property to &Load Data. Now add the code from Listing 11.4 to the Click event of the cmdLoadData button.

Listing 11.4. Reading a disk file and filling a graph.

When you save and run the project, press the Load Data button. Your screen should look something like the one in Figure 11.7.

Notice that the only difference between the code in Listing 11.3 and Listing 11.4 is that the strData variable is loaded from the disk file in Listing 11.4.

Adding Titles, Labels, and Legends

In addition to loading data and setting the graph type, you can also set graph titles, labels for the data points, and legends for the graph.

Now add another button to the project to illustrate these features of the graph control. Set the button's Name property to cmdTitles and its Caption property to &Titles. Add Listing 11.5 to the cmdTitles_Click event window.

Figure 11.7. Loading data from a disk file.

Listing 11.5. Adding code to the cmdTitles_Click event.

In Listing 11.5, you inhìialize 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 11.8.

Figure 11.8. Adding titles, labels, and legends to a graph.

---

TIP: If your graph does not show the legends on the right, you may need to enlarge your graph display image. The graph control determines the exact placement and sizing of all titles, legends, and labels.

---

Display Options

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 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 11.6 to the cmdWrite_Click event window.

Listing 11.6. Adding code to the cmdWrite_Click event.

In Listing 11.6, you first set the name of the file to 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 see the screen flicker during the redrawing. If you check your disk drive, you find the data file you created. You can load this file using Microsoft Paint or any other program that can read bitmap images. Figure 11.9 shows both the Visual Basic 5 form and Microsoft Paint running at the same time.

Figure 11.9. Displaying the graph in both Visual Basic and Microsoft Paint.

Creating Your Data Graph OLE Server

Now that you have learned the basic techniques of using the graph control, you are ready to build your database graph OLE Server. This OLE Server consists of a single form that contains a graph control and a menu of graphing options. It also has methods that load the form, set the graphing values using your dataset, and display the results. You can pass any valid Visual Basic Recordset object to the data graph OLE Server and display any single-set, multipoint dataset without any further modification of the code.

Building the Data Graph Form

First, start a new ActiveX DLL Visual Basic project. Set the name of the project to prjDataGraph and the name of the default class module to DataGraph. Add a form to the project and set its Name property to frmGraph. You also need to make sure you load the Pinnacle-BPS graph control (GRAPH32.OCX) and the Microsoft common dialog control to the project.

---

NOTE: Make sure the Pinnacle-BPS graph control is in 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 use this control to add file and print capabilities to the graphing library. You also need to add a menu to the form. Refer to Figure 11.10 and Tables 11.1 and 11.2 as guides for laying out this form.

Figure 11.10. Laying out the graph library form.


Table 11.1. The control table for the graph library form.

Table 11.2. The menu table for the graph library form.

Note that you are building menu arrays with the form. Menu arrays, like other control arrays, can speed the processing of your program and simplify the coding and maintenance of your forms.

You need to add some code to this form. But first, save it (FRMGRAPH.FRM) and save the project (PRJDATAGRAPH.VBP). Then add the code in Listing 11.7 to the Form_Load event of the project. This sets the default size of the form to fill 75% of the screen.

Listing 11.7. Coding the Form_Load event.

Private Sub Form_Load()
    `
    ` set initial form size
    `
    If Me.WindowState = vbNormal Then
        Me.Width = Screen.Width * 0.75
        Me.Height = Screen.Height * 0.75
    End If
    `
End Sub 

Now, add the code from Listing 11.8 to the Form_Resize event. This code allows users to resize the graph by resizing the form.

Listing 11.8. Adding code to the Form_Resize event.

Private Sub Form_Resize()
    `
    ` make graph fill the form
    `
    Graph1.Left = 1
    Graph1.Top = 1
    Graph1.Width = Me.ScaleWidth
    Graph1.Height = Me.ScaleHeight
    `
End Sub 

The code in Listing 11.9 goes in the mnuFileItem_Click event. This code is executed each time the user selects one of the File menu items. The Index value returns the item number selected by the user. You code the CopyGraph and SaveGraph methods in just a moment.

Listing 11.9. Coding the mnuFileItem_Click event.

Private Sub mnuFileItem_Click(Index As Integer)
    `
    ` handle file menu selections
    `
    Select Case Index
        Case 0 ` save
            GraphSave
        Case 1 ` copy
            GraphCopy
        Case 2 ` separator
            ` no action
        Case 3 ` print
            Me.PrintForm
        Case 4 ` printer setup
            CommonDialog1.ShowPrinter
        Case 5 ` separator
            ` no action
        Case 6 ` exit
            Unload Me
    End Select
    `
End Sub 

Now add the code in Listing 11.10 to the mnuViewItem_Click event. This allows users to select the type of graph they view.

Listing 11.10. Coding the mnuViewItem_Click event.

Private Sub mnuViewItem_Click(Index As Integer)
    `
    ` handle view selections
    `
    Dim intGraphType As Integer
    `
    Select Case Index
        Case 0 ` pie chart
            intGraphType = gphPie3D
        Case 1 ` bar graph
            intGraphType = gphBar3D
        Case 2 ` line chart
            intGraphType = gphLine
        Case 3 ` area graph
            intGraphType = gphArea
    End Select
    `
    Graph1.GraphType = intGraphType
    Graph1.DrawMode = gphDraw
    `
End Sub

Now you're ready to code the GraphCopy method. The code in Listing 11.11 is all you need to copy the graph image to the Windows Clipboard. 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).

Listing 11.11. This code copies the graph to the Windows Clipboard.

Next, add the GraphSave method code from Listing 11.12 to the project. This code prompts the user for a filename and saves the current graph under that filename.

Listing 11.12. Coding the GraphSave method.

That's all the code you need for the form. Save this form now. Next, you create the routine that calls this form.

Building the DataGraph Class Object

In order to display the form you just created, you need to create a class object that allows users to set some properties and executes a ShowGraph method. The basic properties of the DataGraph object are

• GraphType: The initial graph type for the display.
• DatabaseName: The official name of the database that contains the records to graph.
• SQLSelect: The SQL SELECT statement that creates the Recordset to display.
• GraphField: The name of the column in the Recordset that contains the values you load into the GraphData property of the graph control.
• GraphTitle: The title of the graph.

This is a simple graph tool that is capable of displaying a single-set, multipoint dataset 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, you need to add some local storage variables to the class. These contain the passed properties along with a couple of local variables needed for Private methods. Add the code from Listing 11.13 to the class.

Listing 11.13. Setting up local storage for the class object.

Notice the use of the Enum..End Enum construct in the declarations section. This is a special type of constant declaration that combines a user-defined type (dgType) with a set of predefined values (dgPie3D, dgBar3D, dgLine, dgArea). When you use this class object in your programs, you can see the enumerated types in the code-complete windows that appear as you enter the source code.

Now you need to declare Public properties of the class. These properties allow users to manipulate the local storage variables of the class. Note that all five properties are included in Listing 11.14. You need to add each property individually using the Tools | Add Procedure | Property options from the main menu.

Listing 11.14. Coding the Public properties of the DataGraph.

Notice some error-checking code in the GraphType property Let method. This checks for a valid graph type and reports an error if a valid value is not found.

Now add the code from Listing 11.15 to the Class_Initialize event. This sets the default values for the properties.

Listing 11.15. Coding the Class_Initialize event.

Private Sub Class_Initialize()
    `
    ` set startup values
    `
    strDBName = ""
    strSQLSelect = ""
    strFieldPoint = ""
    strTitle = "Data Graph"
    intGraphType = gphBar3D
    `
End Sub 

Now you're ready to code the ShowGraph method. This one method collects all the property values, creates a dataset, and builds a graph based on the data. Create a Public Sub called ShowGraph in the class and add the code from Listing 11.16.

Listing 11.16. Coding the ShowGraph method.

This method calls three other Private methods. Create the Private Sub OpenDB and add the code from Listing 11.17.

Listing 11.17. Coding the OpenDB method.

Next, add the Private Sub InitGraph and enter the code from Listing 11.18. This code loads the frmGraph form and sets the initial values of the display.

Listing 11.18. Coding the InitGraph method.

Finally, add the Private Sub LoadGraphData to the class. This is the code that moves the data from the Recordset into the graph control (see Listing 11.19).

Listing 11.19. Coding the LoadGraphData method.

That's all there is to it. You now have a reusable data graphing OLE Server. All you need to do now is save the project and compile the DLL. You can do this by selecting File | Make prjDataGraph.DLL from the main menu. In the next section, you test this library with a simple example.

Testing the Graph OLE Server

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 guide 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 initialize a copy of the DataGraph object; set the DatabaseName and SQLSelect properties; set the GraphType, GraphField, and GraphTitle properties; and then execute the ShowGraph method. 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 as few as 10 lines of Visual Basic code.

First, if you don't have it running right now, start Visual Basic and create a new Standard EXE project. If you still have Visual Basic up from the last section of this chapter, select File | Add Project... to add a new Standard EXE project. Make this new project the Default startup project, too.

Now, select Project | References and locate and select the prjDataGraph library. This links your new project with the OLE Server DLL that you built earlier in this chapter.

Add a single button to a blank form. Set its Name property to cmdRepSales and its Caption property to &RepSales. Add the code from Listing 11.20 to support the button.

Listing 11.20. Adding code to the cmdRepSales_Click event.

This code example sets a few properties and then calls the ShowGraph method. That's all there is to it! Save this form as FRMTEST.FRM and the project as PRJTEST.VBP. Now run the project. After you click the single command button, you see the graph displayed on-screen. Your screen should look something like the one shown in Figure 11.11.

You have just completed your first database graphing project using the new OLE Server! Before you end your work on this OLE Server, let's add a few new properties to the class object. These give you (and our users) greater control over the graph display.

Figure 11.11. A graph of guide sales data by sales rep.

Adding More Properties to the DataGraph Object

Now that the basic DataGraph object is working, let's add some of the bells and whistles that make this a versatile programming tool. Let's add four new properties to the class:

• LegendField: This is the Recordset column to use for setting the legends of the graph.
• LabelField: This is the Recordset column to use for setting the labels of the graph.
• LeftTitle: This is the string to display as the title on the left side of the graph.
• BottomTitle: This is the string to display as the title at the bottom of the graph.

In order to add these new features, all you need to do is add the property definitions to the DataGraph object and then add code to the ShowGraph method to incorporate these new properties into the displayed graph.

The code in Listing 11.21 shows the changes you need to make to the general declaration section of the DataGraph class. The four new variables are added at the end of the section.

Listing 11.21. Adding local storage variables for the new properties.

The next step is to use the Tools | Add Procedure | Property options from the main menu to add the four new property declarations to the class. Listing 11.22 shows the code for each of these new properties.

Listing 11.22. Code for the new properties.

Because just about any string value could be valid for these properties, no error checking is done at the time the properties are set. In a production application, you might want to add error-checking to protect users.

After adding the new properties, you need to update the Class_Initialize event to set the new properties at the start of the object. Listing 11.23 shows the code you need to add to the Class_Initialize event.

Listing 11.23. Updating the Class_Initialize event.

Private Sub Class_Initialize()
    `
    ` set startup values
    `
    strDBName = ""
    strSQLSelect = ""
    strFieldPoint = ""
    strTitle = "Data Graph"
    intGraphType = dgBar3D
    `
    ` initialize added properties
    `
    strLegendField = ""
    strLabelField = ""
    strBottomTitle = ""
    strLeftTitle = ""
    `
End Sub 

Now you need to build routines to add the legends and labels to the graph. Create a Private Sub method called AddLegends and enter the code from Listing 11.24.

Listing 11.24. Coding the AddLegends method.

Notice the first line of code in Listing 11.24. The line checks to see whether the strLegendField variable contains any printable data. If not, the Exit Sub is executed. No reason to set the legends if no LegendField has been set!

Now add the Private Sub called AddLabels to your class. The code in Listing 11.25 should be added to this new method.

Listing 11.25. Coding the AddLabels method.

Next, you need to add the Private Sub called AddTitles to the class. This sets the left and bottom title properties of the graph. Listing 11.26 contains the code needed for this method. Note that the variables strLeftTitle and strBottomTitle are checked for valid data before the graph properties are actually set.

Listing 11.26. Adding the left and bottom titles to the graph.

Finally, it's time to modify the ShowGraph method to incorporate the new methods into your graph display. Listing 11.27 shows the updated ShowGraph method.

Listing 11.27. The updated ShowGraph method uses the new properties.

Now all you need to do is modify the cmdSalesRep_Click event of the frmTest form to test out these new properties. Switch to the prjTest project and add the code from Listing 11.28 to the cmdSalesRep_Click event.

Listing 11.28. Updating the cmdSalesRep_Click event.

Save and run the PRJTEST.VBP project. When you click the SalesRep button, your screen should look like the one shown in Figure 11.12.

Figure 11.12. Viewing the updated graph object display.

As soon as you confirm that the modifications to the DataGraph object work without errors, recompile the ActiveX DLL. You can use this OLE Server DLL in all your future VBA projects. As an example, you can now add some graphs to the CompanyMaster project you started last week.

Adding Graphs to the CompanyMaster Project

For the last project of the day, you add three graphs to the CompanyMaster project:

• A pie graph showing the actual year-to-date sales totals by region
• A line graph showing the year-to-date sales totals by month
• A bar graph showing the sales by customer

First you 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 object.

Adding the Graph Menu Option

Adding the graph menu items is pretty easy. First, load the MASTER.VBP project from the CHAP11\COMASTER directory. Add the graph object to the project by selecting Project | References and locating and adding the Databound Graph object library (see Figure 11.13).

Figure 11.13. Adding the Databound Graph object library.

Use Table 11.3 as a guide for adding the following menu items to the CompanyMaster menu.

Table 11.3. Added menu items for the CompanyMaster main menu.

Now you need to add code to the form to make the calls to the DataGraph object. 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 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 use to generate the Snapshot object that is passed to the graph library. Place Listing 11.29 in the mnuGraphsItem Click event.

Listing 11.29. Adding the code to the mnuGraphsItem_Click event.

Notice that you used the SQL statement you defined earlier as the SQLSelect property of the DataGraph object. The rest of the code should be familiar by now: You set several properties that are required for the graph object library and then you called the ShowGraph method.

---

NOTE: It is important to keep in mind that you did not have to load the GRAPH32.OCX file into your project. Because this is part of the ActiveX DLL, you do not need to identify it in the Visual Basic project that uses the DLL. The OCX, and its supporting files, must be present on your machine, but you do not have to add it to your project.

---

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

Figure 11.14. Displaying the Sales by Region pie graph.

You can resize the form and the graph resizes too. You can also use the menu on the graph to print, save, or copy the graph to the Clipboard.

Now add the Sales by Month graph to the project. This time, you want a line graph that shows 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 mnuGraphsItem_Click event and add the code in Listing 11.30.

Listing 11.30. Adding the code for the mnuGraphsMonthSales_Click event.

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 Fig-ure 11.15.

Figure 11.15. Displaying the Sales by Month line graph.

Finally, add the Sales by Company bar graph to the CompanyMaster project. Here is the SQL statement you need to produce a dataset 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 modify the mnuGraphsItem_Click event to match the code in Listing 11.31.

Listing 11.31. Modifying the mnuGraphsItem_click event.

Again, the only real difference is 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 11.16.

Figure 11.16. Displaying the Sales by Company bar graph.

Summary

Today you learned how to use the graph control that ships with Visual Basic in order to create visual displays of your data tables. You 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 ActiveX DLL Object Library that you can use to display virtually any dataset 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 by way of the Paste Special operation.

While building the graph library, you learned how to declare and use enumerated constants to improve the readability of your Visual Basic code.

Finally, you used the new graph library to add three graphs to the CompanyMaster project.

Quiz

1. List the advantages of including graphics in your Visual Basic database applications.

2. Describe the purpose of the NumSets and NumPoints properties of the graph control.

3. When you are using the predefined constants for graph types, is the following code correct?

Graph1.GraphType = graphBar3d

4. What character separates data points in a series when the QuickData property is used? What character(s) separate a set of points?

5. Is the following code correct?

Graph1.GraphTitle = "Sales for October"

6. What do the following DrawModes constants do?

gphBlit
gphCopy
gphDraw

7. What is an enumerated type declaration and why is it useful?

8. Write code to get a count of records in a dataset used for graphing.

Exercises

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:

1. Build a database using Visdata or Data Manager. Name this database 12ABCEX.MDB.

2. Build a table in this database and name it Activity. Include three fields: Airline (TEXT 10), Month (INTEGER), and Passengers (INTEGER).

3. Insert the following records into your table:

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

4. Start a new Visual Basic project that uses the LIBGRAPH.BAS module you created today. Build a form and add three command buttons: cmdPie, cmdLine, and cmdBar.

5. Display the following graphs when each button is pressed:
cmdPie: Displays a 3D pie chart that shows comparative activity for the first month.
cmdLine: Displays a line graph that shows total passenger activity by month. Include Passengers as the title on the vertical axis and Month as the title for the horizontal axis.
cmdBar: Displays a 3D bar graph for the activity of ABC Airlines for the entire year.

6. Examine the charts you built. Notice how much easier it is to ascertain trends from these graphs than it is from the data entry table in Exercise 3.