Visual Basic Concepts

Adding a Calculated Field to the Data Report

See Also

A calculated field is a field whose value is calculated as the report is generated. For example, when estimating the tax on an order, you must multiply the total price by the local tax rate. But since local tax rates differ and won't be stored in the database, the tax is generated as the report is created—a calculated field.

In this example, the extended Data Report designer is modified to include a Total field that calculates the value of Quantity * UnitPrice. Adding a calculated field involves the following steps:

1. Using a SQL statement in the data environment's Command object to create the calculated field.
   
   
2. Adding three text box controls to the data report: Quantity, UnitPrice, and (for the calculated field) Total.

The modified data report resembles the figure below.

Note   This topic is part of a series that walks you through creating a sample data report. It begins with the topic, Creating a Simple Data Report.

To add a calculated field to the data report

1. In the Data Environment designer, right-click the OrderDetails command. Then click Properties to display the OrderDetails Properties dialog box.
   
   
2. On the General tab, click the SQL Statement button, and add the following statement to the box:

   Select OrderID, ProductID, UnitPrice, Quantity, (Quantity * UnitPrice) As Total From [Order Details]
   

   The SQL statement multiplies the Quantity value by the UnitPrice value to create the Total value—the calculated field. Also note that the name of the table (Order Details) contains a space, and must be enclosed by brackets.

3. Click OK to close the dialog box.
   
   
4. From the Data Environment designer, drag the Quantity, UnitPrice, and Total fields (under the OrderDetails command) onto the Detail (Products_Detail) section of the Data Report designer.
   
   
5. Delete two Label controls, and change the Caption value of the remaining Label control to *, and arrange the controls to resemble the figure below:

6. On the Data Report designer, click the UnitPrice text box to select it. On the Properties window, double-click DataFormat to display the Property Pages dialog box.
   
   
7. In the Format Type box, click Currency. In the Symbol combo box, select the currency appropriate to your country.
   
   
8. Repeat steps 6 and 7 to change the DataFormat property of the Total text box to Currency.
   
   
9. Click the Total text box control to select it. On the Properties window, change the Alignment property to 1 – rptJustifyRight.
   
   
10. Save and run the project.

Step by Step

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