Faq/Base/118/fr

From The Document Foundation Wiki
< Faq‎ | Base
Jump to: navigation, search
This page is a translated version of the page Faq/Base/118 and the translation is 0% complete.

Other languages:
English • ‎français • ‎italiano

Contents

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

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

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

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 :

Field Data Type

Function

Field

PurchasePrice

Function

Accumulation

Extent

Report

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 :

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.

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.

Report Navigator -> Report

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

Report Navigator -> Page Header

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

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" -> General tab

Keep together Choose yes
Start a new column Choose no
Restore page number Choose no

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.

Report Navigator -> Report Footer

Tab: General

The elements seen above allow you to configure the presentation of the report header.

Report browser -> Footer

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

see here page

Built-in Functions

see here page.

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.