Category: Base

[qsm quiz=12]

Change the orientation and paper size

Orientation is the format in which the page displays, either in portrait mode where the page is displayed with the long edge vertical, or landscape, where the longer edge is horizontal.

[the_ad id=”12355″]
[the_ad id=”12356″]

To change the orientation, select either Portrait or Landscape.

To change the orientation, select either Portrait or Landscape.

To change the paper size, select from the ‘Format’ drop-down menu or use the manual width and height entry boxes below it.

Print a page, selected record(s), complete table

To print records from a table or query, first copy the records from Base and paste into Calc or Writer.

In Calc or Writer click on the printer icon.

Select the pages you want to print by selecting ‘All sheets’ then in the ‘From which print’ section select ‘All pages’ or ‘Pages’ and type the individual or range of pages desired.

Then Click ‘OK’ to print to the selected printer.

Print the result of a query

To print the result of a query, first open the query by double clicking it in the main window.

Select all the records by left clicking the grey box in the top left corner, then right click and select copy.

Paste records into a Calc or Writer file.

Click the printer icon in the toolbar and select ‘all pages’ then click OK to print.

Print specific page(s) in a report, or print complete report

First, open a report by double clicking it in the main window.

This will open a text report in Writer.

Click the printer icon on the toolbar to open the print window.

In the print window, select individual pages or all pages from the ‘Range and Copies’ section, then click OK to print.

[the_ad id=”12397″]

Export a table, query output in spreadsheet or text

[the_ad id=”12355″]
[the_ad id=”12356″]

Open a table or query by double clicking from the main window.

Select File -> New -> Spreadsheet to open a new Libreoffice Calc spreadsheet.

Minimize the spreadsheet for now.

To export all records, left click the grey box in the top left corner of the table/query.

Then right click and select ‘Copy’.

Open the spreadsheet and right click in cell A1, select paste and this will export all of the records from the selected table/query.

To export individual records, click the grey box to the left of the record, then right click and select copy.

Open the Calc spreadsheet, right click and select paste.

Note that the field titles will paste into the spreadsheet automatically without having to copy them.

To export specific records, use the filter tool.

Click ‘standard filter’ icon in the toolbar.

Select the criteria you want to use to filter the data.

Then click the top left grey box to select and copy the records and paste them into a spreadsheet.

To export records as text into a document, first select File-> New -> Text Document to open a new LibreOffice Writer document, then minimize it.

In Base, open a table/query, select the records and copy.

Go back to the Writer document, right click and select paste to open the Insert Database Columns window.

Choose how to insert the data, either table, fields or text.

Select the fields you want to export, to choose them all, click the ‘fast forward’ icon.

If you want field headings to display, then check the ‘Insert table heading’ box.

Click OK to insert the data.

[the_ad id=”12397″]

Add, modify text in headers, footers in a report.

[the_ad id=”12355″]
[the_ad id=”12356″]

Open a report in design view, by right clicking the report in the main window and selecting edit.

To add text to a header or footer, first click ‘Label Field’ from the toolbar.

Click and drag where you want to place the text.

With the label box selected, click the ‘properties’ icon or right click and select properties to display properties window.

To modify the text, click inside the label dialogue box in the properties window and type text.

To edit the font, size and style of the text, click the 3 dots icon next to ‘Font’ in the properties window.

To change the alignment of the text, select options from Horz and Vert Alignment in the properties window.

Any text in the header or footer will display on every page of the report when it is executed.

In LibreOffice Base, the simplest way to present specific fields using average or count functions is to create a query first.

[the_ad id=”12355″] [the_ad id=”12356″]

Average will return the mean average of the field, therefore it requires numerical data. Mean average is calculated by finding the sum total of all the records, divided by the count (the number of records there are). The average function does this for you automatically.

An example of where the ‘average’ function may be used is if you’re using a database to record results of football matches. You can set the average function to find the average number of goals scored per match for a given team, for example.

