Question

All replies

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:

Draw a Function control in an
appropriate Footer section of the Data Report designer.

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

Using the mouse pointer,
click the group footernamed Customers_Footer, and drag it
towards the bottom of the window, to create a space between it and the Orders_Footer
footer.

On the Toolbox, click
the rptFunction control.

Draw the rptFunction
control in the space between the two footers.

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

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

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.

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.

Repeat step 6 with the Label
control.

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

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

Draw a new Function control
in the Customers_Footer section. Align the new control with the
Function control created above.

Draw a Label control
to the left of the new Function control.

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

The Data Report designer
should now resemble the figure below:

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

Right-click the Data Report
designer and click Show Report Header/Footer.

Add a Function control
to the report footer.

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

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

Save and run the project.

Step by Step

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