The ‘Insert’ menu lists several elements that can be inserted into a report, such as a database field, text or graphical objects.
Except for the first option each choice is also available via a comparable button that can be chosen from the insert button bar.
7.1 Field heading
If a database field is selected in the design window and the option ‘Field Heading’ is clicked from the ‘Insert’ menu, a text object is inserted in the 'Page Header' section. The caption is automatically assumed to be the field name. Change the caption by a right mouse click on the text object and selecting ‘Edit Text’ from the context menu.
See paragraph 4.2.1 for the default setting to automatically place a text object with caption in the 'Page Header' section when a database field is added to the report.
7.2 Summary
First select a field that needs to be summarized. Then choose ‘Sum’
from the ‘Insert’ menu. If we choose this for the field ‘qty_ordered’
from the orderdtl table we will see the dialog on the right.
The available summary options in this dialog are:
Function Description
Sum Sums the values of the field (only works on numeric fields).
Average Show the average value of the field (only works on numeric fields).
Maximum Show the maximum value of the field (only works on numeric fields).
Minimum Show the minimum value of the field (only works on numeric fields).
Count Count the number of times that a certain value occurs.
Distinct Count Count the distinct number of times a value occurs (only works on numeric fields).
You can select where the 'Sum' field is placed. This is normally either a 'Group Footer' or a 'Report Footer'. All possible locations are shown.
If your report does not contain a group – see next paragraph – and you would like to place the summary in a group you can immediately create the group by clicking the 'Insert Group' button.
7.3 Group
A ‘Group’ is used to divide data into specific sections and for sorting. For example: Group all orders per customer. The panel for adding a group to a report looks like displayed on the right.
The First combo box is used to select the field on which the grouping takes place. The second combo box defines the sorting-order (ascending or descending).
The ‘Group Name Field’ has per default the same value as the field upon which the grouping takes place. Suppose that the Group is defined as on customer number, but the ‘Group Name Field’ needs to be the customer name this can be changed by checking the checkbox ‘Customize Group Name Field’ to change it to the appropriate field.
By checking the checkbox ‘Repeat Group Header On Each Page’ the section with the header-details are repeated on each page.
7.4 Function field
A ‘Function Field’ is usually a field with content that is derived from another field. A ‘Function Field’ is a calculation based on another field, or a different representation of text.
Select ‘Function Field’ from the ‘Insert' menu and move the mouse cursor to the place in the report where the function field needs to be placed. Press the left mouse button down while dragging, to resize the object as appropriate.
After the field has been placed in the report, the
‘Function editor’ window is activated.
The bottom half is the area for editing the function. You can enter the name of the function but it is not required. If you omit entering a name the name will be "FunctionX", where "X" is the number of the function. You can always change the name later. After entering the function contents you can validate the function by clicking the ‘fx’ button.
Use the three panels in the top half to help in building up the function. Double-clicking on an element in the tree will automatically place the
appropriate piece of code in the editor below, in the function.
There is a tree view for selecting available fields (from report or database), one for selecting a specific type of manipulation (expression or statement) and one for selecting functions.
If a function does not return a value (the 'return' statement is not present), the value returned from the function is undetermined.
Example: If we want a function that returns the first 10 characters from the name of a customer we have to do the following:
Double-click the ‘Return’ expression in the middle tree view. While not shown here the editor part will contain "return" after this.
Double-click the function
‘Left(string,number)’ in the ’Available Functions’ tree view. Make sure that the insertion cursor is placed between the brackets of the ‘Return’ statement.
Edit the second argument of the function (the ‘Number’ behind the comma) to be 10.
Then edit the first argument (the ‘String’
before the comma) by double-clicking the customer name field in the database fields.
Change the name of the function to a meaningful name (e.g. CustomerName).
Check the function by clicking the function-check button in the tool-bar of the function editor window. If no error occurs, the syntax of the function is correct and you can click the OK button to store the function in the report.
7.5 Text object
Select the option ‘Text Object’ and move the mouse cursor to the position in the report where the object needs to go and keep the left mouse button pressed down to create the appropriate size of the object. After releasing the mouse button, text can be edited. Changing the text afterwards can be done by clicking on the object with the right mouse button and choosing ‘Edit Text’ from the context menu or by simply double clicking.
7.6 Line, Box and Picture
Select the option ‘Line’ or ‘Box’ and move the mouse cursor to the position in the report where the line or box has to appear and keep the mouse-button suppressed while giving the line or box the appropriate size.
For adding a picture in a report, first select the picture, then move it to the place in the report and release it in the report by clicking the left mouse button. By default the picture will have the same aspect ratio as the original, and it can now be resized using the mouse.
Images which are stored in a database can also be added. For this, the field which contains the images has to be added to the report.
7.7 Chart object
Select the option ‘Chart Object’ and move the mouse cursor to the position in the report where the chart has to appear and keep the mouse-button suppressed while giving the chart the appropriate size. When the mouse is being released the 'Insert Chart wizard' will appear. See paragraph 3.1 (Adding a chart).
7.8 Sub-report
A sub-report is a report inside another report.
There are specific reasons for constructing reports this way:
Show details of data that is not related to each other. For instance because the data is not related, the data-types do not match, or because the data resides in separate databases.
Combine two or more separate reports into one report.
Showing the same data multiple times in one report, but with different dimensions.
A sub-report can either be linked, or not be linked to the main report. If the reports are not linked, the information is not related, data from one report is not filtered by reference to the data presented in the other report.
It is possible to link reports by using global variables in the filter functions and function fields. The following example shows a main report with order-header information, linked to a sub-report showing order-details.
Create a new report based on the table with order-header data and Group the data on order-number.
Create a second Detail section, the report now shows ‘Details-A’ and ‘Details-B’
Add a ‘Function field’ to the ‘Details - A’ section. The function will define a global variable named
“iOrder” that gets the value of the current order-number:
dim iOrder as global
let iOrder = {Orderhea.Order_Number}
return iOrder
Hide section ‘Details -A’. We do this because the result of the function is not meant to be printed in the report.
Add a sub-report by choosing 'Sub-report' from the 'Insert' menu and place it in section 'Details – B'. The ‘Report Creation Wizard’ is activated for creating the sub-report.
Select the appropriate order-detail fields (no grouping and filtering).
Open the sub-report by double clicking it. Add the following 'Filter Function':
dim iOrder as global
return ({OrderDtl.Order_Number} = iOrder)
Defining the global variable iOrder in the filter function is optional here, because it is already defined in the main report, but it is good practice to leave it in for documentation purposes. The filter process will now only use those order-detail records that belong to the current order record that was set in the main report.