Visual Basic Concepts

Creating a Simple Data Report

This topic creates a simple data report using a Data Environment designer as a data source. The Data Environment designer uses the NorthWind database supplied with Visual Basic to create a simple hierarchical cursor. The cursor contains two tables, Customers and Orders, and uses the CustomerID field to link the two. The finished report resembles the figure below.

Simple Data Report: Order Dates by Customers

Before you begin the step-by-step process, ensure that the Northwind database (Nwind.mdb) is present on your computer. If it is not present, copy the file from your Visual Basic CD onto your hard disk.

To create a simple hierarchical cursor in the Data Environment designer

1. Create a new Standard EXE project.

2. On the Project menu, click Add Data Environment to add a designer to your project. If the designer is not listed on the Project menu, click Components. Click the Designers tab, and click Data Environment to add the designer to the menu.

   Note   The first four kinds of ActiveX designers loaded for a project are listed on the Project menu. If more than four designers are loaded, the later ones will be available from the More ActiveX Designers submenu on the Project menu.

3. On the Data Link Properties dialog box, click Microsoft Jet 3.51 OLE DB Provider. This selects the correct OLE DB provider for accessing a Jet database.

4. Click the Next button to get to the Connection tab.

5. Click the ellipsis button (…) next to the first text box.

6. Use the Select Access Database dialog box to navigate to the nwind.mdb file, which is installed in the Program Files\Microsoft Visual Studio\Vb98 directory.

7. Click OK to close the dialog box.

8. Right-click the Connection1 icon, and click Rename. Change the name of the icon to Northwind.

9. Right-click the Northwind icon, and then click Add Command to display the Command1 dialog box. In the dialog box, set the properties as shown below:

10. Click OK to close the dialog box.

11. Right-click the Customers command, and click Add Child Command to display the Command2 dialog box. In the dialog box, set the properties as shown below:

12. Click the Relation tab. The Relate to a Parent Command Object check box should be checked. The Parent box should contain Customers; both the Parent Fields and Child Fields/Parameters boxes should contain CustomerID.

    When designing relational databases, it's customary for related tables to use the same name for linking fields. In this case, the linking fields are both named CustomerID. The Data Environment designer automatically matches such pairs in the dialog box.

13. Click Add. Click OK to close the dialog box.

    Clicking the Add button adds the relation to the Command object. After closing the dialog box, the Data Environment designer reflects the relationship by displaying the two commands as a hierarchy. This hierarchy will be used to create the data report.

14. Set the properties of the project and designer according to the settings below, then save the project:

Creating the Data Report

Once the Data Environment designer has been created, you can create a data report. Because not all of the fields in the data environment will be useful in a report, this series of topics creates a limited report that displays only a few fields.

To create a new data report

1. On the Project menu, click Add Data Report, and Visual Basic will add it to your project. If the designer is not on the Project menu, click Components. Click the Designers tab, and click Data Report to add the designer to the menu.

   Note   The first four kinds of ActiveX designers loaded for a project are listed on the Project menu. If more than four designers are loaded, the later ones will be available from the More ActiveX Designers submenu on the Project menu.

2. Set the properties of the DataReport object according to the table below:

3. On the Properties window, click DataSource and then click deNwind. Then click DataMember and click Customers.

   Important   To set the DataSource property to deNwind, the Data Environment designer must be open. If it is closed, press CTRL+R to display the Project window, then double-click the data environment icon.

4. Right-click the Data Report designer, and click Retrieve Structure.

   You have added a new group section to the designer. Each group section has a one-to-one correspondence to a Command object in the data environment; in this case, the new Group section corresponds to the Customers Command object. Notice also that the Group Header has a matching Group Footer section.

   Note   The Data Environment allows you to create hierarchies of Command objects wherein a Command object has more than one child object — child Command objects parallel to each other. The Data Report designer, however, is not as flexible, and can't display more than one child object at a time. In such cases, when executing a Retrieve Structure command, the Data Report will display only the first of the child commands, and none below it. Thus you should avoid creating Command hierarchies with parallel children commands.

5. From the Data Environment designer, drag the CompanyName field (under the Customers command) onto the Group Header (Customers_Header) section.

   The Group Header section can contain any field from the Customers command, however, for demonstration purposes, only the Customer name is displayed at this time.

6. Delete the Label control (rptLabel) named Label1.

   If you do not want a Label control to be included with the TextBox control, you can uncheck the Drag and Drop Fields Caption option on the Field Mapping tab of the Data Environment designer's Options dialog box.

7. From the Data Environment designer, drag the OrderDate field (under the Orders command) onto the Details (Orders_Detail) section. Delete the Label control.

   The Details section represents the innermost "repeating" section, and thus corresponds to the lowest Command object in the Data Environment hierarchy: the Orders Command object.

8. Resize the Data Report****designer's sections to resemble the figure below:

   

   It's important to resize the height of the Details section to be as short as possible because the height will be multiplied for every OrderDate returned for the CompanyName. Any extra space below or above the OrderDate text box will result in unneeded space in the final report.

9. Save the project.

Preview the Data Report Using the Show Method

Now that the data environment and the data report objects have been created, you are almost ready to run the project. One step remains: to write code to show the data report.

To show the data report at run time

1. On the Project Explorer window, double-click the frmShowReport icon to display the Form designer.

2. On Toolbox, click the General tab.

   When you add a Data Report designer to your project, its controls are added to the tab named DataReport. To use the standard Visual Basic controls, you must switch to the General tab.

3. Click the CommandButton icon and draw a CommandButton on the form.

4. Set the properties of the Command1 control according to the table below:

5. In the button's Click event, paste the code below.

   Private Sub cmdShow_Click()
       rptNwind.Show
   End Sub
   

6. Save and run the project.

7. Click Show Report to display the report in print preview mode.

Optional—Setting the Data Report as the Startup Object

You can also display the data report with no code at all.

1. On the Project menu, click prjNwind Properties.

2. In the Startup Object box, select rptNwind.

3. Save and run the project.

   Note   If you use this method, you can remove the Form object from your project.

Step by Step

This topic is part of a series that walks you through creating a sample data report.