Faq/Base/118/fr
TDF LibreOffice Document Liberation Project Blogs communautaires Weblate Nextcloud Redmine Ask LibreOffice Donner
Tutorial on using the Report Designer
The Report Designer tutorial, including screenshots, is available in a printable or downloadable format here (currently in French only, pending translation) :
If you choose to display the document in your browser, please wait a few moments for it to load.
Why should I use a Report ?
Some people feel that the only point in having a report is to format data from a database so that it can be printer.
A report designer, however, provides the user with a slew of essential tools to exploit the data stored in a database. It allows data to be presented in detail in various ways. One example might be for following time spent on a projet, displaying profits for the last week, or checking to see which is one's most profitable client. Reports can, for example, be used to create invoices ready to be sent. If you are studying a foreign language, they can be used to print a list of vocabulary relating to a given field, or to register your progress in learning that vocabulary. Another idea might be to follow your creations or designs, through the use of a simple database, and then via the Report Designer, creating a type of catalogue or brochure document containing illustrations and text. In other words, Reports can be used for many varied purposes.
The Report Designer gives access to a host of useful tools allowing elaborate reports to be created : advanced sorting and grouping options are present, as well as the possibility to use SQL queries, statements and functions. The Report Designer makes it significantly simpler to conceive and design complex reports with the help of drag and drop functionality. Additionally, the Report Designer also allows a user to insert graphs and images.
Required Configuration
The Report Designer is integrated into LibreOffice by default. It is however written in the Java programming language, and thus requires that the computer on which it is run have not only a pre-installed Java Runtime Environment (JRE), but that LibreOffice be configured to recognize that JRE in order to be able to use it.
In the Tools -> Options -> LibreOffice -> Java submenu (on Mac, this is in the LibreOffice -> Preferences submenu), check that a Java runtime environment has been found and recognized, and that the corresponding radio button has been ticked / activated, for the JRE of your choice. If no JRE appears in the window dialog, then either your version of the JRE is not recognized by LibreOffice, or else you do not have a JRE installed, and will have to download and install one accordingly JRE download site .
Installing the Report Designer
The Report Designer is actually an extension provided with the default installation of LibreOffice, at least for Windows and Mac. Some Linux distributions, for their own versions of LibreOffice, still supply this extension as a separate component that may have to be installed with the package management system of that distribution.
The Report Designer Interface
The sample database used for the present tutorial was obtained by using the default provided Table Creation Wizard and choosing HouseholdInventory from the Personal list of suggested databases.
Report Designer Toolbars
Report Controls Toolbar
The Report Controls Toolbar contains a range of buttons which can be used to add fields, labels, images (pictures, logos, photos, etc) and graphs to the report.
Main Toolbar
The Main Toolbar enables the addition of fields to a report, the switching on or off of the Report Navigator and the management of sorting and grouping options.*
Alignment Toolbar
The Alignment Toolbar is used for positioning fields and other elements within the report.*
Menu
The menu gives access to the other modules of LibreOffice. Two functions will be mentioned here in more detail : Insert for inserting images into the report and Format in order to use the conditional formatting possibilities for a given field.
Default Report Page
The default page that is displayed on starting the Report Designer is divided into 3 parts : Page Header, Detail, and Page Footer.
Floating Tool Panels
Add Field Panel
This panel is used to add a database field to the report via drag and drop or via the Insert menu.
Sorting and Grouping Panel
This panel is used to manage sorting and grouping options for the various elements of the report.
This panel enables a user to navigate the report document via a tree oriented list hierarchy of objects. Double-clicking on one of the entries in the navigator list will select that item on the report document for selection or editing.
Properties Panel
This panel enables a user to edit the properties of the control, element or object which is currently selected.
For example, when you select a field in a report, you can use the Properties Panel to define the data source for that field, and also other properties such as the element's height and width, its position, formatting, etc...
This panel also allows you to choose the type of document to be used as the output format for the report : either a Writer document (ODT) or a Calc document (ODS).
Creating a Report
Creating a Grouping
In order to create a report that groups data, you need to a add a new Grouping Header section, by choosing the field on which to group via the "Sorting and Grouping" button, panel, or corresponding entry in the Report Navigator (in the present example, we have used the DatePurchased field). Choose the desired sorting options (ascending or descending) and then Present in both the Group Header and Group Footer dropdown lists.
Use the Add Field panel to place the grouped field within the Group Header section. You can then use the various available formatting options under the General entry of the Properties panel to format the field as you wish.
Detail Section
The Detail Section is designed to display a list of data from the database. Using the Add Field panel, add the fields that you want to use in your report to the Detail Section. Field and Label alignment can be slightly arduous, so use the Alignment Toolbar to help out. You can see what your final report will look like by clicking on the Execute Report button in the main toolbar.
Creating a Calculated Field
Whilst it is possible to use the report as you have just created it, it can also be improved. For example, you might want to add a field that calculates the sum of the values for each data group or to obtain a grand total.
In order to get a Grand Total, click on the button Text area in the Report Controls Toolbar and draw an area with the mouse in the Page Footer section of the report. In Properties panel of this new report element, click on the Data tab and set the following options :
| Field Data Type |
Function |
|
Field |
PurchasePrice |
|
Function |
Accumulation |
|
Extent |
Report |
In order to add a field that will calculate the sum of the values for each group, you must first, if you haven't done so already, insert a Group Footer section. Then draw a field in the Group Footer section and choose the following options :
| Data Field Type |
Function |
|
Data Field |
PurchasePrice |
|
Function |
Accumulation |
|
Extent |
Group DatePurchased |
Inserting a diagram
It is also possible to insert a diagram, for example, to show a trend line of the data indexed to the grouping. In order to do this, you first need to create a query to extract the required data from the database to be able to provide it for the chart diagram.
Close the report, then click on Query in your open ODB database file. Create a new query. Choose the table or tables containing the data to be extracted and add the fields to the query. In the present example, we have used the DatePurchased and PurchasePrice fields. In the function criteria of your query, choose Group for the DatePurchased field and Sum for the PurchasePrice field.
Save the query and then open your report in Edit mode to change it. Click on the Diagram button in the main toolbar and draw an area with the mouse in the Footer section to contain the chart. In the Properties panel, on the Data tab, choose the query you just created as the data source.
If the corresponding default histogram graph is not what you want, you change its properties by double-clicking on the graph, thereby entering edit mode for the graph. You can then change the default histogram graph to one more suited to your needs, for example, a 3D graph.
Example of a chart in a report
Inserting an image or logo into the Report Header
This is achieved via the Insert menu. An image inserted in this way will appear on every page of the report.
Conditional Formatting of a Calculated Field
In order to do this, you first need to select your field in the report while in Edit mode. Then, from the main menu, click on Format > Conditional Formatting.
In our present example, the alias for the query used to create the report is displayed within parentheses for the purposes of conditional formatting.
Example of a database report with conditional field formatting
Once you have mastered the fundamental principles of using the Report Designer, you will be able to use all of its functionality to format the data provided by your database to make stunning reports.
This can be reached by either using the F5 function key, or by clicking on the corresponding button in the main toolbar. It displays all of the elements and objects contained within a report. Contextual help is also available for the various entries in the tree list hieararchy, simply by pressing the F1 function key after having selected an entry.
The complete list of hierarchical elements contained within a report can be displayed by clicking on the small "plus sign" or arrow that appears in front of an entry. If there is no such sign or arrow, then that element has no daughter elements.
Tab : General
The following elements allow you to configure the display parameters for your report :
| Name | This is the name you have given to your report, it will appear in the Report Navigator once you press enter after typing in the name and validating. |
| Page Header | here, you have the choice of enabling/disabling printing from the dropdown menu of the content inserted into the Page Header |
| Page Footer | here, you have the choice of enabling/disabling printing from the dropdown menu of the content inserted into the Page Footer |
| Print repeated values | yes |
Tab : Data
The following elements let you configure the data source parameters for your report :
|
Content Type |
Choose the type of data to be used : table, saved query or straight SQL query |
|
Content |
Choose the data source name |
|
Analyse SQL |
yes, or no, depending on how you are accessing your data. Choosing 'yes' is equivalent to activating the native SQL parser functionality when in Query design mode. Choosing 'no' deactivates LO's native SQL parser and passes the query instruction directly to the database driver. This is required for some advanced database functions and server backends and is dependent on the database engine used. |
|
Filter |
choose yes or no depending on the data source |
|
Report output format |
Choose ODF Spreadsheet if you need to apply more advanced mathematical handling of your data results in your report, otherwise choose ODF Text. |
Report browser -> Functions
You will be able to create the functions that you will then use in the report. To create a new function, click on Function then right click New function. To access other examples, follow the links at the end of this page.
| Name | Explicitly name your function, without spaces between the words (ex: SumPurchases) |
| Formula | Clicking on the 3 small dots starts the wizard for creating formulas. You can also enter your own formula directly. For example, to calculate a sum of several values, the syntax will be: [NameOfInitialValue] + [NameOfFunction] (ex: [PurchasePrice] + [PurchaseSum] |
| Initial value | You enter in square brackets the name of the field on which the function will apply (ex: [PurchasePrice]) |
| Deep course | Action not indicated in the original guide |
| Pre-assessment | By choosing yes, the report will be calculated during its creation. This action consumes resources, and you can prefer not to wait until the end of the creation of the report to perform all the calculations |
Tab: General
The following elements allow you to configure the presentation of the report:
| Name |
You can keep the page header or name it differently |
| Visible |
Choose yes to view the content of the header |
| Height | You can enter the height dimension or modify it using the mouse directly in the report page in Edit mode |
| Conditional print expression | Choose conditions if applicable |
| Background transparency |
By choosing no, you will be able to configure a background color for the page header |
Report browser -> Report header
Tab: General
The following elements allow you to configure the presentation of the report header:
| Name |
You can keep Report header or name it differently |
| Force a new page | Choose None if you do not want to create a different page by data or grouping of data. Otherwise, choose the creation of a new page according to your desired layout settings, for example After the section |
| Keep together | Allows printing of group data on a second page if necessary |
| Visible | In general, you will answer yes |
| Height | You can enter the height dimension or modify it using the mouse directly in the report page in Edit mode |
| Conditional print expression | Choose conditions if applicable |
| Background transparency | By choosing no, you can configure a background color for the report header |
You can configure how to group data from the database in the report using the Sorting and grouping panel (see above: Creating a grouping). By default, the name of the group is that of the field you have chosen for the Grouping.
| Keep together | Choose yes |
| Start a new column | Choose no |
| Restore page number | Choose no |
See above, in the Navigator chapter of the report: Functions
The General tab contains the parameters seen above.
Report browser -> Detail
It is the main body of the report. It can contain other components such as label fields which allow you to enter text in the report, text boxes which allow the display of data, controls to display images (very practical for making a trombinoscope or a catalog), graphics, lines, shapes that you will draw and place in the background of a text box, etc ... In summary, this section contains the data of the database that you want to see in your report.
Tab: General
The elements seen above allow you to configure the presentation of the report header.
Tab: General
The elements seen above allow you to configure the presentation of the footer.
Components common to all sections.
Report Controls Toolbar -> Label Field
Tab: General
| Name | You can keep the proposed one or name it differently to distinguish it in the Navigator |
| Label | This is the name that will appear in the edition of the report. You can give it a more explicit name than the one proposed (the name of the database field) |
| Keep together | Allows printing of group data on a second page if necessary |
| Position X | Indicates the distance in cm between the left edge of the control and the left margin of the page |
| Position Y | Indicates the distance in cm between the top of the control and the top of the section |
| Width | Indicates in cm the width dimension of the control |
| Height | Indicates in cm the height dimension of the control |
| Print repeated values | Choose yes or no |
| Conditional print expression | Choose conditions if applicable |
| Print when group changes | Choose yes |
| Background transparency | By choosing no, you can configure a background color for the report header |
| Police | Choose settings |
| Horizontal alignment | Choose settings |
| Vertical alignment | Choose settings |
Report Controls Toolbar -> Formatted Field (Text Area)
Tab: General
| Name | You can keep the proposed one or name it differently to distinguish it in the Navigator |
| Position X | Indicates the distance in cm between the left edge of the control and the left margin of the page |
| Position Y | Indicates the distance in cm between the top of the control and the top of the section |
| Width | Indicates in cm the width dimension of the control |
| Height | Indicates in cm the height dimension of the control |
| Print repeated values | Choose yes or no |
| Conditional print expression | Choose conditions if applicable |
| Print when group changes | Choose yes |
| Background transparency | By choosing no, you can configure a background color for the report header |
| Police | Choose settings |
| Horizontal alignment | Choose settings |
| Vertical alignment | Choose settings |
| Formatting | Choose the parameters, in particular if you want a display of the currency, or the date, particular |
Tab: Data
| Data Field Type | Choose the type of data field: Field or formula: you will have to choose the field or the formula Function: you will have to choose the type of function Counter: you will have to choose whether it applies to the group or to the whole report User-defined function: you will have to choose a function from those you have created |
| Data field | Choose the database data field or the formula. For example, to print Boolean fields in the form of checked/unchecked boxes, the formula will be IF ([Boolean];"U+E531";"U+E470") for Boolean fields Boolean from the table or query that was used to create the report |
| Function | Choose the function: Accumulation will sum the data of the field you selected |
| Scope | Apply the function to a Group or to the whole report according to your choice |
Report Controls Toolbar -> Image
Tab: General
| Name | You can keep the proposed one or name it differently to distinguish it in the Navigator |
| Text direction | You can keep the proposed one or modify it |
| Keep as link | By choosing not the image is integrated into the document (advantage: portability; disadvantage: increase in weight) |
| Visible | Choose yes or no depending on what you want |
| Position X | Indicates the distance in cm between the left edge of the control and the left margin of the page |
| Position Y | Indicates the distance in cm between the top of the control and the top of the section |
| Width | Indicates in cm the width dimension of the control |
| Height | Indicates in cm the height dimension of the control |
| Print repeated values | Choose yes or no |
| Conditional print expression | Choose conditions if applicable |
| Print when group changes | Choose yes |
| Background transparency | By choosing no, you can configure a background color for the report header |
| Vertical alignment | Choose settings |
| Images | Select the image you want to insert |
| Adjust the size | Select the parameter so that the image adapts or not to the size of the control |
Tab: Data
| Data Field Type | Choose Field or formula if you want to have a different image for each item in the Detail section (e.g. catalog, trombinoscope)) |
| Data field | Choose the database data field that contains the <OBJECT> images |
| Function | |
| Scope |
Report Controls Toolbar -> Diagram
Tab: General
| Name | You can keep the proposed one or name it differently to distinguish it in the Navigator |
| Position X | Indicates the distance in cm between the left edge of the control and the left margin of the page |
| Position Y | Indicates the distance in cm between the top of the control and the top of the section |
| Width | Indicates in cm the width dimension of the control |
| Height | Indicates in cm the height dimension of the control |
| Print repeated values | Choose yes or no |
| Conditional print expression | Choose conditions if applicable |
| Print when group changes | Choisissez oui |
| Type de diagramme |
Vous pouvez modifier le type Histogramme par défaut pour choisir un autre type de diagramme |
| Aperçu des lignes | Choisissez les paramètres |
| Police | Vous pourrez paramétrer une police de caractère et ses styles |
| Alignement horizontal/Vertical | Sélectionnez les paramètres pour positionner le diagramme |
Tab: Data
| Content type | Choose the type of data source |
| Content | Choose the name of the source |
| Analyze the SQL statement | Yes or no depending on the source |
| Filtered | Activate if you want to configure a filter |
| Sorting | Choose the desired field and type of sorting |
| Master link field | Choose the desired field |
| Link slave fields | Choose the desired field |
Report Controls Toolbar -> Fixed Line
Tab: General
| Name | You can keep the proposed one or name it differently to distinguish it in the Navigator |
| Visible | Choose yes or no depending on what you want |
| Position X | Indicates the distance in cm between the left edge of the control and the left margin of the page |
| Position Y | Indicates the distance in cm between the top of the control and the top of the section |
| Width | Indicates in cm the width dimension of the control |
| Height | Indicates in cm the height dimension of the control |
| Vertical alignment | Choose settings |
| Orientation | Choose if you want a horizontal line between the two margins, or vertical between two sections |
Syntax Help
Built-in Functions
Examples of using the Report Builder
Several examples of using the Report Builder are available here forum as proposed by various users of both OpenOffice.org and LibreOffice.
You can use a search within the forum to look for a given word, expression or term. For example, you could search here bool in the section of the forum dedicated to Base questions.