Querying & Reporting: Reporting with Visual Basic 6.0

Visual Basic (VB) 6 introduced the new Data Report Designer as a way to create reports from within VB. This reporting tool increases developers' control over report execution. It's not as glitzy as some third-party products, but developers need functionality, not glitz, and Data Report Designer has the power of VB behind it.

The Data Report Designer supports hierarchical data commands that return a recordset containing a child recordset, which is related to the parent by a key. You can create reports from these recordsets and perform grouping operations. Hierarchical data commands are based on the hierarchical cursor feature in ADO 2.0 and the Data Environment Designer.

Let's examine Data Report Designer. First, it doesn't hook directly into a database table or use SQL to pull its data. Instead, each report has a DataSource property that lets you point at an object that will return an ADO recordset, allowing different data sources to feed data into one report.

To create the reports, you use the Data Report Designer interface, which uses a traditional banded report writer format, as you see in Screen 1. Each report part (e.g., headings, footers, and details) appears in a separate band of the report. The bands in Screen 1 are labeled Page Header, Detail, and Page Footer and change as you add groups that cause repeating sections in the data. For example, if you group the information to display customer order data for individual customers, Data Report Designer will add a Group Header and Group Footer. The Page Header and Page Footer bands contain each page's header and footer. Additionally, you can create a separate header and footer for the entire report by adding a Report Header and Report Footer.

Usually the innermost band in the report, the Detail band does most of the work and repeats for each detail record in the recordset. If you have several grouping bands, the Detail band sits in the middle and represents the output data.

In design mode, the Data Report Designer works similarly to a VB form (i.e., after adding a report to a project, you can edit the Data Report Designer report or use the Properties window to change the report's properties). Also, the Data Report Designer adds a special DataReport tab to the Toolbox. This tab contains controls that work only in reports. You can link the data-bound controls to the recordset that drives the report. However, only during the design phase can you set the other controls, which add information or design to the report.

You can add as many Data Report Designers to a project as you need. Each Data Report Designer represents one report and shows up in the Designers folder in Project Explorer.

To add a Data Report Designer to your project, select Add Data Report from the Project menu. If the Designer folder doesn't exist, this command will create it and put a Data Report Designer in it. You can open the Data Report Designer by double-clicking the report name in the Designer's folder.

Creating a Report

Before you create a report, create the report's DataSource. The DataSource can be an ADO recordset, data class, or Data Environment data command. In this example, I used the Pubs sample database that ships with SQL Server. I added a Data Environment to the project, then added a data connection to the Pubs database. Next I created a new data command (Authors) and set its SQL text (CommandText property) to:

SELECT au_id, au_lname, au_fname, phone,address, city, state, zip, contractFROM authors ORDER BY au_lname, au_fname

Close the command to update the properties. Now, add a report to your project (Project, Add Data Report). Next, click the report name in Project Explorer and press F4 to open the report's properties. Now, set the report's properties that determine its interaction and appearance. For this example, we'll change only the most frequently used properties.

First, give the report a descriptive name such as rptAuthors, and select the Data Environment containing your data command as the DataSource. This section will link the report to the Data Environment. Next, select the data command (Authors) for the DataMember property. These steps are necessary to link the report to a data source. Next, enter a report title string (Author Listing) in the Caption property. The other default values will work until you begin to fine-tune your report.

Now, save the project. The report file will default to the same file name as its name in the project. The extension for the file is .dsr, which is used for all designers. To avoid conflicts with other designers, such as the Data Environment, prefix your reports with a standard naming convention such as rpt.

Next, open the report designer by double-clicking its name. When you assign a data command to the report, set up the report's default structure to match the recordset that the command returns. Right-clicking the report designer and selecting Retrieve Structure from the shortcut menu is the easiest way to achieve this match. If you've already created a report, don't use this command, because it'll recreate the structure and destroy your settings. When the command finishes, the report's structure—Page Header, Detail, and Page Footer bands—will match your command. For this example, that structure consists of Page Header, Detail, and Page Footer bands.

Now, let's create the report. This process is like building a VB form. You drag the special Data Report controls onto the Data Report Designer for the report you're working on. Your data command provides the easiest way to add controls to the report. Open the Data Environment containing the data command the report uses. Size the window so that you can see the Data Environment Designer and Data Report Designer at the same time. To add label and textbox controls for each recordset field that the data command returns, drag and drop the data command onto the Detail area of the Data Report Designer. The format will resemble a table, but you can quickly change that format.

Drag the first field's label to the Page Header, and drop it into the position where you want the field. Now, drag the textbox that goes with that label and drop it under the Detail section. Perform this step for each field in the recordset. Using this technique, I created the report I designed in Screen 1. Notice how the header fields line up with the detail fields.

Before you continue with this report work, you can add code to the application to test it. Add a form to your project, then add a Command button and name it cmdAuthorReport. Double-click the command button you just added to open the code editor. In the Click event for the button, add this code:

rptAuthors.Show

The report object's Show method will open the report in a preview window. Screen 2 shows the completed sample report (rptAuthors). The report window lets users review the report, print it, and export it to an htm or ASCII file.

You can print the report directly by calling the PrintReport method, which takes several parameters. If you execute the method with no parameters, a dialog will prompt you for information about the report. The parameters are:

In addition to PrintReport, you can call the ExportReport method to export the report to a file. Also, you can pass this method parameters to alter its behavior.

So far, you can see how the report creates simple layouts and hooks them into your application. The Image, Line, and Shape controls let you add graphic features to a report. I added the lines under the field titles in Screen 2 to the Page Header just below the fields. Then I copied this line to the clipboard, pasted it into the Detail area, aligned it just under the fields, and sized the bottom of the Detail area to just below the line. These two lines generated the results seen in Screen 2.

The Data Report Designer is an important new reporting tool for database applications. It's easy to set up, but lets you produce well-designed reports. The report object also lets you control report display or printing, or export reports from your code to htm or Text files, making the reports usable in numerous situations.