Creating Reports with Data Report Object

Data Report Designer allows you to create reports. The reports generated through Data Report Designer take data from data sources such as Data Environment. Using Data Report Designer you can do the following:

¨         Export report to HTML file or text file

¨         Create report with groups and aggregates

¨         Create a hierarchical report

Components of Data Report Designer

The following are the various parts of Data Report Designer.

DataReport object

This is like a Visual Basic form. It contains the visible designer and code.  Code is used to control report at runtime by responding to the events generated by the data report.  Visible designer allows you to design the layout of the report.

Sections Collection

This is a collection of section objects. A section object represents a section of the report.  You can use section objects at runtime to reconfigure the section. At design time each section is represented by a header, which is used to select the section. 

Data Report Controls

Data report can contain only a set of special controls.  When you are in Data Report Designer, a different toolbox is displayed with controls that are specific to Data Report.

Note: You cannot use Visual Basic's intrinsic controls, or any ActiveX controls, on the Data Report designer.

Sections of the Data Report Designer

The following are the sections in Data Report Designer. Each section contains a set of values to be displayed.

Section	Description
Report Header	Contains the text that appears at the very beginning of a report, such as the report title, author name, or database name. If you want the Report Header to be the first page in the report, set its ForcePageBreak property to rptPageBreakAfter.
Page Header	Contains information that goes at the top of each printed page, such as the report's title.
Group Header/Footer	Contains a repeating section of the data report. Each group header is matched with a group footer. The header and footer pair is associated with a single Command object in the Data Environment designer.
Detail	Contains the innermost repeating part (the records) of the report. The detail section is associated with the lowest-level Command object in a Data Environment hierarchy.
Page Footer	Contains the information that goes at the bottom of each printed page, such as the page number.
Report Footer	Contains the text that appears at the very end of the report, such as summary information, or an address or contact name. The Report Footer appears between the last Page Header and Page Footer

Table 21.1: Sections of Data Report object.

Creating a Simple Report

Now, let us create a sample report to understand the steps involved in creating a simple report. For any report that is generated using Data Report Designer, you have a few important steps.

¨         Add Data Report Designer to project

¨         Establish a connection to database and create a command using Data Environment designer.

¨         Change DataSource and DataMember properties of Data Report object.

¨         Place fields on the Data Report Designer in the required sections.

¨         Invoking the report using Show method.

The following steps illustrate how to create a simple report using Data Report Designer and Data Environment.

The report displays the list of products from Products table of Products.mdb.

1.      Start a new project by selecting File menu and New Project option.

2.      Select Data Project in New Project dialog box.

3.      Visual Basic creates a new project with a form, Data Environment and Data Report. As shown in figure 21.1

4.      Invoke Data Environment Designer and select Connection object.

5.      Click on right button and invoke Properties of the Connection object.

6.      Select Microsoft Jet 3.51 OLE DB Provider as the provider.

7.      Select Connection tab and select “D:\VBBOOK\PRODUCTS.MDB” as the database. Please note that in your system the path may be different. So choose whichever path is appropriate to your system.

8.      Rename Connection object to cnnProducts.

9.      Select popup menu of cnnProducts and select Add Command to add a command object.

A Command object with the name command1 will be created.

10.  Select properties of command object and change the following properties.

Command Name                      comProducts

Database Object                      Table

Object Name                            Products

 

Figure 21.1: Data Project's project explorer.

Placing fields on Data Report Designer

After command object is created, place the fields in the command object on Data Report Designer. The following are the required steps to do that:

1.      Open Data Report Designer by double clicking on DataReport1 in Project Explorer.

2.      Invoke properties window using F4 when you are in Report Designer

3.      Change the following properties of Data Report Designer.

DataSource                        DataEnvironment1

DataMember                     comProducts

4.      Arrange Data Report Designer and Data Environment Designer in such a way that you can see both of them. See figure 21.2.

5.      Drag fields from comProducts command object into Detail section Data Report Designer.

Or

Drag comProducts command object from Data Environment into Detail section of Data Report Designer.

Note: In either of the above cases, the fields are placed along with headings. If you want to turn this option off then follow these steps:

1.      In Data Environment Designer, select Data Environment object.

2.      Click on right button and select Option menu from popup menu.

3.      Select Field Mapping tab in Options dialog, then uncheck Drag and Drop Field Captions.

Arranging fields in Data Report Designer

1.      If field headings are also placed in Data Report Designer then we have to move them to Page Header section.

2.      If field headings are not copied then create labels in Page Header section using RptLabel control.

3.      When you are dealing with Data Report Designer you get DataReport toolbox. RptLabel is a control available in DataReport toolbox.

4.      Whether you got labels (headings) automatically or you manually added, change the caption property of each label as follows.

