• What's New with Reporting?
• Building a Simple Report

What's New with Reporting?

Creating reports is a main function of any good business application. You might have useful data in the system, but without a coherent way to present it, the numbers are meaningless. Visual Basic's primary reporting tool wasn't even a Microsoft product until this version. Previous versions of VB included a version of Seagate Software's Crystal Reports tool. With the release of VB 6, Microsoft has integrated a good report writer into VB that will provide most users an alternative to purchasing another tool.

The VB Data Report Designer will look familiar to developers who have built reports in Microsoft Access, because the layout of the tools is almost identical. However, the Data Report for VB can actually be compiled into your executable, which means no format files need to be installed on a user's machine. This also means that the format files can't be corrupted by users who decide to go exploring.

Your VB reporting workspace has a few new additions. The first is the Report Layout window (shown in Figure 31.1). You learn more about its different sections later in this example.

FIGURE 31.1 The Report Layout window looks similar to the Report Designer used in Microsoft Access.

The next change is the addition of six new controls to the Toolbox, as described in Table 31.1.

---

Data Report Designer ToolBox controls

These six controls aren't interchangeable with their counterparts you've always used for form design. Likewise, you can't use form design controls in Data Report Designer.

---

TABLE 31.1  Data Report Designer Controls

Control Type	Description
RptTextBox	Any data that will be supplied at runtime--through code or through a command--needs to be put in a RptTextBox. RptTextBox controls can be configured to look like plain text without any borders.
RptLine	Use this control to draw a variety of lines on the report.
RptFunction	This control performs functions on data groups in the report and can be used only in a group footer section of the report. It supplies functions such as row and value counting, sums, and so on.
RptLabel	The RptLabel control is used to add static text to the report.
RptImage	Use the RptImage control to add a picture to your report. This control, like the standard Image control, can hold bitmaps (BMP), icons, metafiles, or GIF or JPEG files.
RptShape	You can use the RptShape control to add a variety of graphical shapes to your report, including rectangles, circles, and variations of them.

Building a Simple Report

As its name implies, the Data Report Designer builds reports from database tables. The example in this chapter uses the Northwind Traders database (Nwind.mdb), which is included in the main Visual Basic directory.

Start building your report

1. From the Project menu, choose Add Data Report.

2. You need a title for your report. This title can be either in the Report Header, and only on the first page of the report, or in the Page Header and on the top of every report. Add a RptLabel control or two to create a title for your report, as shown in Figure 31.2. Simply draw the RptLabel control where you want it and set at least the Caption property to change the text that should be displayed.

FIGURE 31.2 RptLabel controls enable you to add static text anywhere on your report. The text in the Report Header section repeats on every page in your report.

---

Cleaning up the report

You can also set the Caption property of the Report itself by using the Properties window when the Report is selected. Also, any blank space you leave around the controls will be repeated whenever the report is shown, so be sure to place the controls correctly and to close up any blank space around them.

---

You can add other labels or graphics to the report as you want. The next step is to connect the report to a data source by using the Data Environment Designer. A data environment contains all the connections and queries that you're using in your application. In this example, you need a query to generate a list of customers for your report.

Using the Data Environment Designer

1. From the Project menu, choose More ActiveX Designers and then choose Data Environment.

2. To make a connection to the Northwind Traders database, right-click Connection1 and select Properties from the popup menu.

3. Because you're connecting to an Access database, select Microsoft Jet 3.51 OLE DB Provider (see Figure 31.3) and then click Next.

FIGURE 31.3 Always use the Jet OLE DB Provider when connecting to Access databases in a data environment.

4. Enter or select the database filename to use for this connection. The Northwind Traders database is located in the VB installation directory. For the other options in the window (see Figure 31.4), you don't need to specify any user ID (other than Admin) because no access control is set up on the Nwind.mdb file. If you were using another database that had security, you would have to specify a user ID and password here or at runtime.

5. To verify that the connection is correct, click the Test Connection button. If you receive a message indicating a successful test, click OK to continue. Otherwise, go back and make sure that you followed the steps correctly.

