Select the Surname group section on the left hand side (in blue)
In the properties section on the right hand side, where it says ‘Force New Page’ select ‘Before Section’.
This will insert a page break before each new section.
Execute the report to see the effect of this change.
Notice that a page break has been inserted between each group record.
To change the sorting and grouping in a report, click the sorting and grouping icon.
In the window that pops up, you can select the grouped fields and change their properties.
You can choose to sort ascending or descending, as well as choose whether to display headers and footers.
Execute the report with the settings shown by clicking the execute report icon.
Notice that the report displays the surname in ascending alphabetical order.
Close the report and return to the report builder view.
Open the sorting and grouping window.
For Surname change sorting to Descending.
Select ‘First Name’.
For Group Header select ‘Not present’
Execute the report.
Notice that the report is sorted by Surname in reverse alphabetical order (descending).
Also notice that the First Name group is no longer present as we turned off the group header.
This means that it is still grouping the field, but not displaying it in the report.
Create a report in wizard mode, select the table Car and owner details and add the first 5 fields only.
Click Next twice.
Add grouping levels for Surname and First Name.
Click Next twice.
Choose ‘Columnar, two columns’ layout.
Click Next.
Name the report.
Select Dynamic report
Select ‘Modify report layout’
Click Finish.
Notice the colour coding on the left-hand side of the window.
Orange represents headers and footers.
Blue represents groups.
Pink represents the report details or page.
To add a field to a section of the report, we first need to click View -> Add Field
The Add field window pops up.
Click a section that you want to add a field to, in this example click on the ‘Detail’ pink section to highlight it.
Select a field to add, in this case ‘Address’ and double click it.
Notice that the field now appears in the Details section.
To delete the field, simply click on the label and text box and press the delete key.
You can insert any field from the table into any of the sections.
Be aware that if you add a data field to the header or footer it will appear at the top and bottom of each page.
Concatenate fields means to merge them together.
There is no option to do this automatically in Base, so we first have to create a query in design mode.
Add the Car and owner details table. We want to merge First Name and Surname fields.
In the Field column type: “First Name” || ‘ ‘ || “Surname” to merge the fields. Give it the Alias ‘Full Name’
If you prefer, you can enter the command in SQL view.
Press F5 to run the query.
Notice that the First Name and Surname fields have been merged into a single field called Full Name.
Save the query with the title: ‘Query Names’ and exit the query design window.
Create a report in wizard mode.
Add Query Names and select the field ‘Full Name’ to appear in the report. (Note: if we want to display more fields we can simply add them to the query, then add to the report).
Keep clicking Next until you get to Choose layout and select a column layout.
Click Finish to run the report.
The report will display the concatenated (merged) fields as one.
This can be repeated for other fields if necessary by using the ||’ ‘|| command in a query, then running the report.
In Libreoffice Base, there is no automatic option to do this in a report.
We must first create a query that we will then use to run a report.
Create a new query in design view.
Add the table Job Costs twice, the reason for this will be explained shortly.
Creating a query that gives a running total column is quite tricky in Base.
Switch to SQL mode and enter the code shown to the left.
This SQL code names the tables a1 and a2, then uses the SUM command to create a running total or ‘Subtotal’
The statement WHERE “a1”.”Cost”<=“a2”.”Cost” is important as this adds them up cumulatively. Go back to design view.
It may be a bit clearer to see the query logic in design view.
The Cost column is subtotalled using the SUM function, but first the Cost field is put into descending order, then the new value is added to each of the smaller values (<=a2.Cost).
This is complicated, but it’s the only way to get a cumulative total in Base.
Press F5 to see the query result.
Notice that the subtotal is a running total of Cost.
The results may not be perfect, as we might prefer the Job No to be listed in ascending order.
Unfortunately, we can’t do that as we had to sort the Cost field in order to create the Subtotal.
This is the best we can do in LibreOffice Base.
Now we can run a report to display the running sum.
Use the wizard to create a report.
Add the query we just created and select all 3 fields.
Click Finish to create the report.
A report is generated that displays the running sum column.
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.
A subform allows you to display associated data from a different table within a form.
[the_ad id=”12355″]
[the_ad id=”12356″]
Create a new form using the wizard.
On the first step, add all of the fields from the table ‘Jobs’, then click next.
On the second step, we can add a subform based on the existing relation to Order Details table, as we created a relation in a previous tutorial. Select it and click next.
If there was no created relationship in step 2 you can add them manually.
For step 3, select all of the fields by clicking the double arrow button.
Click Next.
Decide on the arrangement and styles you want in steps 5,6,7, then add a name for the form and click Finish.
The form will open automatically.
Scroll through the records using the controls at the bottom of the window.
Notice that you can modify or add records to the Orders table, at the same time viewing records from the subform from the ‘Order Details’ table.
This can be useful in cases when you need a reminder of other related information while inputting or modifying data.
To delete a linked subform, first open the form in design mode by right clicking and selecting edit.
Select the subform by left clicking on it.
Press the delete key to remove the subform.
[the_ad id=’12397′]
You may need to change the tab order of a form, for instance if you add a new response box or want to give certain fields priority over others.
[the_ad id=”12355″]
[the_ad id=”12356″]
Right click the Jobs Entry form and select edit.
If we want to change the order of entry so that the Car Reg No is entered first, followed by the Date, Job No, Job Description and Staff Number, we need to change the tab order.
Click the ‘activation order’ icon.
Click on Car Reg No and then keep clicking Move Up until it’s at the top of the list.
Repeat for Date until it’s second on the list.
Repeat for Job No, Job Description and Staff Number in that order (as shown)
Once the activation order is correct click OK. Close the form design mode and then open the form in entry mode by double clicking it.
Click the new record icon and enter a Car Reg No.
Then press the tab key and the cursor will move to the Date box.
Press tab again and it will move to Job No, then Job Description, then Staff Number, as this is the sequence that we set up.
[the_ad id=’12397′]
Open the car and owner detail form in design mode.
We are going to create options for the title field to save time typing them out. The options are going to be Mr, Mrs and other.
[the_ad id=”12355″]
[the_ad id=”12356″]
Start by making the text box next to ‘Title’ smaller by dragging the left green dot after selecting it.
Then click the label field icon from the form control toolbar and create a label field.
Select the label field box, right click and select control.
In the ‘Label’ section type ‘Other’.
Right click the text box we resized earlier, and select control.
Click the data tab and make sure Input Required is set to No.
Click the ‘Option Button’ icon in the form control toolbar and create two option buttons next to the title field box by clicking and dragging.
Right click one of the option buttons and select ‘control’.
Click the Data tab and select ‘Title’ from the Data field section. This links the response to the Title field.
Select No for input required.
In the Reference value (on) section type ‘Mr’
Click the General tab.
In the Label section type ‘Mr’
In the Name section type OptMr.
Close the properties box, right click on the option button and select control.
Repeat the whole procedure for the other object button, using Mrs instead of Mr.
If we left all of the buttons and text box for this title field ungrouped, when filling in the form someone could select Mr and Mrs. To stop this we group the boxes.
Select each option button and the text and label boxes, while holding down shift to select them all.
Right click and select Group->Group from the drop down menu. This means people can only select one option.
Open the form by double clicking it.
Click on the new record icon.
Try selecting the options Mr or Mrs. Notice that the text box automatically updates.
If you want to enter a different option simply type it into the text box.
[the_ad id=’12397′]
For this example, we need to set up a new form. Start the wizard and create a form from ‘Car and owner details’ including all the fields.
[the_ad id=”12355″]
[the_ad id=”12356″]
Then open this in design mode by right clicking and selecting ‘edit
Notice that the ‘Account settled?’ field is a check box which we selected when we set up the table, as the answer is either yes or no.
Ctrl and left click the check box, then right click and select ‘control’
Input required should be set to ‘Yes’ for this field, although leaving it unchecked is actually recorded as a negative input.
We can set reference values here, i.e. the output whether checked or unchecked.
E.g. we could set 1 for on and 0 for off if we want to perform calculations, for instance.
[the_ad id=’12397′]