Adding a Function Control to the Data Report

See Also

The Data Report designer features its own set of controls. Among these, the Function control has no counterpart among the Visual Basic intrinsic controls and deserves further explanation.

The Function control displays data that is calculated at run time, using a built-in function, as the report is generated. A typical example is shown in the figure below, where the Function control is used to display the sum of sub-totals to create a total value for a particular order.

If you have created a calculated field using a SQL statement (in the previous topic, "Adding a Calculated Field to the Data Report"), you may wonder why a Function control can't also be used to calculate the value of Quantity * UnitPrice. In short, the Function control can calculate values only after all other records in a group section have been processed. In contrast, a SQL statement calculates the values as part of the fields in a record as they are processed.

The steps to adding a Function control to the Data Report designer are:

1. Draw a Function control in an appropriate Footer section of the Data Report designer.
   
   
2. Set the DataMember and DataField properties to appropriate values (a numeric field from a relevant data environment Command object.)

Data Report with Function Control Showing Totals

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 Function control to the data report

1. Using the mouse pointer, click the group footer named Customers_Footer, and drag it towards the bottom of the window, to create a space between it and the Orders_Footer footer.
   
   
2. On the Toolbox, click the rptFunction control.
   
   
3. Draw the rptFunction control in the space between the two footers.
   
   
4. Set the properties of the rptFunction control according to the table below:

   Property	Setting
   DataMember	OrderDetails
   DataField	Total
   Name	fncTotal
   Alignment	1 – rptJustifyRight
   DataFormat	Currency

   
   

   The FunctionType property of the control determines what operation will be performed with the data found in the DataField. By default, the property is set to 0-RptFuncSum, to sum the data. Other functions include: Average, Minimum, Maximum, Row Count, STDDev (Standard Deviation), and Value Count.

5. Draw a Line control just above the Quantity text box, and a Label control to the left of the text box. Set the Caption property of the label to Total.
   
   
6. Click the fncTotal control to select it. On the Properties window, double-click Font to display the Font dialog box. Change the Font style to Bold.
   
   
7. Repeat step 6 with the Label control.
   
   
8. Position the controls on the designer to resemble the figure below:

   

9. Save and run the project.

Increasing the scope of the function

You can increase the scope of the function by placing the control in a lower group footer. For example, you may want to find out the grand total of each customer's set of orders.

To show a grand total for all orders

1. Draw a new Function control in the Customers_Footer section. Align the new control with the Function control created above.
   
   
2. Draw a Label control to the left of the new Function control.
   
   
3. Set the properties of the controls as shown in the table below:

   Control	Property	Setting
   rptFunction	Name	fncGrandTotal
   rptFunction	DataMember	OrderDetails
   rptFunction	DataField	Total
   rptFunction	Font (Font Style)	Bold
   rptFunction	Alignment	1 – rptJustifyRight
   rptLabel	Caption	Grand Total
   rptLabel	Font (Font Style)	Bold
   rptLabel	Alignment	1 – rptJustifyRight

   
   
4. The Data Report designer should now resemble the figure below:

   

5. Save and run the project.

Optional—Adding a Report Total

It's possible to increase the scope of the Function control even further. If you wish to see a total of all orders in the report, you can add a Function control to the Report Footer section.

To add a Report Total to the data report

1. Right-click the Data Report designer and click Show Report Header/Footer.
   
   
2. Add a Function control to the report footer.
   
   
3. Set the property of the control as shown in the table below:

   Property	Setting
   Name	fncReportTotal
   DataMember	OrderDetails
   DataField	Total
   DataFormat	Currency
   Alignment	1 – rptJustifyRight

   
   

   Note   The number for the entire report will be especially large. Therefore the width of the Function control will have to be adjusted accordingly.

4. Save and run the project.

Step by Step

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