Field	Heading
Prodno	Product Number
Prodesc	Product Description
Qoh	Quantity On Hand
Rpu	Rate Per Unit
Ptype	Product Type

5.      Select all labels and change Font Name to Arial, Bold to True, and Size to 10.

6.      Change Alignment property of all heading labels to 2-rptJustifyCenter.

7.      Increase the Height of all the labels to 450 units.  This is done to ensure that the heading will wrap to next line if it cannot be accommodated in one line.

8.      Reduce the size of Detail section to eliminate extra space below fields. 

9.      Change Alignment property of QOH and RPU fields to 1-rptJustifyRight.

10.  Change Alignment property of PRODNO and PTYPE to 2-rptJustifyCenter.

Running the Report

To invoke report you can make either the report the startup object or you can invoke report using Show method of the data report object.

Making report the startup object will be easier while you are designing report.  But final reports are always invoked either by user selecting an option in menu or by clicking on a command button.

Let us see how to invoke report using a command button.  As you have seen, when you selected Data Project, Visual Basic has created  a Form, Data Environment, and Data Report.  So invoke Form designer and place a command button. Change the following properties of command button.

Name               cmdProductlist

Caption                        &Products List

And write code to invoke report in click event of the command button.

Private Sub cmdproductlist_Click()

  DataReport1.Show

End Sub

Listing 21.1: Code to invoke data report.

 

Figure 21.2: Placing fields from data environment to data report object.

Note: You can also make DataReport the Start up object of the project to start project with Data Report.

1.      Run the project using F5.

2.      When form is displayed, click on  command button to invoke report.

3.      At runtime the report should look like figure 21.3.

Figure 21.3: Data Report at runtime.

Improving Appearance of the report

The report shown in figure 21.3 is O.K but not good. We can modify the report to make it more readable. Also observe, it wastes a lot of space at the top and on the left.  So let us see how to modify the report.

1.      Invoke Properties window from Data Report Designer and select properties for DataReport1.

2.      Change the following properties.

Left Margin                        100

Right Margin                     100

Top Margin                       100

Bottom Margin                  100

3.      Place a line control at the bottom of the headings to separate heading from details.

4.      Change BorderStyle property to 1- vbFixedSingle.

Adding  Report Title and Report Footer

A report can contain a title, which is printed at the top of the report and a report footer, which is printed at the bottom of the report.

To add report title and report footer sections to Data Report Designer:

1.      Click right button of mouse on Data Report Designer.

2.      Check Show Report Header/Footer option on popup menu.

3.      Data Report Designer adds two more sections – Report Header and Report Footer.

To add a title to the report:

1.      Select Report Header section and place a label control in it.

2.      Change the Caption of the label to “Products List”

3.      Change Font related properties to increase the size of the title.

To add current date and page number:

1.      Select Report Header section and click on right button.

2.      From popup menu select Insert controls -> Current Date(long format)

      A label is placed with Caption property set to %D (which stands for date long format).

3.      Place a label on the left of it and change it’s Caption property to “Date:”

4.      Click right button on the data report again and select  Insert Controls -> Current Page Number.

Another label is placed with caption %p (which means current page number). You can also select Total Number of Pages; which means a label with caption %P (capital P).

5.      Place a label on the left and change caption to “Page:”

6.      Change Font Bold setting of “Date:” and “Page:” labels to True.

Arrange all labels and fields in a neat format and then run the report (click on Products List command button). Your report should look like the one shown in figure 21.4.

 

Figure 21.4: Data Report after formatting.

Exporting Report

A report generated using Data Report can be exported to either HTML document or any text file.  Once you export report to HTML file, the report can be viewed in any browser (such as Internet Explorer) that supports HTML.

The following procedure shows how to export a report to HTML file:

1.      Run the report that we have created by clicking on Products List button.

2.      When report is run, click on Export button at the top of the report in toolbar.

3.      Select the type of file to which you want to export the report using Save as type drop-down list box.(figure 21.5)

4.      Enter report as the name of the file into which report is to be exported.

5.      Click on Save button.

This completes the process of exporting the report. Now a file with the name “report.html” is created.

 

Figure 21.5: Export dialog box used to export report.

Displaying report using Internet Explorer

Take the following steps to display the HTML file created using Export dialog in Internet Explorer.

1.      Invoke Internet Explorer

2.      Select File -> Open and select the report.html form the directory where you saved it. Use browse button to invoke Open dialog box, if required.

3.      After selecting filename click on OK button.

4.      Internet Explorer displays the report that was exported to the file, report.html. Figure 21.6 shows report loaded into Internet Explorer.

Figure 21.6 : Exported data report in Internet Explorer.

Creating a Master-Detail report

We have seen how to generate a simple report in the previous section. Now let us create a slightly complex report that contains the following additional feature:

¨         Getting data from more than one table

¨         Grouping data

