Faq/Base/118

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) :

[[Media:Tutoriel Assistant de création de Rapport.odt|Tutoriel de l'Assistant de création de rapport]]

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 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.

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.

Report Navigator Panel
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 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.

in the page footer :
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 :

in the Group Footer section :
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 :

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.

Report Navigator
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 :

Tab : Data
The following elements let you configure the data source parameters for your report :

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.

Tab: General
The following elements allow you to configure the presentation of the report:

Tab: General
The following elements allow you to configure the presentation of the report header:

Report navigator: Groups -> Sorting and grouping panel
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.

Report navigator: Groups -> "Group name" -> Functions
See above, in the Navigator chapter of the report: Functions

Report navigator: Groups -> "Group name" -> Group header
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.

Syntax Help
See the French AOO wiki page for Report Builder syntax.

Built-in Functions
See the French AOO wiki page for Report Builder 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.