Share via

Visual Basic Concepts

  • Article

Adding a Function Control to the Data Report

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.

  1. 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.

  2. 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.

  3. Repeat step 6 with the Label control.

  4. Position the controls on the designer to resemble the figure below:

  5. 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

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

  2. 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.
  1. Save and run the project.

Step by Step

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

To See
Go to the next step Grouping Information in the Data Report
Start from the beginning Creating a Simple Data Report