¨         Displaying aggregates

The report that we are going to create is a Sales report. It displays the details of the product at the top followed by details of the sales for that product. We take data from products table for product number and product description and details of sales from Sales table.

The following are the major steps involved in creating this report. These changes are to be made to existing Data Environment and Data Report.

¨         Creating comSales (based on SALES table) child command object of comProducts object in Data Environment.

¨         Adding sum of amount (qtysold * rpu) to comSales command object.

¨         Creating a  data report and arrange data in data report

Adding child object

Let us add a child object to comProducts command object of Data Environment. In this case, comSales (child object) and comProducts (parent object) are joined using common field – PRODNO.

The following are the steps to create child object:

1.      Select comProducts command object and click on right button.

2.      Select Add Child Command from popup menu.  A child command object is added.

3.      Invoke child command object’s properties and change name to comSales.

4.      Select SQL Statement radio button in Source of data group and enter the query shown in figure 21.7.

5.      Select Relation tab. Select PRODNO the field to be used from parent and child object to be used and click on Add button to establish relationship between comProducts and comSales based on prodno.

6.      Click on OK button to dismiss properties dialog of child object.

7.      Invoke properties of comProducts and select Aggregates tab.

8.      Click on Add button to add an aggregate.

An aggregate is added. Change the settings of aggregate as show in figure 21.8.

We have completed the process of creating a child command object that takes the details of sales. Now let us concentrate on creating report using data report.

.

Figure 21.7: Properties of comSales object.

Designing report using Data Report Designer

We have to add a new Data Report object to current project and take data from comProducts and comSales command objects. The steps involved in handling report data is as follows.

1.      Add a new data report to project using Project -> Add Data Report option

2.      Change the following properties of Data Report.

Name                     drSales

DataSource                       DataEnvironment1

DataMember         comProducts

3.      Click on right button on Data Report and select Retrieve Structure from popup menu.

4.      Tile Data Environment Designer and Data Report Designer vertically and drag fields into sections as follows.

Section	Field
comProducts_header	Prodno
	Proddesc
comSales_details	Invno
	Qtysold
	Rpu
	Expr1004
ComProducts_Footer	ProdSales

5.      Arrange field headings in comProducts_header section.

Figure 21.8: Creating aggregates using Aggregates tab.

Formatting RPU, AMOUNT and ProdSales

As  fields rpu, amount and prodsales display amount they are to be formatted.  We will include currency symbol ( Rs.), thousands separator, and two decimal places.

To format RPU field:

1.      Select RPUfield in Data Report Designer.

2.      Invoke properties window and select Data Format property.

3.      When Property Page for Data Format is displayed (figure21.9) change properties as shown in figure 21.9.

4.      Repeat the process for AMOUNT (EXPR1004), and ProdSales.

5.      Change headings for fields as shown in figure 21.10.

6.      Change Alignment property of all heading to 2-rptJustifyCenter.

7.      Add three lines, above and below product details and one above total (see figure 21.10).

8.      Change margins of the drSales according to the requirement.

Figure 21.9: Formatting number using Format property page.

           

9.      After all changes are made, add one more command button to form and change the following properties of the command button.

Name                     cmdsalesreport

Caption                  &Sales Report

10.  And write the following code for click event. If user clicks on this button, the report shown in figure 21.10 is displayed.

Private Sub Command1_Click()

   drSales.Show vbModal

End Sub

Figure 21.10: Master-detail report at runtime.

Events of Data Report object

The following are the events of the data report object.

Like the standard Visual Basic form, the life of a Data Report designer is marked by certain key events. Those events and the order in which they occur are shown in the following table:

Event	Description
Initialize	Occurs after the query has completed and controls are sited on the form.
Resize	Occurs when the designer is first displayed or when the window state of the data report object changes.
Activate	Occurs when the designer becomes the active window.
ProcessingTimeout	Occurs approximately once every second until all processing has ended. Use this event to determine if processing has taken too long, and to cancel the processing. Note: This event will not occur until the query has completed. See below.
Deactivate	Occurs when the designer is no longer the active window. Use this event to determine if the user has clicked another form or designer.
QueryClose	Occurs before the designer is terminated. Set the Cancel argument to True to cancel termination. The CloseMode argument returns the type of action that is causing the termination.
Terminate	Occurs when number of references to the designer is set to 0.

Table 21.3: Events of Data Report object.

Methods of Data Report object

The following are the specific methods of the DataReport object.

Method	Meaning
ExportReport	Exports the text of a report to a file using a specified ExportFormat object.  If total data required is not supplied then Export dialog box is displayed. (figure 21.5).
PrintReport	At run time, prints the data report created with the Data Report designer. You can specify which pages are to be printed by giving page range.
Refresh	Refreshes the report.

Table 21.4: Methods of Data Report object.