Faq/Base/118

    From The Document Foundation Wiki
    < Faq‎ | Base

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