![]() |
|||
![]()
|
![]() |
![]() |
![]() |
Day 11Displaying Your Data with GraphsToday 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.
The Advantages of Graphing Your DataAlthough 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 ControlThe 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:
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
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.
|
Control | Property | Setting |
Form | Name | frmGraph |
Caption | Graph Data | |
Height | 3375 | |
Left | 2145 | |
Top | 1710 | |
Width | 5280 | |
Graph | Name | Graph1 |
Height | 2415 | |
Left | 120 | |
Top | 120 | |
Width | 4935 | |
BorderStyle | 1-Fixed Single | |
CommonDialog | Name | CommonDialog1 |
Caption | Menu |
&File | mnuFile |
&Save | mnuFileItem(0) |
&Copy | mnuFileItem(1) |
- | mnuFileItem(2) |
mnuFileItem(3) | |
Print Set&Up | mnuFileItem(4) |
- | mnuFileItem(5) |
E&xit | mnuFileItem(6) |
&View | mnuView |
&Pie Chart | mnuViewItem(0) |
&Bar Graph | mnuViewItem(1) |
&Line Chart | mnuViewItem(2) |
&Area Graph | mnuViewItem(3) |
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.
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.
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.
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.
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).
Public Sub GraphCopy() ` ` copy graph to clipboard ` Graph1.DrawMode = gphBlit Graph1.DrawMode = gphCopy Graph1.DrawMode = gphDraw ` MsgBox "Graph has been copied to the clipboard", _ vbInformation, _ "Copy Graph" ` End Sub
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.
Public Sub GraphSave() ` ` save graph to disk file ` Dim strFileName As String ` CommonDialog1.DefaultExt = ".bmp" CommonDialog1.DialogTitle = "Save Graph" CommonDialog1.Filter = "Bitmap Files | *.bmp" CommonDialog1.ShowSave strFileName = CommonDialog1.filename ` If Trim(strFileName) <> "" Then Graph1.DrawMode = gphBlit Graph1.ImageFile = strFileName Graph1.DrawMode = gphWrite Graph1.DrawMode = gphDraw End If ` End Sub
That's all the code you need for the form. Save this form now. Next, you create the
routine that calls this form.
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
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.
Option Explicit ` ` enumerated graph types ` Enum dgType dgPie3d = gphPie3D dgBar3d = gphBar3D dgLine = gphLine dgArea = gphArea End Enum ` ` local property storage ` Private intGraphType As Integer Private strDBName As String Private strSQLSelect As String Private strFieldPoint As String Private strTitle As String ` ` for internal use only Private ws As Workspace Private db As Database Private rs As Recordset Private lngNumPoints As Long Private lngLoop As Long `
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.
Public Property Get GraphType() As Integer GraphType = intGraphType End Property Public Property Let GraphType(ByVal vNewValue As Integer) If IsNumeric(vNewValue) Then intGraphType = Int(vNewValue) End If ` If intGraphType < 1 Or intGraphType > 11 Then Err.Raise 380 ` invalid property intGraphType = 0 End If ` End Property Public Property Get DatabaseName() As String DatabaseName = strDBName End Property Public Property Let DatabaseName(ByVal vNewValue As String) strDBName = vNewValue End Property Public Property Get SQLSelect() As String SQLSelect = strSQLSelect End Property Public Property Let SQLSelect(ByVal vNewValue As String) strSQLSelect = vNewValue End Property Public Property Get GraphField() As String GraphField = strFieldPoint End Property Public Property Let GraphField(ByVal vNewValue As String) strFieldPoint = vNewValue End Property Public Property Get GraphTitle() As String GraphTitle = strTitle End Property Public Property Let GraphTitle(ByVal vNewValue As String) strTitle = vNewValue End Property
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.
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.
Public Sub ShowGraph() ` ` display graph ` On Error GoTo LocalErr ` Screen.MousePointer = vbHourglass OpenDB InitGraph LoadGraphData Screen.MousePointer = vbNormal ` frmGraph.Graph1.DrawMode = gphDraw frmGraph.Show vbModal ` Exit Sub ` LocalErr: Err.Raise vbObjectError + 4, App.EXEName, "Error displaying graph" ` End Sub
This method calls three other Private methods. Create the Private Sub OpenDB
and add the code from Listing 11.17.
Private Sub OpenDB() ` ` open database/recordset ` On Error GoTo LocalErr ` Set ws = dbengine.Workspaces(0) Set db = ws.OpenDatabase(strDBName) Set rs = db.OpenRecordset(strSQLSelect, dbOpenSnapshot) ` Exit Sub ` LocalErr: Err.Raise vbObjectError + 1, App.EXEName, "Error creating data set" ` End Sub
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.
Private Sub InitGraph() ` ` initialize the graph form ` On Error GoTo LocalErr ` rs.MoveLast lngNumPoints = rs.RecordCount ` Load frmGraph frmGraph.Graph1.GraphType = intGraphType frmGraph.Graph1.GraphTitle = strTitle frmGraph.Graph1.NumSets = 1 frmGraph.Graph1.NumPoints = lngNumPoints frmGraph.Graph1.AutoInc = 1 ` Exit Sub ` LocalErr: Err.Raise vbObjectError + 2, App.EXEName, "Error intializing graph form" ` End Sub
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).
Private Sub LoadGraphData() ` ` fill graph with data ` On Error GoTo LocalErr ` rs.MoveFirst For lngLoop = 1 To lngNumPoints frmGraph.Graph1.GraphData = rs.Fields(strFieldPoint) rs.MoveNext Next ` Exit Sub ` LocalErr: Err.Raise vbObjectError + 3, App.EXEName, "Error loading graph data" ` End Sub
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.
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 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.
Private Sub cmdRepSales_Click() ` ` test graph libaray ` Dim objDG As Object ` Set objDG = New DataGraph ` objDG.DatabaseName = App.Path & "\..\..\data\books5.mdb" objDG.sqlselect = "SELECT SalesRep, SUM(Units) AS UnitsSold FROM BookSales GROUP BY SalesRep" objDG.graphfield = "UnitsSold" objDG.graphTitle = "Units Sold by Sales Rep" ` objDG.showgraph ` End Sub
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 book sales data by sales
rep.
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:
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.
Option Explicit ` ` enumerated graph types ` Enum dgType dgPie3d = gphPie3D dgBar3d = gphBar3D dgLine = gphLine dgArea = gphArea End Enum ` ` local property storage ` Private intGraphType As Integer Private strDBName As String Private strSQLSelect As String Private strFieldPoint As String Private strTitle As String ` ` for internal use only Private ws As Workspace Private db As Database Private rs As Recordset Private lngNumPoints As Long Private lngLoop As Long ` ` added properties Private strLegendField As String Private strLabelField As String Private strLeftTitle As String Private strBottomTitle As String `
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.
Public Property Get LegendField() As Variant LegendField = strLegendField End Property Public Property Let LegendField(ByVal vNewValue As Variant) strLegendField = vNewValue End Property Public Property Get LabelField() As Variant LabelField = strLabelField End Property Public Property Let LabelField(ByVal vNewValue As Variant) strLabelField = vNewValue End Property Public Property Get LeftTitle() As Variant LeftTitle = strLeftTitle End Property Public Property Let LeftTitle(ByVal vNewValue As Variant) strLeftTitle = vNewValue End Property Public Property Get BottomTitle() As Variant BottomTitle = strBottomTitle End Property Public Property Let BottomTitle(ByVal vNewValue As Variant) strBottomTitle = vNewValue End Property
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.
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.
Private Sub AddLegends() ` ` add legends to the existing graph ` If Trim(strLegendField) = "" Then Exit Sub ` frmGraph.Graph1.AutoInc = 1 ` rs.MoveFirst For lngLoop = 1 To lngNumPoints frmGraph.Graph1.LegendText = rs.Fields(strLegendField) rs.MoveNext Next ` End Sub
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.
Private Sub AddLabels() ` ` add labels to the existing graph ` If Trim(strLabelField) = "" Then Exit Sub ` frmGraph.Graph1.AutoInc = 1 ` rs.MoveFirst For lngLoop = 1 To lngNumPoints frmGraph.Graph1.LabelText = rs.Fields(strLabelField) rs.MoveNext Next ` End Sub
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.
Private Sub AddTitles() ` ` add left and bottom titles ` If Trim(strLeftTitle) <> "" Then frmGraph.Graph1.LeftTitle = strLeftTitle End If ` If Trim(strBottomTitle) <> "" Then frmGraph.Graph1.BottomTitle = strBottomTitle End If ` End Sub
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.
Public Sub ShowGraph() ` ` display graph ` On Error GoTo LocalErr ` Screen.MousePointer = vbHourglass OpenDB InitGraph LoadGraphData ` ` added property handling AddLegends AddLabels AddTitles ` Screen.MousePointer = vbNormal ` frmGraph.Graph1.DrawMode = gphDraw frmGraph.Show vbModal ` Exit Sub ` LocalErr: Err.Raise vbObjectError + 4, App.EXEName, "Error displaying graph" ` End Sub
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.
Private Sub cmdRepSales_Click() ` ` test graph libaray ` Dim objDG As Object ` Set objDG = New DataGraph ` objDG.DatabaseName = App.Path & "\..\..\data\books5.mdb" objDG.sqlselect = "SELECT SalesRep, SUM(Units) AS UnitsSold FROM BookSales GROUP BY SalesRep" objDG.graphfield = "UnitsSold" objDG.graphTitle = "Units Sold by Sales Rep" ` ` added properties objDG.labelfield = "SalesRep" objDG.lefttitle = "Units Sold" objDG.bottomtitle = "Sales Reps" objDG.legendfield = "SalesRep" ` objDG.showgraph ` End Sub
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.
For the last project of the day, you add three graphs to the CompanyMaster project:
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 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.
Caption | Menu |
&Graphs | mnuGraphs |
Sales by &Region | mnuGraphsItem(0) |
Sales by &Month | mnuGraphsItem(1) |
Sales by &Customer | mnuGraphsItem(2) |
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.
Private Sub mnuGraphsItem_Click(Index As Integer) ` ` handle graph menu requests ` Dim objDG As New DataGraph Dim strSQL As String ` Select Case Index Case 0 ` sales by region ` strSQL = "SELECT Region, SUM(Amount) AS SalesTotal FROM " strSQL = strSQL & "SalesData WHERE CompanyName<>'Budget' GROUP BY ÂRegion" ` objDG.DatabaseName = Data1.DatabaseName objDG.SQLSelect = strSQL objDG.GraphType = dgPie3d objDG.GraphField = "SalesTotal" objDG.GraphTitle = "Sales by Region" objDG.LegendField = "Region" ` Case 1 ` sales by month Case 2 ` sales by customer End Select ` objDG.ShowGraph ` End Sub
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.
Private Sub mnuGraphsItem_Click(Index As Integer) ` ` handle graph menu requests ` Dim objDG As New DataGraph Dim strSQL As String ` Select Case Index Case 0 ` sales by region ` strSQL = "SELECT Region, SUM(Amount) AS SalesTotal FROM " strSQL = strSQL & "SalesData WHERE CompanyName<>'Budget' GROUP BY ÂRegion" ` objDG.DatabaseName = Data1.DatabaseName objDG.SQLSelect = strSQL objDG.GraphType = dgPie3d objDG.GraphField = "SalesTotal" objDG.GraphTitle = "Sales by Region" objDG.LegendField = "Region" ` Case 1 ` sales by month ` strSQL = "SELECT Month, SUM(Amount) AS SalesTotal FROM " strSQL = strSQL & "SalesData WHERE CompanyName<>'Budget' GROUP BY ÂMonth" ` objDG.DatabaseName = Data1.DatabaseName objDG.SQLSelect = strSQL objDG.GraphType = dgLine objDG.GraphField = "SalesTotal" objDG.LabelField = "Month" objDG.GraphTitle = "Sales by Month" objDG.LeftTitle = "($)" objDG.BottomTitle = "Months" ` Case 2 ` sales by customer End Select ` objDG.ShowGraph ` 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 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.
Private Sub mnuGraphsItem_Click(Index As Integer) ` ` handle graph menu requests ` Dim objDG As New DataGraph Dim strSQL As String ` Select Case Index Case 0 ` sales by region ` strSQL = "SELECT Region, SUM(Amount) AS SalesTotal FROM " strSQL = strSQL & "SalesData WHERE CompanyName<>'Budget' GROUP BY ÂRegion" ` objDG.DatabaseName = Data1.DatabaseName objDG.SQLSelect = strSQL objDG.GraphType = dgPie3d objDG.GraphField = "SalesTotal" objDG.GraphTitle = "Sales by Region" objDG.LegendField = "Region" ` Case 1 ` sales by month ` strSQL = "SELECT Month, SUM(Amount) AS SalesTotal FROM " strSQL = strSQL & "SalesData WHERE CompanyName<>'Budget' GROUP BY ÂMonth" ` objDG.DatabaseName = Data1.DatabaseName objDG.SQLSelect = strSQL objDG.GraphType = dgLine objDG.GraphField = "SalesTotal" objDG.LabelField = "Month" objDG.GraphTitle = "Sales by Month" objDG.LeftTitle = "($)" objDG.BottomTitle = "Months" ` Case 2 ` sales by customer ` strSQL = "SELECT CompanyName, SUM(Amount) AS SalesTotal FROM " strSQL = strSQL & "SalesData WHERE CompanyName<>'Budget' GROUP BY ÂCompanyName" ` objDG.DatabaseName = Data1.DatabaseName objDG.SQLSelect = strSQL objDG.GraphType = dgBar3d objDG.GraphField = "SalesTotal" objDG.LegendField = "CompanyName" objDG.LabelField = "SalesTotal" objDG.GraphTitle = "Sales by Company" objDG.LeftTitle = "($)" objDG.BottomTitle = "Companies" ` End Select ` objDG.ShowGraph ` End Sub
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.
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.
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 |
![]() |
|
Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details. |