The ‘count’ function will display a number representing the total number of records containing data in that field.

E.g. if there are 25 records containing data, in the result of a query for a specified field, the ‘count’ of that field will return the value 25. Therefore, the data in this field doesn’t have to be numerical, it can take any form.

You can also choose to count ‘null’ values, i.e. records where there is no data present for the specified field.

An example of where this could be used is a report to count the number of customers that sign up for a loyalty card in a shop. You can set it to only count records where the ‘sign up date’ field is filled in, for example.

Design a new query and add the tables and fields shown.

Group Last Name, First Name, employee-ID and Salary, so none of these are repeated.

Add two Bonus fields and select ‘Count’ and ‘Average’ from the Function drop-down menus. Then save the query as ‘average bonus’.

Now create a report using the wizard and use the ‘average bonus’ query.

Add all of the fields from the query.

Click Next.

Relabel the COUNT and AVG fields to more descriptive labels

As the query is already grouped, there’s no need to group the report.

Choose Sort by ‘Last Name’ – Ascending, to sort the report in alphabetical order.

Choose the preferred layout and click Finish to create the report.

A report will open as a document, displaying the count and average for the bonus fields in alphabetical order by name.

Once you have closed the report, if you wish to run it again, simply double click the report from the main window.

To edit the report, right click it in the main window and select edit.

[the_ad id=”12397″]

Related lessons

In this lesson, we learn about the various database functions available in the ‘Function Wizard’ of Microsoft Excel. We explore the ‘DSUM’ function and its three arguments: ‘Database’, ‘Database field’, and ‘Search criteria’.
Advanced Spreadsheets – Database Functions

Present specific fields by maximum or minimum

You can present fields in a report using maximum or minimum functions, again without the need for a query.

The function is applied directly to the field to either display the minimum or maximum value from the records. Therefore, it can only be used on numerical values.

[the_ad id=”12355″] [the_ad id=”12356″]

An example where this could be used is in a financial or accounting report. For instance, you could display the maximum or minimum sales figures for a given time frame.

From the main window, right click the report and select edit.

Create a label and text box for the specific field you want to find the maximum or minimum of e.g. Maximum Bonus.

Double click the text box or select Properties from the toolbar.

In the properties window, select the ‘Data’ tab.

Change Data Field Type to ‘Function’.

Select the specific Data Field, e.g. bonus.

From the ‘Function’ drop-down menu select ‘Maximum’ or ‘Minimum’.

The data will be displayed with the maximum or minimum value in the specified box.

In this example, the maximum bonus from the bonus field is displayed.

[the_ad id=”12397″]

Present specific fields in a grouped report by sum

The ‘sum’ function will add together all the records in a specified field.

Therefore, the sum function can only be applied to numerical fields.

Reports allow you to present fields as a sum without having to create a query to include the function.

[the_ad id=”12355″]
[the_ad id=”12356″]

An accounts database is an example of where this might be useful. Total monthly sales revenue can be displayed in a report by using the sum function simply by applying it to a ‘takings’ or ‘daily revenue’ field.

To present a specific field as a sum, first open the report in design mode.

Then click on the ‘Label’ icon on the toolbar, and draw a text box in a grouped section.

To change the text in the label, double click the text box, or select properties.

In the Label section of properties, type the text you want to display e.g. ‘Total Bonus’.

Then click the ‘Text Box’ icon from the toolbar and draw a text box next to the lable.

Double click the text box or select properties.

Select the ‘Data’ Tab from the properties window.

To present fields by sum, select the ‘Function’ option from the Data Field Type drop-down menu.

Select the field you want to display the sum for, from the Data Field drop-down menu e.g. Bonus.

From the Function drop-down menu, choose ‘Accumulation’.

From the Scope drop-down menu, choose the group you are displaying the data in.

Execute the report to open the text document.

The text box created will now be displayed as a sum of the selected field.

In this case, all of the bonus values are added together to give a total bonus value for each individual employee record.

