Ceci est une ancienne révision du document !
If you have been following along in this series on LibreOffice Base, you now have a database file with tables and relationships. You can enter information into your database through forms, but what about getting information out of the database? Queries and Reports are used for extracting data from your database - the Queries define what data is extracted, and the Reports define the appearance of the extracted data. We will create a query and a report to show how you can generate a report of your data.
Creating a Query
Queries poll the database for certain information in your database. You have three ways to create a query: a wizard, design view, and SQL. The wizard doesn't work with the type of relational database we have created and SQL is beyond the scope of this How-to, so we will use the design view to create our query. We will create a query that contains all the important fields in our tables: title, published year, author(s), and type(s).
Click on Queries in the Database pane, then click on Create Query in Design View in the Tasks pane. A Query design form will display with a Add Tables or Query pop-up dialog. Add all the tables to the Query Design form, and close the pop-up. You will end up with a form that looks a lot like the relationships design we created previously. Below the tables, you see a form that will contain the fields which we want to include in our query. From the Books table drag Title and Published into the form. Drag Name from Authors, and drag Type from Media. That is all we need for this query. Save it as AllFields. Close the Query Design form.
You now have a query to use in creating multiple reports for your database. If you're curious about the SQL used to create your query, you can right-click on the newly created query and select Edit in SQL View. This brings up the SQL View with the complete SQL statement for the query you just created. I wouldn't recommend changing this unless you are well versed in SQL, but creating multiple queries in Design View, and then viewing them in SQL View, could help you begin to learn SQL.
Creating A Report
A report runs a query and formats the query results into something you can use. You can create many different reports with the query we created – depending on how you group the data from the query. We will create a report for sorting our books by media type, and I will suggest how you might create other reports using the same query.
There is only one way to create a report – use the wizard. Select Reports in the Database pane and click on Use Wizard to Create Report. A report template window will appear with the report wizard. You can actually watch your template fill in as you go through the steps of the wizard, giving you some ideas about how your final report will look.
In step 1, you will pick your query and the fields to use in the report. If not selected already, select the AllFields query we just created. Move all the fields into the Fields in report box by clicking on the » button. If you wanted to create a report that uses just some of the fields, you would just select the required fields. For our report, we will use all the fields. Click Next.
Step 2 is labeling our fields. Here we specify how the fields are labeled in our report. Change the label for Type to Media Type, and Name to Author(s). Click Next.
We create our groupings in step 3. Groupings control how the fields are consolidated and arranged in the report. Grouping is important, because we can use it to create a whole different report depending on how we group the fields. For example, if you wanted to create a report of titles by author, you would make Name your first grouping, then Title as a sub-group. For our report, we are creating a report of Titles by Media Type, so our grouping order is Type, Title, Published. We add published because, if there is more than one author, the published date will repeat in the report template, a byproduct we don't want. We exclude the Name field because if there is more than one author, we want them listed together. Click Next.
Sorting is done in step 4, but we don't have much use for it here. You will notice that you can change only whether the sorting is ascending or descending for our groupings. In the fourth box, select Name and leave on Ascending. Click Next.
In step 5, we can choose a layout for the data and the header. There are several for each, and they change the look of the report. I left mine at the defaults, but feel free to play around with these options. Under the Layout of data list box, you can select whether the report is landscape or portrait. For this report, I think portrait will work best. Click Next.
The final step is where we create the report. You can give it a title, indicate how the report is used, and what to do with the report. For this report, give it a title of TitleByType. Now, we need to answer the two questions. What type of report do you want to create? A static report is a one time report. It cannot change. Once it is created, the data is fixed. If, however, you want a report you can re-use, you want a dynamic report. A dynamic report is just a template you can use over again. For this report, we want a dynamic report we can use again, so select Dynamic. How do you want to proceed after creating report? Modify report layout will allow you to edit the report as a writer document. Create report now is obvious; it will fill in the data and generate the report. We can always edit the layout later, so we will select Create report now. Click Finish.
Base generates our report and displays it in a Writer window. This generated report is read-only. If you want to edit the text or layout of the report, close it. In the Reports pane, right-click your report and edit. This opens the report template in Writer, where you can add text, graphics, etc, just like you would in any Writer document. Just take care when changing anything in the cells where the data is plugged in.
In this How-to, we created a query and a report. Play around with the grouping and sorting settings in the report wizard and see just how many different reports you can create from the one query we created.
Next time, we will use macros to create enhancements to our database and make it act more like an application.