6. Because a name like Connection1 isn't useful, click the Connection1 item and hover for a few seconds. VB will allow you to rename the connection at this point. Pick a useful name, such as dcnNWind, which is the name used throughout this example.

FIGURE 31.4 Enter the filename of the database you want to use in this connection. Access always provides the Admin user ID, even if you don't specify one. For this example, you can leave Admin as the user ID.

7. Repeat step 6 and give the data environment a useful name, such as denvNWind. If you have multiple data environments in your project, it's imperative that you keep the names straight.

You've successfully created a data environment. Be sure to save your work but don't shut the window just yet. With the data environment created, you can now create the query that will retrieve customer information from the database.

Create the query to retrieve information from the database

1. Right-click Commands and select Add Command from the popup menu. The Data Environment Designer will add a new command to the environment. Right-click it and select Properties.

Quite a few properties are available for the command, but the ones you need to set are on the first page, General (see Figure 31.5).

2. Give the command a useful name, such as qryGetCustomerList.

3. Pick the connection to use. Select dcnNWind from the Connection drop-down list.

FIGURE 31.5 All the properties you need to set for this query are on the General page.

---

Use the SQL Builder

You can use the SQL Builder if you want to create complex joins and don't want to type the SQL yourself.

---

4. Because the database can understand SQL, type the following SQL Statement to specify how to retrieve the data:

SELECT CompanyName, City, Region, Country, Phone

FROM Customers ORDER BY CompanyName

5. Click OK.

Be sure to save your work because you have now finished building your data connections.

Finish building the report

1. Before adding the data fields, you need to set a few properties on the report itself so that it knows where to obtain its data. Set the DataSource property to the name of your data environment; set the DataMember property to the name of your customer list query. If you don't set these properties, you'll receive errors when you try to run the report.

---

Finding the Properties window

If you can't see the Properties window (where these properties can be set), press F4 to open it.

---

2. To add data to the report, take a field from the Data Environment Designer window, such as CustomerName, and drag it to the Detail section of your report. VB will automatically draw a RptTextBox, along with a RptLabel, control on the report.

---

Using repeating rows

The Detail section is very narrow because any blank space in this section will be repeated for every row in the report. Also notice that a line is drawn beneath the column headers. You can use the RptLine control to create this graphic, if you like.

---

3. To make this report column-based, drag the RptLabel into the PageHeader section. Place the RptTextBox beneath the RptLabel but leave the RptTextBox in the Detail section. Repeat this process for all the fields. Your window will look like Figure 31.6.

FIGURE 31.6 You can move your controls between sections to create a column-based report.

4. To include a page number and total number of pages (as in Page 3 of 5), create a page footer. This is very easy to do with a RptLabel control, which supports a number of substitutions so that you don't have to write code to put page numbers in your report. The RptLabel control supports these substitutions:

%p	Current page number
%P	Total number of pages
%d	Current date (short format)
%D	Current date (long format)
%t	Current time (short format)
%T	Current time (long format)
%i	Report title

If, for instance, you wanted to include a page number, the Caption for the RptLabel control would be as follows:

Page %p of %P

VB will automatically substitute the page number and number of pages on each page of the report.

Now that the report is complete, you can run it just like any other module in your project. Choose Properties from the Project menu and use this report as your startup form. When you run your program, your report will be displayed as shown in Figure 31.7.

---

Centering the page number

To center the page number, right-click the RptLabel, select Center in Section, and then select Horizontally from the pop-up menu.

---

FIGURE 31.7 The report is shown in its final form in run mode.

With the report viewer, users can print or export the report to plain text or HTML. As shown in Figure 31.7, you also can shrink or enlarge the report by using the Zoom feature available on the viewer.

To show this report from code, use the standard Show method. You can use the PrintReport method to instruct the report to print through code. Adding a True or False to the PrintReport call tells VB whether to display a Print dialog. You don't even have to have a CommonDialog control in your project--VB will automatically create the Print dialog.

---

© Copyright, Macmillan Computer Publishing. All rights reserved.