[the_ad id=”12397″]

Present specific fields in a grouped report

[the_ad id=”12355″]
[the_ad id=”12356″]

For this section, create a new table to display salary and another to display bonuses.

This data will require a grouped report to display correctly as there are multiple entries for employee-ID.

Next, we need to create a relationship, in the main window, click ‘Relationships’ from the ‘Tools’ menu.

Click ‘Add Tables’ from the ‘Insert’ menu. This will give you the option to select a table and add the newly created tables.

To make the relationship, click and hold on a field in one table, then drag across to the one you want to create a relationship with in another table.

Lines symbolising the links will join the two fields.

Right click the joining line and click edit.

Select ‘Update cascade’ and ‘Delete cascade’ from the Relations menu.

Click OK, then save the relationship and close the window

Next create a query in design view.

Add the tables you want to use in the query (in this case Employee Basic Details and Salary and Bonus).

Add the desired fields from the three tables.

Select sort ascending for last name to get results in alphabetical order.

Click the save icon and name the query.

From the main window, select ‘Use Wizard to create report’.

Select the Query you made earlier.

Add the fields that you want to display in the report.

Click ‘Next’ until you get to step 3-Grouping.

Set up groups for data that you don’t want to be repeated in the report.

For this example, don’t add Bonus and Bonus Date as some people received more than one bonus.

This will display the Grouped fields, followed by each bonus earned, rather than duplicating all of the data.

Click ‘Next’ until 5-Choose layout.

Choose the layout you prefer. For this example, Columnar, two columns is best.

Click ‘Finish’ to display the report.

The report document may look badly formatted.

Notice how fields don’t line up and grouped data splits across pages.

In the main window, right click the report and select Edit.

To align correctly, click each text box individually, then click the ‘align left’ icon from the ‘align’ toolbar.

This will line up all the text boxes to the left.

Click on the horizontal line dividers and press delete. Add one horizontal line above ‘Last Name’ group to divide records.

Click the ‘Sorting and Grouping’ icon from the toolbar.

To stop groups splitting over pages, click each group separately and select ‘Keep Together’ – ‘Whole Group’ from the drop-down menu.

To check the formatting click ‘Execute Report’ from the toolbar to open the report document.

[the_ad id=”12397″]

Either create a new report through the wizard or edit an existing report in Report Design View.

[the_ad id=”12355″]
[the_ad id=”12356″]

To group fields, click on the ‘Sorting and Grouping’ icon on the toolbar.

Grouping fields will stop information from being duplicated, for instance if the same person has a home and mobile number stored in the database.

Select a field name for each group from the drop down menus.

Move the fields that you don’t want repeated up to the header for that group.

Use the horizontal line icon to draw a line above the top field to separate records.

Execute the report to open a text document.

In this example, the report no longer repeats the name and group for records that have both a mobile and home number.

[the_ad id=”12397″]

Create and name a report based on a table or query

[the_ad id=”12355″]
[the_ad id=”12356″]

Begin by clicking ‘Reports’ in the Database area of the main window, then click ‘Use Wizard to Create Report’ from the Tasks area.

In the Report Wizard window, select the desired table or query from the drop down menu.

Select the fields that you want to show in the report by double clicking in the Available Fields menu.

If you want to display them all, click the fast forward button.

Click next when ready to move on. For now, keep clicking until you get to ‘5. Choose layout’

Try selecting different Layouts, such as columnar, in blocks, etc.

Notice how it changes the display in the main report page behind.

This is how your final report will look, so decide on a style you like, then click Next.

Type a name for the report in the dialogue box under ‘Title of report’ and select ‘Dynamic report’ and ‘Create report now’.

Click Finish to run the report, opening a text document in LibreOffice Writer.

The text document will display all the data fields and records from the table or query that you selected.

Notice that in this example some of the records are repeated as they had mobile and home number data in one of the tables.

Soon we will look at creating grouped reports to solve this problem.

[the_ad id=”12397″]