Advanced Database – Format arithmetic calculation controls in a report
We are going to use the query that we created in a previous tutorial.
The query is shown to the left, it calculates the total hours worked on each job for each member of staff.
Then the query calculates and displays the average number of hours worked for each staff member.
We are going to create a report to display this data.
Create a report in wizard mode.
Add both fields from the query.
Click Finish as we are not too concerned with the layout.
Base will generate and open a report document in Writer.
The report displays the staff number and the average of the total hours.
It gives the average rounded to 2 decimal places.
We may want to change this to 1 decimal place.
Also, we probably want to change the column title from Total Hours to Average Total Hours to represent the data more accurately.
Open the report in design view by right clicking and selecting edit.
Right click on the text box that displays ‘=Total Hours’ and select Properties
Click the General tab, then click the ‘…’ symbol next to Formatting
Here you can change how the data is displayed. Select Category->Number then in Options change Decimal places to 1. Click OK.
Now right-click on the Total Hours title text box and select properties.
In the Label section, change the text to: Average Total Hours.
Save and close the report.
Generate the report by double clicking it in the Reports section of the main database window.
Notice that the new report displays the revised column title and rounds the values to 1 decimal place.
The same method can be used to control the way percentages, currency, etc. are formatted and displayed in a report.