It's a safe bet that the majority of the decision-support applications you create with Visual Basic will include at least one graph or chart. The decision-support applications you develop for upper management's review and for planning purposes are
likely to consist wholly of a standard repertoire of graphs, plus drill-down features to display the detailed data behind the graph. Chapter 8, "Designing a Decision-Support Front-End," gives you an introduction to using the graph custom control
supplied with the Professional Edition of Visual Basic 4.0 to create graphs based on data summarized by crosstab queries. This chapter expands on the examples of Chapter 8 by showing you how to organize a graph- and chart-based decision-support
application, how to use the multiple document interface with graphs and charts, and how to provide the navigational tools your clients need for drill-down applications. Chapter 12, "Extending Your Use of the Data Access Object," expands on the
examples created in this chapter.
One of the major tasks that faces developers of decision-support applications for management is organizing the hierarchical structure of the display. A typical decision-support application, designed for executive management, is likely to comprise at
least the six sets of graphs shown in Figure 10.1. The collection of graphs and charts that are used in the majority of decision-support applications is quite similar, regardless of the size of the firm for which you're developing the application.
In addition to the 21 graphs and charts shown in Figure 10.1, you need to provide overlay graphs that compare the current year's performance with that of prior years. Performance comparisons are a vital component of all decision-support applications.
The following list describes the chart types for each of the chart sets shown in Figure 10.1:
The sections that follow describe additional issues that arise when you create a major decision-support application.
Chapter 8 discusses how the level of information is stratified by management level. Creating separate applications to provide information at different management levels is an inefficient process. In such cases, you have two or three applications to
support and maintain, instead of one master application. Many "hands-on" executives want to be able to display data that is ordinarily the province of operational management. The better approach is to include summary data that is accessible by
executives but not by operational management. You can determine who can display which graphs by requiring that users log in to the application with a user ID and password that matches entries in a users table. Permissions are incorporated in fields of the
users table. Login methods are discussed later in this chapter.
Whereas executive management sees the summary data for the corporation as a whole, operational managers are limited to viewing graphs that are related to their particular regimes. As an example, regional sales managers can display sales, margins,
orders, backlogs, and order cycle times for orders originating in their particular region. Similarly, product line managers can view data that relates to the single product category they manage. Table 10.1 lists the typical sets of graphs and charts in a
multilevel decision-support application.
| Graph or Chart and Sequence | Executive Management | Regional Sales Management | Product Line Management |
| Sales 1 | Total | Regional totals | Category total |
| Sales 2 | Category | Product in region | Product in category |
| Sales 3 | Segment | Customer in region | Segment in category |
| Sales 4 | Region | Employee in region | |
| Margin 1 | Average | Regional average | Category average |
| Margin 2 | Category | Product in region | Product in category |
| Margin 3 | Segment | Customer in region | Segment in category |
| Margin 4 | Region | Employee in region | |
| Orders 1 | Total | Regional totals | Category total |
| Orders 2 | Category | Product in region | Product in category |
| Orders 3 | Segment | Customer in region | Segment in category |
| Orders 4 | Region | Employee in region | |
| Backlog 1 | Total | Regional totals | Category |
| Backlog 2 | Segment | Customer in region | Product in category |
| Order Cycle 1 | Average | Regional average | |
| Order Cycle 2 | Segment | Customer averages | |
| Order Cycle 3 | Histogram | Histogram by | Histogram by |
| region | product | ||
| Inventory 1 | Total | Category | |
| Inventory 2 | Category | Product | |
| Inventory 3 | Turns | Product Cost | History |
It is clear from Table 10.1 that a decision-support application for a large firm easily can require 100 different graphs. When you add the capability to display historical data, the permutations and combinations of graphs range in the
hundreds or even the thousands. Completing a table of the graphs and charts that constitutes the decision-support application, as well as completing the tables that display the numerical values behind the charts, is the first major milestone in the
organization process. You obtain approval of your table of graphs and charts from the client and then begin to build the backbone of your application.
Clients who commission data-support applications are notorious for being fickle in the extreme. You are likely to find that your client will make ad hoc changes to your graph and chart table in the midst of the development process. If you
hard-code table hierarchies and definitions, you are in big trouble. A more flexible approach is to create a graph-and-chart definitions table whose fields provide most or all of the parameters required to display a chart. You can add, delete, or
change the access rights to charts simply by updating records in the table.
The term commission is used in the first sentence of the preceding paragraph because designing decision-support applications is an art, not a science. A well-designed decision-support application paints a portrait of the corporation in graphs and charts.
Table 10.2 lists some of the fields that constitute the graph-and-chart definitions table, tblDefinitions, for the sample decision-support application of this chapter. All the fields listed in Table 10.2 are used in the code examples that follow,
except GraphQuery1 and GraphQuery2. Code is used to generate all the SQL statements required to create Recordset objects to supply data for the graphs.
| Field | Data Type | Description |
| Code | Text | Two-character numeric code for the graph |
| Category | Text | The category of the graph (Sales, Margins, Orders, Backlog, and so on) |
| Level | Integer | The level of the graph in the hierarchy (1 = top, 2+ = drill-down) |
| GraphCaption | Text | The caption for the MDI form title bar |
| Units | Text | Units ($, %, Days, and so on, for the graph) |
| Type | Text | Type of graph (line, multiline, and so on) |
| GraphType | Integer | The GraphType value of the graph control |
| GraphStyle | Double | The GraphStyle value of the graph control |
| Access | Text | Who can view the graph: xecutives, (R)egional Sales Managers, (P)roduct managers, and so on |
| Periodicity | Text | (W)eekly, (M)onthly, (Q)uarterly, (Y)early |
| Periods | Double | Number of periods in graph |
| DataTable | Text | Name of the table supplying the data (for example, tblSalesTotalMonth) |
| DataField | Text | The name of the field that supplies the data for the graph |
| Legend | Yes/No | Is a legend required? |
| LegendTable | Text | The name of the table that contains the legend information |
| LegendField | Text | The name of the field containing the legend code |
| LegendTextField | Text | The name of the field containing the description of the legend |
| GridRowHeader | Text | Text for the grid-row header above the legend text |
| UseActivated | Yes/No | Test to see if category is active? |
| TableQuery1 | Text | The base query to roll up data for the graph |
| TableQuery2 | Text | A rollup subquery (if needed) |
| GraphQuery1 | Text | The name of a QueryDef object to create the graph query (if used) |
| GraphQuery2 | Text | A subquery for the graph QueryDef object (if needed) |
Figure 10.2 shows the leftmost fields of the data in tblDefinitions that are used to create the graphs described in this chapter.
Figure 10.2. The first seven fields of tblDefinitions displayed in Access 95's table data grid.
You may find that you need to create your decision-support application without having operational data or rolled up summary tables available to test the application. If you don't have the tables required to create the rolled up data, you can use an
existing database, such as Access 95's Northwind Traders sample database, to generate much of the data you need with make-table queries. The Graphs.mdb database that supplies the data for the examples in this chapter uses a combination of tables attached
from Northwind.mdb and make-table queries to generate the rolled up data needed to create the graphs and charts shown in the sections that follow.
Northwind Traders does not maintain separate tables for orders and invoices. Apparently, Northwind creates an invoice when the product is shipped, indicated by an entry in the Date Shipped field of the Orders table. Therefore, to determine monthly
backlog data, you need to compare the date on which the order was booked with the shipping date. If the month of the ShippedDate field does not match the month of the OrderDate field, the order was pending at the end of the month, and the record for the
order is included in the backlog rollups.
Combining order entry and invoicing data in a single table violates the rule that entities in a table represent only a single object. Orders and invoices are different real-world objects. Combining orders and invoices in a single table also precludes shipping partial orders and then processing backorders when the goods become available in inventory.
The qryMonthlyBacklogTotal QueryDef object of Graphs.mdb creates the tblBacklogTotalMonth table. The SQL statement that generates the total backlog data by year and month (from the date of the first entry in the Orders table through the end of the year
1995) is this:
SELECT Format(Orders.[OrderDate],"yyyy") AS Year,
Format(Orders.[OrderDate],"mm") AS Month,
Sum([Order Details].[UnitPrice]*[Order Details].Quantity*
(1-[Order Details].Discount)) AS Backlog
INTO tblBacklogTotalMonth
FROM Orders, [Order Details]
WHERE ((Orders.[OrderID]=[Order Details].[OrderID])
AND ((Format([Orders].[ShippedDate],"mmyy"))<>
Format([Orders].[OrderDate],"mmyy"))
AND (Orders.[OrderDate]<#01/1/96#))
GROUP BY Format(Orders.[OrderDate],"yyyy"),
Format(Orders.[Order Date],"mm");
The other QueryDef objects that create backlog by product category, market segment, and sales region tables add an additional GROUP BY criterion to the SQL statement.
Although the Products table of Northwind.mdb includes a UnitPrice field, there is no field for Unit Cost. To generate margin data, you need to know the cost of the product on a monthly basis. To keep your margin graphs from appearing as straight lines,
you add a random factor to the cost data. You also can factor in the effect of inflation and, because Northwind Traders deals internationally, you can add the effects of international currency value fluctuations relative to the dollar, to make your
simulated cost data appear more realistic. The Products table also has a "UnitsInStock" field that presumably indicates the current inventory level of each product. However, you need monthly inventory data, not just the current inventory.
Therefore, you also need to generate fictitious inventory levels for each month.
Before you write an application to generate the unit cost and inventory levels values for each product and period, you need to create the table to hold the values. The SQL statement that follows creates the tblInventory table that contains one record
for each product in the Products table for every month of every year for which data exists in the tblSalesTotalMonth table:
SELECT tblSalesTotalMonth.Year,
tblSalesTotalMonth.Month,
Products.[ProductID],
Products.[UnitPrice]
INTO tblInventory
FROM Products, tblSalesTotalMonth;
If you look closely at the preceding SQL statement (open the qryCartesianInventory QueryDef in Access or VisData), you'll see that the statement generates the Cartesian product of the tblSalesTotalMonth and Products tables. The Cartesian product is
generated because no join is created between the two tables with either a WHERE or INNER JOIN statement. This is one of the few instances when you purposely create a Cartesian product.
The next step is to modify the structure of tblInventory to provide the Sales (Number, Double), Units (Number, Long Integer), LandedCost (Currency), and Quantity (Number, Long Integer) fields for the table. Then, you create an update query to add the
Sales and Units data for each product for each month. For update queries with Access tables, you need to use the INNER JOIN syntax to create the required joins between the three fields of each table, as shown in the following SQL statement for
qryCartesianJoin:
UPDATE tblInventory, tblSalesProductMonth,
tblInventory INNER JOIN tblSalesProductMonth
ON tblInventory.Year = tblSalesProductMonth.Year,
tblInventory INNER JOIN tblSalesProductMonth
ON tblInventory.Month = tblSalesProductMonth.Month,
tblInventory INNER JOIN tblSalesProductMonth
ON tblInventory.[ProductID] =
tblSalesProductMonth.[ProductID]
SET tblInventory.Sales = [tblSalesProductMonth].[Sales],
tblInventory.Units = [tblSalesProductsMonth].[Units];
The Randomize application (Randomiz.vbp and Randomiz.frm in your \DDG_VB4\32_bit\Chaptr10 folder) adds the monthly unit cost data and inventory level data you need to tblInventory. The code that responds to a click of the Randomize button of
frmInventory appears in Listing 10.1.
Randomiz.vbp and all the other sample applications in this chapter require that the Graphs.mdb sample database included on the accompanying CD-ROM be located in the default C:\DDG_VB4\32_bit\Chaptr10 folder. Graphs.mdb includes files that are attached from Microsoft's Northwind.mdb that Graphs.mdb expects to find in your C:\MSOffice\Access\Samples folder. The use of the attached files from Northwind.mdb is not necessary to execute the sample applications in this chapter.
Listing 10.1. The code to create randomized values for monthly unit cost and inventory levels for each product.
Private Sub cmdRandomize_Click()
'Purpose: Random number generator for the inventory table
' Creates inflation-weighted costs (weighted average) and
' randomized inventory quantities for each month
Dim dbGraphs As Database
Dim tblInv As Recordset
Dim varCost As Variant
Dim intCtr As Integer
Dim intMonth As Integer
Dim lngQuantity As Long
Dim lngLastQuan As Long
Dim lngLastProd As Long
Set dbGraphs = OpenDatabase("c:\ddg_vb4\32_bit\chaptr10\graphs.mdb")
Set tblInv = dbGraphs.OpenRecordset("tblInventory")
'Use the timer device to seed a new random number
Randomize
'Go to the first record (for safety)
intCtr = 1
tblInv.MoveFirst
'Define this procedure as a single transaction
BeginTrans
'Loop through all of the records
Do Until tblInv.EOF
txtCounter.Text = intCtr
txtCounter.Refresh 'increment record counter and update
'Introduce random element varying initial gross margin
'from 40 to 50%
varCost = (0.5 + 0.01 * (Int(9 * Rnd + 1))) * tblInv("UnitPrice")
'Introduce inflationary factors totaling 10%
'from 1991 through 1996
intMonth = 12 * (Val(tblInv("Year")) - 1991) + _
Val(tblInv("Month"))
varCost = varCost * (1 + 0.1 * Val(tblInv("Month")) / 32)
'Create quantities on hand that are related, more or less, to cost
Select Case varCost
Case Is > 100
lngQuantity = Int(40 - 10 + 1) * Rnd + 10
Case Is > 50
lngQuantity = Int(50 - 12 + 1) * Rnd + 12
Case Is > 25
lngQuantity = Int(60 - 16 + 1) * Rnd + 16
Case Is > 10
lngQuantity = Int(80 - 20 + 1) * Rnd + 20
Case Else
lngQuantity = Int(100 - 25 + 1) * Rnd + 25
End Select
'If no sales were made during the month, use last month's quantity
If IsNull(tblInv("Sales")) And _
tblInv("ProductID") = lngLastProd Then
lngQuantity = lngLastQuan
End If
'Save the last Quantity and Product ID values
lngLastQuan = lngQuantity
lngLastProd = tblInv("ProductID")
'Update the Landed Cost and Quantity Fields
tblInv.Edit
tblInv("LandedCost") = varCost
tblInv("Quantity") = lngQuantity
tblInv.Update
'Go to the next record
tblInv.MoveNext
intCtr = intCtr + 1
Loop
'Commit the updates to all of the records
CommitTrans
End Sub
Figure 10.3 shows 10 of the 2,464 records of tblInventory with the randomized LandedCost and Quantity entries filled by the code of Private Sub cmdRandomize_Click. Only the Year, Month, LandedCost, and Quantity fields are needed for the
Inventory table of Graphs.mdb, which simulates a real Northwind Traders Inventory table. The tblInventoryProductMonth consists of a copy of the Inventory table for naming consistency with tables that contain rolled up data.
Figure 10.3. Values added to tblInventory by the randomizing code of Listing 10.1.
The potential of displaying several hundred different forms in a variety of sequences requires that you provide a simple and intuitive method of navigating between the graphs and charts that are accessible to the user:
Figure 10.4 shows the starting point of the Graphs sample application for this chapter—Graphs.vbp in your \DDG_VB4\32_bit\Chaptr10 folder. The button bar design and much of the code that displays the initial graph shown in Figure 10.4 are imported
from the Ui_examp.vbp application in Chapter 8. The sections that follow describe the development of the Graphs applications and also provide listings for the code that is needed to provide basic display capabilities.
Figure 10.4. The opening form of the Graphs application.
Your decision-support applications will have at least two classes of windows: graphs and grids. Grids may have two or more classes: grids that show detail data by category and grids that compare historical data. Although you can use the Show and Hide
methods to display separate forms for each class of window, using the Windows multiple document interface is a better approach. If you use separate forms for each class of window, you'll need to create a set of menus and menu event handlers for each form.
Using MDI forms enables you to use a common set of menus for all your graphs and grids.
Creating MDI applications with Visual Basic 4.0 is a simple, straightforward process:
You can display multiple MDI child forms in the parent form container and cascade or tile the multiple MDI child forms. The Graphs sample application uses this MDI feature only in a limited way, primarily for demonstration purposes. Inexperienced PC users have a tendency to become confused by a multiplicity of forms on their display.
Table 10.3 lists the seven possible combinations of window states that are available to applications that use MDI parent and child windows.
| Parent State | Child State | Comment |
| Maximized | Maximized | This is the most likely combination you'll want to use as the default for decision-support applications. The title of the MDI child form appears in parentheses in the title bar of the parent form. The menu bar appears as the menu bar of the child window. Only one MDI child window can appear when the child window is maximized. |
| Maximized | Normal | The title of the MDI child form appears in the child form's own menu bar. You can display multiple child forms in cascaded, horizontally tiled, and vertically tiled arrangements. The arrangement and a list of open MDI children is controlled by a standard set of Windows menu choices. |
| Maximized | Minimized | The MDI child form is minimized to an icon and the surface of the parent form or the next open child form appears. You cannot set the Backcolor property of MDI parent forms, so the background color of an MDI parent window is the Windows default background color, usually white. |
| Normal | Maximized | Similar to the maximized/maximized state, except that the MDI parent form's window is sizable. Normal/maximized is the state used to create most of the figures that appear in this chapter. |
| Normal | Normal | The MDI child form's title bar appears, and both the parent and child windows are sizable. You use code in the MDIForm_Resize event-handling subprocedure to scale the MDI form to fit the resized parent window. Figures 10.5 and 10.6 (which follow) are two examples of the appearance of the Graphs application in normal/normal windows mode. |
| Normal | Minimized | The active MDI child form is minimized to an icon. Any other open MDI child windows appear in their current window state. |
| Minimized | Not Applicable | The MDI parent window is minimized to an icon and no MDI child windows are visible. |
The Graphs application uses a button toolbar that is derived from the design of the button bar discussed in Chapter 8. Figure 10.5 illustrates the buttons used for the Graphs sample application in the normal/normal window state. The toolbar consists of
two sets (groups) of buttons; the first six buttons (from left to right), shown in Figure 10.5, determine the class of the graph, and the last three buttons determine the type of the graph or chart displayed. Thus, the user clicks the Sales, Margins,
Orders, Backlog, Cycles, or Inventory button to display the monthly totals for the chosen graph class. To display the chosen class of data by product line, market segment, or sales region, the user clicks one of the second sets of buttons. To redisplay the
totals line chart, the user clicks the class button (in the depressed or True state) again.
Figure 10.5. The Graphs navigation toolbar added to the MDI child window.
Figure 10.5 shows the parent and child windows of the Graphs sample application with the button-style toolbar contained in the MDI child window. You place toolbars in the child window when the button complement changes in accordance with which child
window is active. The toolbar buttons for the Graphs application are constant throughout the application for a single category of user. (You do need to disable or "disappear" specific graph-type buttons for graph classes to which the type does
not apply, however.) Thus, you can place the toolbar for the Graphs application on the MDI parent form.
Figure 10.6 shows the toolbar for the Graphs application contained in the MDI parent form. The document control menu is open in Figure 10.6 to demonstrate that removing the minimize button from an MDI child form by setting the value of the MinButton
property to False also removes the Minimize menu choice from the document control menu.
To add controls to the MDI parent form, you add a picture box to the form that is deep enough to contain the toolbar buttons. When you add a picture box to an MDI parent form, the value of the Align property automatically is set to 1 (Align top) and
the Width property is set to the ScaleWidth value of the form. The ScaleWidth property is the width of the client area of the MDI parent form. The client area is the area inside the borders of the parent form, below the menu, and below a picture box (when
present.) The ScaleWidth value of a parent form is 120 twips less than the value of the Width property, because sizable borders are 60 twips (3 points) wide.
Figure 10.6. The Graphs MDI parent and child forms with the toolbar contained in the parent window.
The Childbtn.vbp application in your \DDG_VB4\32_bit\Chaptr10 folder is provided so that you can compare the behavior of forms with the button bar contained in the child or parent window. Figure 10.5 was created with the Childbtn.vbp application.
The menu bar choices shown in Figure 10.6 are, with two exceptions, common to a variety of Windows applications and appear in the established sequence for MDI applications. Menu choices with accelerator-key (Alt+key) combinations are provided
for each action you can accomplish with a button, plus additional actions for which no toolbar buttons are provided. It is a good design practice to include menu choices that duplicate the action of every button of every toolbar in your application.
The two nonstandard menu bar choices, Next and Previous, take the place of Next and Previous buttons or a pair of arrow buttons on the MDI form. Substituting menu bar choices (Next and Previous have no pull-down menus) for
command buttons is unconventional, but it saves display real estate and maintains all mouse operations near the top of the display. If your toolbar has enough space to accommodate Next and Previous buttons, you can add them to the toolbar and
remove the two menu bar choices. (Left- and right-arrow icons from the \VB4\Icons\Arrows folder are good candidates for the PictureDn property of these buttons.)
Use 3D group push buttons for Next and Previous choices, instead of command buttons, to maintain an appearance that is consistent with the other toolbar buttons. Include a line that sets the Value property of each of the buttons to False in the grpButton_Click event handler so that the group push buttons emulate the operation of command buttons.
Figure 10.7 shows the Menu Design window for the MDIToolbar form of the Graphs application that displays the choices for the Window menu. The choices shown—Cascade, Tile, and Arrange Icons—are provided only as examples. (The Graphs
application does not enable the user to cascade windows, and when the tiling of child windows is necessary or practical, applying the Arrange method in the application's code controls the display of multiple windows.) Ticking the Window List check box adds
a list of the open MDI child windows to the Window menu in run mode. The window list automatically is separated by a horizontal line from the menu choices you add to the Window menu.
Figure 10.7. The standard menu choices of the Windows menu of MDI applications.
The chtMonthly graph control of the frmMDIGraph form and its associated code is copied from the Butn_bar.frm file of Chapter 8's Ui_examp.vbp application. However, you need to scale both the MDI child form and the graph contained in the MDI child form
if: (1) the user changes the window state of either the parent or the child form, or (2) the user resizes the parent form in the normal window state. You also need to change the width of each of the control buttons so that all the buttons appear when the
user makes the MDI parent form smaller. You use the MDIForm_Resize event-handling subprocedure of the MDI parent form to change both the size of the MDI child forms and the graph control (or other scalable control objects, such as image boxes) contained in
the MDI child form.
The code in Listing 10.2 resizes the MDI form, toolbar buttons, graph, and grid to occupy the largest available area within the constraints of each object's container. (The Grid control of the Graphs application is discussed later in this chapter.) In
the Graphs application, the ResizeFormAndGraph subprocedure is executed by the MDIForm_Resize and the Form_Resize event handlers of the parent and child forms, respectively. If the user resizes a normal-state child window with the mouse (or with the Size
choice of the document control menu and the arrow keys), the Resize event is triggered and the child window springs back to fill the client area of the parent form.
Listing 10.2. Code to resize the MDI child form and its graph.
Sub ResizeFormAndGraph()
'Purpose: Resize the form, graph and buttons
'Note: This function is recursive without the flag
Dim intLeft As Integer 'Left distance from client edge
If fInhibitResize Or (frmMDIToolbar.WindowState = 1) Then
'Prevent recursion and don't try changing size of an icon
Exit Sub
End If
If frmMDIGraph.WindowState = 0 Then
'Window is in normal mode so resizing is valid
frmMDIGraph.Top = 0
frmMDIGraph.Left = 0
frmMDIGraph.Width = frmMDIToolbar.ScaleWidth
frmMDIGraph.Height = frmMDIToolbar.ScaleHeight
End If
fInhibitResize = True 'Prevent recursion
DoEvents 'Give the window a chance to change size
'Set the dimensions of the graph
frmMDIGraph.chtMonthly.Width = 1.1 * frmMDIGraph.ScaleWidth
frmMDIGraph.chtMonthly.Top = 0
frmMDIGraph.chtMonthly.Left = -0.05 * frmMDIGraph.ScaleWidth
'Set the height of the graph, taking into account the grid size
If frmMDIGraph.grdMonthly.Visible = True Then
SetGridPosition
Else
If fIsCrosstab Then
frmMDIGraph.chtMonthly.Height = frmMDIGraph.ScaleHeight - 300
'Add color keys and legends to the graph
AddLegends
Else
frmMDIGraph.chtMonthly.Height = frmMDIGraph.ScaleHeight
End If
End If
'Set the widths of the grid columns
SetColWidths
'Set the size and position of both sets of buttons
For intCtr = 1 To 6
frmMDIToolbar.gpbGraphClass(intCtr).Left = intLeft
frmMDIToolbar.gpbGraphClass(intCtr).Width = (frmMDIToolbar.ScaleWidth - 15) / 9
intLeft = intLeft + frmMDIToolbar.gpbGraphClass(intCtr).Width
Next intCtr
For intCtr = 1 To 3
frmMDIToolbar.gpbGraphType(intCtr).Left = intLeft
frmMDIToolbar.gpbGraphType(intCtr).Width = (frmMDIToolbar.ScaleWidth - 15) / 9
intLeft = intLeft + frmMDIToolbar.gpbGraphType(intCtr).Width
Next intCtr
'Reset the recursion flag
fInhibitResize = False
End Sub
Another method for preventing the user from resizing an MDI child window is to set the value of the BorderStyle property of the child window to 0 (None) or 1 (Fixed Single). The effect of changing the BorderStyle from the default value of 2 (Sizable)
to one of the other three choices is as follows:
In some cases, such as a horizontal split screen, you may want to make one of the child windows user-sizable and use code to resize the other window to fill the remaining space in the client area of the parent form. You can emulate a split window by this method. The one-sizable/one-not approach is used in the examples later in the chapter that display Grid controls and graphs in two MDI child windows on a single form.
Figure 10.9. The appearance of an MDI child window with the value of its BorderStyle property set to 3.
Up to this point, all the examples have used the default Monthly Sales for 1994 data, which appears as the default graph when you launch the Graphs application. (1994 is the only year in the Northwind Traders sample database for which there is a full
year of data.) The sections that follow describe the primary functions and subprocedures of Graphs.vbp that, except as noted, are contained in the Graphs2.bas module.
After you have your data sources established, the next step is to rewrite the DrawMonthlyGraph procedure of Chapter 8 to use the fields of records in the tblDefinitions table of Graphs.mdb to supply the following values for each graph:
Use control arrays whenever possible in decision-support applications. Control arrays are used for the two sets of group push buttons on frmMDIGraph as well as for the legend text and color images (imgColor(0) through imgColor(9)) for the legend and the first column of the Grid control. The use of control arrays is one of Visual Basic's most important advantages over the control objects offered by Access. Using a control array, instead of individual controls, often saves a substantial number of lines of code in decision-support applications.
Listing 10.3 shows the Visual Basic code used to create graphs from data contained in two different levels of tables: (1) simple monthly-summary tables typical of those that store rolled up data from detail tables of mainframe and minicomputer
databases, and (2) crosstab tables you create from data in rollup tables using Jet SQL's TRANSFORM and PIVOT statements. Simple level 1 tables are processed row-by-row, and crosstab level 2 tables (fCrosstab = True) are processed row-by-column using
nested loops. The code shown in Listing 10.3 specifies the type and style of the graph (chtMonthly), establishes the number of data points (12) and data sets (NumSets = 1 for simple line graphs or NumSets = RecordCount for crosstab area charts), and
provides the data for each point (using the ThisPoint property) of each data set (using the ThisSet property).
Sub Main contains the declarations of Public variables, including all the object variables required for the Graphs application. The MDIForm_Load and Form_Load procedures for the MDI parent (frmMDIToolbar) and child (frmMDIGraph) forms establish the initial size as well as other variable values that apply only to these two forms.
Listing 10.3. The code for the DrawMonthlyGraph() function, which creates the data for the graph and Grid controls.
Function DrawMonthlyGraph() As Integer
'Purpose: Draw the monthly sales graph using the data in tblSalesRollupMonth
' The graph is actually drawn by the calling procedure
'Called by: frmMDIGraph_Load, menu choices, and button clicks
'Local variables
Dim fPlotPoints As Integer 'Turn on plotting of points
Dim intDataSets As Integer 'Number of data sets
Dim intSet As Integer 'Data set counter
Dim intColStart As Integer 'Beginning column of query Snapshot
Dim intCol As Integer 'Column counter for data points
Dim strSQL As String 'SQL statement for query
Dim intNullPoints As Integer
'Check to see that the table is available
For intCtr = 0 To dbGraphs.TableDefs.Count - 1
If dbGraphs.TableDefs(intCtr).Name = tblGraphs("DataTable") Then
fPlotPoints = True
End If
Next intCtr
If Not fPlotPoints Then
MsgBox prompt:="Table '" & tblGraphs("DataTable") & _
"' not available.", _
buttons:=vbCritical, Title:="Programmer at Work"
Exit Function
End If
On Error GoTo DrawError
Screen.MousePointer = 11 'Set the mousepointer to the hourglass
'Set the title bar for the MDI child form
frmMDIGraph.Caption = tblGraphs("GraphCaption") & " for " & strYear
'Set the GraphType and GraphStyle property values for the chart
frmMDIGraph.chtMonthly.DataReset = 2 'Reset the color data
frmMDIGraph.chtMonthly.GraphType = tblGraphs("GraphType")
frmMDIGraph.chtMonthly.GraphStyle = tblGraphs("GraphStyle")
'Enable and set the appropriate buttons with inhibit on
fInhibit = True
SetTypeButtons
If tblGraphs("Legend") Then
'Only crosstab queries have legends
fIsCrosstab = True
Else
fIsCrosstab = False
End If
'Snapshot the rollup table with SQL statement from strCreateSQL()
strSQL = strCreateSQL()
'Paste the SQL statement to the clipboard (for testing purposes)
Clipboard.SetText strSQL 'Remove this line in production version
'Create the Snapshot object from the SQL statement
Set ssGraph = dbGraphs.OpenRecordset(strSQL)
'Hide the grid while updating (speeds update operation)
frmMDIGraph.grdMonthly.Visible = False
ssGraph.MoveLast 'Go to the last record to get record count (important)
frmMDIGraph.grdMonthly.Row = 0 'Fixed column header for grid
If fIsCrosstab Then
'Set thin lines and turn off statistics
frmMDIGraph.chtMonthly.ThickLines = 0
frmMDIGraph.chtMonthly.LineStats = 0
'Set the number of data sets in the graph and the grid
frmMDIGraph.chtMonthly.NumSets = ssGraph.RecordCount
frmMDIGraph.grdMonthly.Rows = ssGraph.RecordCount + 2
intColStart = 1 'Column 0 = GROUP BY identifier
'Create the grid column and row headers
GridColRowHeaders
Else
'Set thick lines and turn on statistics
frmMDIGraph.chtMonthly.ThickLines = 1
frmMDIGraph.chtMonthly.LineStats = 9 'Add best fit and mean statistics
'Monthly totals or averages only have 1 data set
frmMDIGraph.chtMonthly.NumSets = 1
frmMDIGraph.grdMonthly.Rows = 2
intColStart = 2 'Column 0 = Year, Column 1 = Month
'Clear the image and text from the left box
ClearGrid 1, 0
'Create the grid column headers (only)
GridColHeaders
'Test for January of "current" year
ssGraph.MoveFirst
If ssGraph("Month") = "01" Then
strYearStart = ssGraph.Bookmark
'Set the graph type to line and the style to standard
frmMDIGraph.chtMonthly.GraphType = 6
frmMDIGraph.chtMonthly.GraphStyle = 0
frmMDIGraph.chtMonthly.NumSets = 1
Else
'An error condition
MsgBox prompt:="No data found for January, " & _
strYear & ".", _
buttons:=vbCritical, Title:="Error in Rollup"
ssGraph.Close
Exit Function
End If
End If
'Set the grid column widths
SetColWidths
'Disappear the standard legends and labels
For intCtr = 1 To 8
frmMDIGraph.imgColor(intCtr).Visible = False
frmMDIGraph.lblLegend(intCtr).Visible = False
Next intCtr
ssGraph.MoveFirst
intCtr = 1
'Load the data points and the grid explicitly
'Note: Two procedures are used for code clarity and operating speed
If fIsCrosstab Then
'Set up the crossfoot row and column totals
ReDim varRowTotals(frmMDIGraph.chtMonthly.NumSets)
ReDim varColTotals(frmMDIGraph.chtMonthly.NumPoints)
'Process rows by column
intSet = 1
Do Until ssGraph.EOF
'Process rows
intCtr = 1
intNullPoints = 0
For intCol = intColStart To ssGraph.Fields.Count - 1
'Set graph and grid columns and rows
frmMDIGraph.chtMonthly.ThisSet = intSet
frmMDIGraph.grdMonthly.Row = intSet
frmMDIGraph.chtMonthly.ThisPoint = intCtr
frmMDIGraph.grdMonthly.Col = intCtr + 1
'Set the graph and grid data when data becomes available
If fPlotPoints Or Not IsNull(ssGraph.Fields(intCol)) Then
'Test for Null values (Null causes an error)
If IsNull(ssGraph.Fields(intCol)) Then
'Null values represent 0 when point plotting is on
frmMDIGraph.chtMonthly.GraphData = 0
frmMDIGraph.grdMonthly.Text = ""
intNullPoints = intNullPoints + 1
Else
frmMDIGraph.chtMonthly.GraphData = ssGraph.Fields(intCol)
'Set the crosstab grid values
SetGridValues intCol, intSet
'Turn on plot pointing when the first value is encountered
fPlotPoints = True
End If
End If
intCtr = intCtr + 1
DoEvents
Next intCol
ssGraph.MoveNext
intSet = intSet + 1
Loop
'Add the row and column totals
SetGridCrossfoot
Else
'Process each record in the monthly summary table (1 record/month)
frmMDIGraph.chtMonthly.ThisSet = 1
'Set up the grid with 12 columns and no fixed column
frmMDIGraph.grdMonthly.Cols = 12
frmMDIGraph.grdMonthly.FixedCols = 0
frmMDIGraph.grdMonthly.Row = 1
'Set the point to the first month in the table (usually 1)
intCtr = Val(ssGraph("Month"))
Do Until ssGraph.EOF
frmMDIGraph.chtMonthly.ThisPoint = intCtr
frmMDIGraph.grdMonthly.Col = intCtr - 1
If IsNull(ssGraph("SumOfData")) Then
frmMDIGraph.chtMonthly.GraphData = 0
frmMDIGraph.grdMonthly.Text = ""
frmMDIGraph.grdMonthly.Caption = ""
Else
frmMDIGraph.chtMonthly.GraphData = ssGraph("SumOfData")
SetGridValues 2, 1
End If
ssGraph.MoveNext
intCtr = intCtr + 1
Loop
End If
ssGraph.Close
If fShowGrid Then
frmMDIGraph.grdMonthly.Visible = True
End If
SetGridPosition 'Set the position of the grid in the form
Screen.MousePointer = 0 'Reset the mouse pointer
DrawMonthlyGraph = True 'Set the value of the function
fInhibit = False
'Only draw if the graph types and number of points are the same
'Note: Graph autoredraws if GraphType or NumPoints changes
If intLastType = frmMDIGraph.chtMonthly.GraphType Then
'Redraw except under following condition
If intLastSets = frmMDIGraph.chtMonthly.NumSets Or _
frmMDIGraph.chtMonthly.GraphType = 8 Then
If intLastType <> 0 Then
'Note: This prevents flashing or bouncing of the graph
' intLastType = 0 on startup
frmMDIGraph.chtMonthly.DrawMode = 2
End If
End If
End If
'Save the last graph type and number of points
intLastType = frmMDIGraph.chtMonthly.GraphType
intLastSets = frmMDIGraph.chtMonthly.NumSets
intLastPoints = frmMDIGraph.chtMonthly.NumPoints
Exit Function
DrawError:
MsgBox prompt:=Err.Description, _
buttons:=vbCritical, _
Title:="Error Drawing Graph or Chart"
fInhibit = False
Screen.MousePointer = 0 'Reset the mouse pointer
Exit Function
End Function
Use the Clipboard.SetText strSQLStatement method when you're developing the code to write complex SQL statements. You can paste the SQL statement into the SQL Statement window of the VisData application or into the SQL text box of Access's query design window to check for syntax errors and view the query result table.
The code in Listing 10.3 also supplies data for the cells of the Grid control grdMonthly on the frmMDIGraphs form. The code used to generate the Grid control is described in the section titled "Adding a Detail Grid to the MDI Child Form,"
later in this chapter.
The two SQL statements you need for creating the snapshot-type Recordset object to supply the data to the line graph and area chart that display monthly gross margins by category are as follows:
SELECT Year, Month, Margin AS SumOfData
FROM tblMarginAverageMonth
WHERE Year = "1995" ;
TRANSFORM SUM(Margin) AS SumOfData
SELECT [CategoryID]
FROM tblMarginCategoryMonth
WHERE Year = "1995"
GROUP BY [CategoryID]
PIVOT Month IN ("01", "02", "03", "04", "05", "06",
"07", "08", "09", "10", "11", "12");
Although the crosstab query statement uses the SUM() SQL aggregate function in the TRANSFORM clause, there is only one record for the margins of products in a given category for each year and month. Thus, the crosstab query "sums" a single record and serves only to reformat the data from a tabular structure to spreadsheet style. If your crosstab query is based on a table with more than one record per query, you could use the AVG() function to determine the average margin. Using the AVG() function, however, does not return the sales-weighted average margin that your clients are likely to want. Therefore, you should use individual rollup tables to create each type of graph that displays percentages.
The code for the strCreateSQL() function shown in Listing 10.4 is typical of the functions needed to create crosstab queries for a variety of decision-support applications. The IN() predicate is used to ensure that columns are created for 12 months,
even if there is no data for some of the months. Using a fixed number of months makes programming simpler and provides a constant abscissa for the graphs.
Listing 10.4. Code to create required SELECT and crosstab query statements.
Function strCreateSQL() As String
'Purpose: Create the SQL statement for SELECT and crosstab queries
' Put the grid legend labels in the strLegendText() array
' Put the graph legend labels in the strLegend() array
'Called by: DrawMonthlyGraph
Dim strSQLTransform As String 'TRANSFORM clause
Dim strSQLSelect As String 'SELECT clause
Dim strSQLFrom As String 'FROM clause
Dim strSQLWhere As String 'WHERE criterion
Dim strSQLGroupBy As String 'GROUP BY clause
Dim strSQLPivot As String 'PIVOT clause
Dim strSQLIn As String 'IN predicate
Dim intDataSets As Integer 'Data sets in crosstab
'Create the basic FROM and WHERE clauses
strSQLFrom = "FROM " & tblGraphs("DataTable") & " "
strSQLWhere = "WHERE Year = " & Chr(34) & strYear & Chr(34) & " "
If fIsCrosstab Then
'Create the SELECT statement for the crosstab
strSQLSelect = "SELECT [" & tblGraphs("LegendField") & "] "
'Define Access SQL crosstab clauses
strSQLTransform = "TRANSFORM Sum(" & tblGraphs("DataField") & _
") AS SumOfData "
strSQLGroupBy = "GROUP BY [" & tblGraphs("LegendField") & "] "
strSQLPivot = "PIVOT Month "
'Create the legends from the appropriate table
Set ssLegend = dbGraphs.OpenRecordset(Name:=tblGraphs("LegendTable"), _
Type:=dbOpenSnapshot)
ssLegend.MoveFirst
Do Until ssLegend.EOF
'Create a list of quoted, comma-separated strings
If tblGraphs("UseActivated") Then
'Test if category is presently active
If ssLegend("Activated") Then
'Add the category to the IN predicate
intDataSets = intDataSets + 1
strLegendText(intDataSets) = ssLegend(tblGraphs("LegendTextField"))
strLegend(intDataSets) = ssLegend(tblGraphs("LegendField"))
End If
Else
'Add all categories to the IN predicate
intDataSets = intDataSets + 1
strLegendText(intDataSets) = ssLegend(tblGraphs("LegendTextField"))
strLegend(intDataSets) = ssLegend(tblGraphs("LegendField"))
End If
ssLegend.MoveNext
Loop
'Create the IN predicate from the strMonths() array
strSQLIn = "IN ("
For intCtr = 1 To 12
strSQLIn = strSQLIn & Chr(34) & strMonths(intCtr, 3) & Chr(34) & ", "
Next intCtr
strSQLIn = Left(strSQLIn, Len(strSQLIn) - 2) & ")"
'Concatenate the SQL clauses for the crosstab query
strCreateSQL = strSQLTransform & strSQLSelect & strSQLFrom & _
strSQLWhere & strSQLGroupBy & strSQLPivot & strSQLIn & ";"
Else
'Create a conventional SELECT query statement
strSQLSelect = "SELECT Year, Month, " & _
tblGraphs("DataField") & " AS SumOfData "
'Concatenate the SQL clauses for the SELECT query
strCreateSQL = strSQLSelect & strSQLFrom & strSQLWhere & ";"
End If
End Function
The standard legend for area charts that you generate with the graph custom control only can appear to the right of the chart. The graph custom control does not have Microsoft Graph's capability to format and relocate legends. To maximize the width of
the chart itself, legends should be placed in a single line under the chart, as shown in Figure 10.10. If Figure 10.10 were a four-color plate, you could see that the small boxes adjacent to the legend text match the colors of the stacked areas. Run the
Graphs.vbp project and then click the Regions button to display the graph shown in Figure 10.10.
Figure 10.10. The Sales by Sales Region graph with a horizontal legend at the bottom of the graph.
The AddLegends subprocedure, whose code appears in Listing 10.5, uses eight of the imgColor() color image boxes and the eight lblLegend() labels to create the horizontal legend shown in Figure 10.10. Most of the code in Listing 10.5 is required to
properly space a varying number of legends across the width of the form. You need to call your AddLegends subprocedure when the Resize event is triggered so that the legends always appear in the proper location.
Listing 10.5. Code to add a horizontal set of color key images and legend text to a chart.
Sub AddLegends()
'Purpose: Add color keys legends to the graph
Dim strText As String
Dim intWidth As Integer
Dim intMargin As Integer
intMargin = (frmMDIGraph.ScaleWidth / _
(frmMDIGraph.chtMonthly.NumSets * 2)) - 300
For intCtr = 1 To frmMDIGraph.chtMonthly.NumSets
'Legend color keys
frmMDIGraph.imgColor(intCtr).Width = 240
frmMDIGraph.imgColor(intCtr).Height = 250
frmMDIGraph.imgColor(intCtr).Top = frmMDIGraph.ScaleHeight - 365
frmMDIGraph.imgColor(intCtr).Left = _
intMargin + (frmMDIGraph.ScaleWidth - intMargin) * _
(intCtr - 1) / frmMDIGraph.chtMonthly.NumSets
frmMDIGraph.imgColor(intCtr).Visible = True
'Legend labels
frmMDIGraph.lblLegend(intCtr).Width = 600
frmMDIGraph.lblLegend(intCtr).Top = frmMDIGraph.ScaleHeight - 300
frmMDIGraph.lblLegend(intCtr).Left = frmMDIGraph.imgColor(intCtr).Left + 300
If InStr(strLegend(intCtr), "&") > 0 Then
'Use a && to prevent underlining with & character in captions
strText = strLegend(intCtr)
If InStr(strText, "&") = 1 Then
strText = "&" & strText
ElseIf InStr(strText, "&") = Len(strText) Then
strText = strText & "&"
Else
strText = Left(strText, InStr(strText, "&") - 1) & "&" & _
Mid(strText, InStr(strText, "&"))
End If
frmMDIGraph.lblLegend(intCtr).Caption = strText
Else
frmMDIGraph.lblLegend(intCtr).Caption = strLegend(intCtr)
End If
frmMDIGraph.lblLegend(intCtr).Visible = True
Next intCtr
End Sub
The If InStr(strLegend(intCtr), "&") > 0 Then. . .End If structure detects the presence of an ampersand (&) in the ES&W (England, Scotland, and Wales—AKA Great Britain) region ID code. An ampersand (&) in a caption results in the underlining of the character following the ampersand and the disappearance of a character you want to appear as a literal. The preceding structure adds a double-ampersand (&&) to make a single & character appear.
Another advantage of creating your own chart legend is that you can use event handlers to take a specific action when the user clicks or double-clicks the image box or the label. Legends you create with Visual Basic controls are active objects; the
legends of the graph control are passive—that is, legends are not hot spots on the graph.
Graphs and charts are an excellent means of depicting trends, but (at least in the business world) numbers are what count. Therefore, you need to provide the user of a graphic-based application the capability to display the values that underlie the
graph. The simplest method of displaying numerical data underlying the graph is to use the Grid control, as shown in the maximized window state in Figure 10.11. To make the Grid control in Figure 10.11 appear when one of the area charts is displayed by the
Graphs application, double-click the background of the chart or choose the View | Detail command from the Graphs application's menu.
When you add a Grid control to the bottom of the graph, vertical display area comes at a premium. You can eliminate the area needed for a color-coded legend by adding a column to the grid that displays a "color chip" corresponding to the
associated element of the area chart. The color chips in the leftmost column of the grid are another instance of the image controls that create the horizontal legend color codes. You have to take it on faith that the colors in the check mark column of the
grid of the black-and-white Figure 10.11 actually correspond to the colors of the associated stacked chart elements—or you can execute the Graphs.vbp application to see it work for yourself.
Figure 10.11. A Grid control that displays the data underlying the regional sales area chart.
When the user chooses the normal window state, a grid with more than four rows squeezes the graph into an area with a small vertical dimension. Thus, the ResizeFormAndGraph subprocedure changes the height of the grid to four rows when the window state
is normal, as illustrated by Figure 10.11. If the user maximizes the window, the grid is changed to show all of the available columns, as shown in Figure 10.12.
The sections that follow describe the Visual Basic code used to define, populate, format, and size the Grid control, as well as to add crossfoot totals to the grid. Adding crossfoot totals to a Visual Basic Grid control is much simpler than adding row
and column totals to an Access continuous form, because Access requires that you place the column headers in the form header and the column totals in the form footer. Access does, however, offer more flexibility in formatting or coloring a grid.
Figure 10.12. A level 2 multiline graph in the maximized window state with a full-table grid.
The code to specify the dimensions of the grid, create the column and row headers, and add the values to the data cells of the grid shown in Figure 10.11 appears in Listing 10.6. The GridColRowHeaders subprocedure sets the number of rows and columns of
the Grid control. The SetColWidths subprocedure determines the widths of the grid's columns. The SetGridValues subprocedure fills the fixed rows and columns with the color images and data. (SetColWidths is a separate subprocedure, because you also need to
change the column widths when the user resizes the MDI parent window.)
Listing 10.6. The three procedures required to create a Grid control that displays numerical graph data.
Sub GridColRowHeaders()
'Label the grid column headers
frmMDIGraph.grdMonthly.Cols = 15
frmMDIGraph.grdMonthly.FixedCols = 2
frmMDIGraph.grdMonthly.FixedAlignment(0) = 0
frmMDIGraph.grdMonthly.FixedAlignment(1) = 0
For intCtr = 2 To 14
frmMDIGraph.grdMonthly.Col = intCtr
frmMDIGraph.grdMonthly.ColAlignment(intCtr) = 1
frmMDIGraph.grdMonthly.FixedAlignment(intCtr) = 2
If intCtr < 14 Then
frmMDIGraph.grdMonthly.Text = strMonths(intCtr - 1, 1)
Else
frmMDIGraph.grdMonthly.Text = "Totals"
End If
Next intCtr
'Add the grid legend pictures
frmMDIGraph.grdMonthly.Col = 0
For intCtr = 0 To frmMDIGraph.grdMonthly.Rows - 1
frmMDIGraph.grdMonthly.Row = intCtr
If intCtr = frmMDIGraph.grdMonthly.Rows - 1 Then
frmMDIGraph.grdMonthly.Picture = frmMDIGraph.imgColor(11)
Else
frmMDIGraph.grdMonthly.Picture = frmMDIGraph.imgColor(intCtr)
End If
Next intCtr
'Label the grid row headers
frmMDIGraph.grdMonthly.Col = 1
For intCtr = 0 To frmMDIGraph.grdMonthly.Rows - 1
frmMDIGraph.grdMonthly.Row = intCtr
If intCtr = 0 Then
frmMDIGraph.grdMonthly.Text = tblGraphs("GridRowHeader")
ElseIf frmMDIGraph.grdMonthly.Row = frmMDIGraph.grdMonthly.Rows - 1 Then
frmMDIGraph.grdMonthly.Text = "Monthly Totals"
Else
frmMDIGraph.grdMonthly.Text = strLegendText(intCtr)
End If
Next intCtr
End Sub
Sub SetColWidths()
With frmMDIGraph.grdMonthly
'Set the column widths for numerical values
For intCtr = 0 To .Cols - 1
If fIsCrosstab Then
'Use fixed column widths for crosstabs
.ColWidth(intCtr) = 774
Else
'Use variable column widths for monthly data
.ColWidth(intCtr) = Int(.Width / .Cols) - 18
End If
Next intCtr
If fIsCrosstab Then
'Fix the first two column widths
.ColWidth(0) = .RowHeight(0)
.ColWidth(1) = 1200
End If
End With
End Sub
Sub SetGridValues(intCol As Integer, intSet As Integer)
'Purpose: Set the grid values for crosstab queries, accumulate sums
'Arguments: intCol = column number, intSet = row number
If fIsCrosstab Then
'Sum only for crosstabs
varColTotals(intCtr) = varColTotals(intCtr) + ssGraph.Fields(intCol)
varRowTotals(intSet) = varRowTotals(intSet) + ssGraph.Fields(intCol)
End If
'Format the data in accordance with the Units field
If intSet = 1 Then
'First row has symbols determined by definitions table
If tblGraphs("Units") = "$" Then
frmMDIGraph.grdMonthly.Text = Format(ssGraph.Fields(intCol), "$#,##0")
ElseIf tblGraphs("Units") = "%" Then
'Note: The percent format automatically multiplies by 100
frmMDIGraph.grdMonthly.Text = Format(ssGraph.Fields(intCol), "##0.0%")
ElseIf tblGraphs("Units") = "Avg" Then
frmMDIGraph.grdMonthly.Text = Format(ssGraph.Fields(intCol), "#,##0.00")
Else
frmMDIGraph.grdMonthly.Text = Format(ssGraph.Fields(intCol), "#,##0")
End If
Else
'Remaining rows use standard formatting, except percent
If tblGraphs("Units") = "%" Then
'Provide right alignment spacing for missing % symbol
frmMDIGraph.grdMonthly.Text = _
Format(100 * ssGraph.Fields(intCol), "##0.0") & " "
ElseIf tblGraphs("Units") = "Avg" Then
frmMDIGraph.grdMonthly.Text = Format(ssGraph.Fields(intCol), "#,##0.00")
Else
frmMDIGraph.grdMonthly.Text = Format(ssGraph.Fields(intCol), "#,##0")
End If
End If
End Sub
Grid controls also can provide your client crossfooted row and column totals, as shown in the Grid control of Figure 10.12. Listing 10.7 is the code used to create totals for dollar values and averages for percentage and other values that require
averaging, such as order cycle time in days. A substantial part of the code for the SetGridCrossfoot subprocedure is needed to compute averages properly; null values in cells (represented by empty "" cells) are not included in the averaging
divisor. The Margins by Product Category graph uses the averaging method.
Listing 10.7. The code to create row and column totals and to crossfoot the grid data.
Sub SetGridCrossfoot()
'Purpose: Add the crossfoot grid totals from the graph data to the grid
Dim intRow As Integer
Dim intCol As Integer
ReDim intNotNullRows(frmMDIGraph.chtMonthly.NumSets) As Integer
ReDim intNotNullCols(frmMDIGraph.chtMonthly.NumPoints) As Integer
On Error GoTo CrossfootError
varColTotals(0) = 0
'Count not-Null ("") rows and columns for averaging
If tblGraphs("Units") = "%" Or tblGraphs("Units") = "Avg" Then
For intRow = 1 To frmMDIGraph.chtMonthly.NumSets
frmMDIGraph.grdMonthly.Row = intRow
For intCol = 1 To frmMDIGraph.chtMonthly.NumPoints
frmMDIGraph.grdMonthly.Col = intCol + 1
If frmMDIGraph.grdMonthly.Text <> "" Then
intNotNullCols(intCol) = intNotNullCols(intCol) + 1
intNotNullRows(intRow) = intNotNullRows(intRow) + 1
End If
Next intCol
Next intRow
For intRow = 1 To frmMDIGraph.chtMonthly.NumSets
If intNotNullRows(intRow) = 0 Then
intNotNullRows(intRow) = 1
End If
Next intRow
For intCol = 1 To frmMDIGraph.chtMonthly.NumPoints
If intNotNullCols(intCol) = 0 Then
intNotNullCols(intCol) = 1
End If
Next intCol
End If
'Place column totals in last row of each column
frmMDIGraph.grdMonthly.Row = frmMDIGraph.grdMonthly.Rows - 1
For intCol = 1 To frmMDIGraph.chtMonthly.NumPoints
frmMDIGraph.grdMonthly.Col = intCol + 1
Select Case tblGraphs("Units")
Case "$"
frmMDIGraph.grdMonthly.Text = Format(varColTotals(intCol), "$#,##0")
Case "%"
frmMDIGraph.grdMonthly.Text = Format(varColTotals(intCol) / _
intNotNullCols(intCol), "##0.0%")
Case "Avg"
frmMDIGraph.grdMonthly.Text = Format(varColTotals(intCol) / _
intNotNullCols(intCol), "##0.00")
Case Else
frmMDIGraph.grdMonthly.Text = Format(varColTotals(intCol), "#,##0")
End Select
varColTotals(0) = varColTotals(0) + varColTotals(intCol)
Next intCol
varRowTotals(0) = 0
'Place row totals in last column of each row
frmMDIGraph.grdMonthly.Col = 14
For intRow = 1 To frmMDIGraph.chtMonthly.NumSets
frmMDIGraph.grdMonthly.Row = intRow
Select Case tblGraphs("Units")
Case "$"
frmMDIGraph.grdMonthly.Text = Format(varRowTotals(intRow), "$#,##0")
Case "%"
frmMDIGraph.grdMonthly.Text = Format(varRowTotals(intRow) / _
intNotNullRows(intRow), "##0.0%")
Case "Avg"
frmMDIGraph.grdMonthly.Text = Format(varRowTotals(intRow) / _
intNotNullRows(intRow), "##0.00")
Case Else
frmMDIGraph.grdMonthly.Text = Format(varRowTotals(intRow), "#,##0")
End Select
varRowTotals(0) = varRowTotals(0) + (varRowTotals(intRow) / _
frmMDIGraph.chtMonthly.NumSets)
Next intRow
frmMDIGraph.grdMonthly.Row = frmMDIGraph.grdMonthly.Rows - 1
'Use the arithmetic average for "%" and "Avg" values
If (tblGraphs("Units") = "%" Or tblGraphs("Units") = "Avg") Then
varRowTotals(0) = varRowTotals(0) / frmMDIGraph.chtMonthly.NumPoints
varColTotals(0) = varColTotals(0) / frmMDIGraph.chtMonthly.NumSets
frmMDIGraph.grdMonthly.Col = 1
frmMDIGraph.grdMonthly.Text = "Averages"
End If
'Add crossfoot values to grid
frmMDIGraph.grdMonthly.Col = 14
Select Case tblGraphs("Units")
Case "$"
frmMDIGraph.grdMonthly.Text = Format(varRowTotals(0), "$#,##0")
Case "%"
frmMDIGraph.grdMonthly.Text = Format(varRowTotals(0), "##0.0%")
Case "Avg"
frmMDIGraph.grdMonthly.Text = Format(varRowTotals(0), "##0.00")
Case Else
frmMDIGraph.grdMonthly.Text = Format(varRowTotals(0), "#,##0")
End Select
Exit Sub
CrossfootError:
MsgBox prompt:="Error computing totals.", _
buttons:=vbCritical, _
Title:="Crossfooting Error"
Exit Sub
End Sub
The format of the data for Grid controls that display dollar totals and averages for other values should comply with conventional accounting practices. The first row of values includes a dollar sign ($) or a percentage symbol (%). The symbol is omitted from the remaining rows of data. Dollar signs indicate totals, and percentage symbols can represent averages or totals of individual percentage values (usually 100 percent). The Format() statements in the SetGridValues subprocedure of Listing 10.6 and the SetGridCrossfoot subprocedure of Listing 10.7 follow this formatting standard.
You can infer the distribution of sales, orders, backlog, and inventory from the area charts that constitute the second level of the Graph sample application. However, using a pie chart to show the percentage composition of categories making up the
month's total is a more effective presentation method. Figure 10.13 uses a three-dimensional pie chart to show the distribution of Northwind Traders' Inventory by Product Category for the month of February 1994. To display the pie chart shown in Figure
10.13, click the Inventory and Products buttons. Double-click the area chart that appears to display the grid (if the grid is not visible). Then, double-click the February column heading in the grid.
Technically, the pie chart shown in Figure 10.13 is not a drill-down chart. Strictly defined, drill-down charts and grids require that you present more detailed data as you drill through each level in the hierarchy. The pie chart in Figure 10.13 does not present more detailed data—it presents an alternate view of less data than is presented in the grid.
The frmMDIPieChart form contains the chtPieChart graph object. The code that detects the double-click on the Grid control's fixed row and the code that draws the pie chart from the data in the selected column of the row appear in Listing 10.8. The
grdMonthly_DblClick event handler determines the column that has been selected and then checks to see that all cells in the column are selected. (When you double-click the column header, the Grid control automatically selects all the cells in the column.)
Figure 10.13. A pie chart showing the distribution of inventory between product classes.
Although included together in Listing 10.8, the grd_Monthly_DblClick procedure is stored in the grdMonthly Grid control on the grfMDIGraph form of the Graphs.vbp application. The DrawPieChart procedure is stored in the Graphs2.bas module.
Listing 10.8. Code to display a pie chart from one set of data points of an area chart.
Private Sub grdMonthly_DblClick()
'Purpose: Detect a double-click on the grid for drill-down
With grdMonthly
If fIsCrosstab And .SelStartCol > 1 Then
'Month column double-clicked
If .SelStartRow = 1 And .SelEndRow = .Rows - 1 Then
'Column header double-clicked
DrawPieChart
Else
'A category was double clicked
'Use a grid to display individual products
End If
End If
End With
End Sub
Sub DrawPieChart()
'Purpose: Draw a Pie Chart with graph data
'Note: This procedure is located in GRAPHS2.BAS because of
' an inconsistency in VB's recognition of object
' variables. Object variables declared with global scope
' can lose their object variable type under some circumstances.
' The result is an 'Object is not an array' error message
Dim dblTotal As Double
Dim intPoints As Integer
Dim intPoint As Integer
If tblGraphs("Units") <> "$" Then
Exit Sub
End If
fInhibitResize = True
frmMDIPieChart.chtPieChart.Visible = False
'Set the size of the window, if normal state
If frmMDIPieChart.WindowState = 0 Then
frmMDIPieChart.Top = 0
frmMDIPieChart.Left = 0
frmMDIPieChart.Width = frmMDIToolbar.ScaleWidth
frmMDIPieChart.Height = frmMDIToolbar.ScaleHeight
End If
'Perform initial sizing of graph
frmMDIPieChart.chtPieChart.Width = frmMDIPieChart.Width
frmMDIPieChart.chtPieChart.Left = -(frmMDIPieChart.chtPieChart.Width - _
frmMDIPieChart.Width) / 2
frmMDIPieChart.chtPieChart.Height = 1.2 * frmMDIPieChart.Height
frmMDIPieChart.chtPieChart.Top = -(frmMDIPieChart.chtPieChart.Height - _
frmMDIPieChart.Height) / 2
'Set the captions, number of points, etc.
frmMDIPieChart.Caption = frmMDIGraph.Caption & " - " & _
strMonths(frmMDIGraph.grdMonthly.SelStartCol, 2)
frmMDIPieChart.chtPieChart.AutoInc = 0
'Set font and graph characteristics
frmMDIPieChart.chtPieChart.FontUse = 4
frmMDIPieChart.chtPieChart.FontFamily = 1
frmMDIPieChart.chtPieChart.FontStyle = 2
frmMDIPieChart.chtPieChart.FontSize = 200 '(Not effective)
frmMDIPieChart.chtPieChart.GraphStyle = 0
frmMDIGraph.chtMonthly.ThisPoint = frmMDIGraph.grdMonthly.SelStartCol - 1
'Get the total of the set
For intCtr = 1 To frmMDIGraph.chtMonthly.NumSets
frmMDIGraph.chtMonthly.ThisSet = intCtr
dblTotal = dblTotal + frmMDIGraph.chtMonthly.GraphData
Next intCtr
'Get the number of points > 1% of total
For intCtr = 1 To frmMDIGraph.chtMonthly.NumSets
frmMDIGraph.chtMonthly.ThisSet = intCtr
If frmMDIGraph.chtMonthly.GraphData > 0.01 * dblTotal Then
intPoints = intPoints + 1
End If
Next intCtr
frmMDIPieChart.chtPieChart.NumPoints = intPoints
'Reset all the data, then set the data
frmMDIPieChart.chtPieChart.DataReset = 9
intPoint = 1
For intCtr = 1 To frmMDIGraph.chtMonthly.NumSets
'Set the data point of the originating graph
frmMDIGraph.chtMonthly.ThisSet = intCtr
'Don't display data for 0-value items
If frmMDIGraph.chtMonthly.GraphData > 0.01 * dblTotal Then
'Set the data point of the pie chart
frmMDIPieChart.chtPieChart.ThisPoint = intPoint
frmMDIPieChart.chtPieChart.GraphData = frmMDIGraph.chtMonthly.GraphData
If intCtr = 4 Then
'Demonstrate an exploded wedge
frmMDIPieChart.chtPieChart.ExtraData = 1
End If
'Set the grid column and row for the lable
frmMDIGraph.grdMonthly.Col = 1
frmMDIGraph.grdMonthly.Row = intCtr
frmMDIPieChart.chtPieChart.LabelText = frmMDIGraph.grdMonthly.Text & _
" - " & Format(frmMDIGraph.chtMonthly.GraphData, "$#,##0")
intPoint = intPoint + 1
End If
Next intCtr
DoEvents 'Let all above occur before showing form
'Open the Pie chart MDI child window
frmMDIPieChart.Show
DoEvents
fInhibitResize = False
'Display the graph
frmMDIPieChart.chtPieChart.Visible = True
End Sub
Each group of buttons on the toolbar requires a procedure to display the corresponding graph. Listing 10.9 is an example of code to display a particular graph based on the index of the push button within the group and the value of the button. Similar
code is used in the gpbGraphType_Click subprocedure, which enables you to choose the type of level 2 area chart or multiline chart to display.
Listing 10.9. The event handler and subprocedure to display graphs by class.
Private Sub gpbGraphClass_Click(intIndex As Integer, fValue As Integer)
'Purpose: Display first graph in class sequence
Dim strCode As String
Dim intCntr As Integer
'Don't do any work if the button value is false
If Not fValue Then Exit Sub
'Prevent recursion on value change
If fInhibit Then Exit Sub
fInhibit = True
'assert class button specified by intIndex
gpbGraphClass(intIndex).Value = True
For intCtr = 1 To 3 'Reset the type buttons
gpbGraphType(intCtr).Value = False
Next intCtr
strCode = LTrim(Str(intIndex)) & "1" 'Set the graph code
'Required due to lack of Public database objects
DisplayNewGraphClass strCode
DoEvents
'Show the form, in case another form is on top
frmMDIGraph.Show
DoEvents
fInhibit = False 'Reset the inhibit flag
End Sub
Sub DisplayNewGraphClass(strCode As String)
'Purpose: Test to see if the graph is different
'Note: Must be in a module to recognize tblGraphs as a Recrodset object
'Don't reprocess the current graph
If tblGraphs("Code") <> strCode Then
tblGraphs.Seek "=", strCode
If Not tblGraphs.NoMatch Then
'Disable the unused buttons for the class
DisableTypeButtons
'Get the graph data
If DrawMonthlyGraph() Then
'Draw the graph on the form as a Windows metafile
DoEvents
End If
End If
End If
End Sub
The event handlers for the navigational code, associated with push buttons and menu choices, appear in the frmMDIToolbar form.
The submenu choices of the View menu of frmMDIToolbar constitute a menu array whose index values correspond to the values of the Code field of tblDefinitions. The mnuView_Click procedures combine the functions of gpbGraphClass_Click and the
gpbGraphType_Click subprocedures to display the proper area chart or line graph. The DisplayNewGraphClass subprocedure is located in Graphs2.bas, because the subprocedure uses a Recordset object (tblGraphs) that is declared by a Dim statement in the
Declarations section of Graphs2.bas.
This chapter provided you with an introduction to the design of graph- and chart-based decision-support applications, and it described a typical database table used to define the graphs and charts. The graph and chart definitions table was used, in
conjunction with rolled up summary data, to create a set of graphs for the Graphs sample application. An MDI parent form to contain navigation buttons and menu choices was developed, and then an MDI child form was designed to accommodate the graph and grid
objects that display the data.
Of related interest, Chapter 12 shows you how to use third-party Grid controls to display data in tabular format.
The next chapter deals with printing data contained in database tables. Visual Basic 4.0 doesn't have the built-in report design capabilities of Access. Until Microsoft adds their own report generator to Visual Basic, plan on using the Crystal Reports
add-in report-printing application, which is included with the Professional and Enterprise Editions of Visual Basic 4.0.
