Records can be calculated in a report by grouping values based onspecific fields. Calculated fields can be included in a report. You canwork out the total sum of the amount received, the maximum and minimumvalues, the averages, or the count of a number of items. The calculationthat is performed determines where the calculated field will bepositioned in the report.
Here are some examples:
|
=sum([amt_received]) |
Calculates the sum of the received payments from parents |
|
=average([salary]) |
Calculates the average salary |
|
=maximum([salary]) |
Displays the highest salary |
|
=minimum([salary]) |
Displays the lowest salary |
|
=count([surname]) |
Counts the number of students |
|
=[salary]*12 |
Multiplies the value by 12 |
|
=[amt_received]/12 |
Divides the amount received by 12 |
|
=[salary]+[bonus] |
Adds the salary to the bonus |
|
=[salary]-[bonus] |
Subtracts the salary from the bonus |
When using the preceding calculations, note the following:
- Brackets are extremely important, just as they are when used inExcel to construct formulas. Just like in Excel, we use square bracketsto identify field names on a report.
- Field names are case sensitive.
- Field names must be typed exactly as they appear in the originalfield.
- Uppercase, lowercase, and spaces must correspond.
Let’s go through an example of calculating a report together:
- Open a report to edit in Designview. We will use the StudentsRPT report created inthe previous example.
- Create a textbox in the report’s footer:
- Once the Text Box control has beeninserted onto the report, the two textboxes may appear to overlap:
- Separate the Text Boxcontrol by dragging the unbound box away from the label. Change the nameof the Text label to Number ofPayments. Resize if necessary:
- Click on the Unbound control andtype =count(*). Change the font, if necessary, using theFormat tab on the top ribbon.
- Preview your report to check the changes. Then, change back to
Design view. - Edit your report’s footer to containa =sum([AMT_RECEIVED]) function to calculate the total amountof fee payments received per student:
- Change the view to Report View.Note that the calculations are visible at the very end of the report inthe footer section:
In the next section, we will look at formatting values to display thecurrency format on a report.