Creating a Report

From NeoWiki

Forms are one way to arrange the data in a database in a more helpful manner. Forms often work best on the screen; they aren't always the best option for printing. Reports are another way to arrange data. Reports are more amenable to printing. This tutorial demonstrates how to use the Reports Wizard built into Base.

Creating a Report Based on a Table or Query

1. In the main database document window, click on the Reports icon in the left hand column.
2. Click on Use Wizard to Create Report...

   Note: you can also access the report wizard by control-clicking or right-clicking on a query or a table (when Queries or Tables is selected in the left hand column) and selecting Report Wizard...

3. When the Report Wizard opens, select a query or table from the drop-down. In this case, choose Query: All Sightings.
4. We want all the fields in the Available Fields section to be in the report. Click on the >> to move them all at once.

   To move one field over at a time, use the > button

5. If desired, change the order of the fields by selecting a field and clicking the up or down arrow buttons.
6. Click Next>
7. In the Labeling fields pane, make any desired changes to Label names
8. Click Next>
9. In the Grouping pane, specify the fields you want to group by. We want to group by both AOU_ID (to provide the right sort order) and by Bird_Name. So select AOU_ID and then click on > repeat with Bird_Name.
10. Click Next>
11. Choose the Layout of data and Layout of headers and footers. I recommend one of the Align Left options for Layout of data and Default for Layout of headers and footers. Note that you as you change settings, the report document beneath the Report Wizard changes.
12. For this report, you want to leave the orientation set for Landscape.
13. Click Next>
14. In the final pane, give the report a name. I choose All Sightings By Bird Name
15. Leave the radio buttons set for Dynamic report. This way the report will update as data changes.
16. Because the wizard makes some odd choices for column widths, select Modify report layout.
17. Click Finish. The wizard and the report document will disappear. Be patient, and the report document will reappear.
18. Adjust column widths by dragging the column borders as needed.
19. To change the date format, control-click or right-click on the date. Choose Number Format... from the contextual menu. Select the desired formatting, and click OK;
20. When the arrangement looks right, save and close the form. (Command-S and then click the red close button}}

    Unlike forms, clicking the Design Mode On/Off button on the toolbar does not result in the report being built. To have the report generate, we must close it and reopen it.

21. Double click on the All Sightings By Bird Name to see the report.

Notes

• Sorting (Pane 4) is only available for reports based on tables. Reports based on queries will sort according to the sort order specified in the query.

Variations

• Note that the report above includes some bird species more than once. This is because grouping in reports works differently than grouping in queries. To see only one line per bird species, you must base the report on the Life_List query we created in Using the Group Function in a Query.
• Suppose you wanted to group instead by StateOrProvince andLocation, so that you could see what birds you had seen at any given location. You would first need to create a new query based on All Sightings where the query is sorted according to StateOrProvince and Location. Then follow the instructions above, grouping instead by StateOrProvince and Location.
• Say you wanted to print a report that listed all the birds in the AOU birdlist and you wanted the report to include a checkbox for each bird. If you had identified the bird, the checkbox would be checked. To do so, you would need to do the following:
  1. Create a query using a Left or Right Outer Join to join the AOU_Birdlist and Sightings tables. Make sure to use EnglishName from the AOU_Birdlist table (and not BirdName from Sightings) and verify that the the join type selects all the records of the AOU_Birdlist table.

     if you have from the AOU_Birdlist table, and you want to list order and family names in the report, you will need to first make a side query combining the order, family, and EnglishName as well as the IDs for each of these three tables (AOU_Birdlist, Orders, and Families) before creating the Outer Join query.

  2. Group the fields in this query by AOU_ID and EnglishName. See Using the Group Function in a Query

     If you have , you would presumably want to group by Order ID, Order, *Family ID, Family, AOU_ID and EnglishName.

  3. Include the Date field and use the COUNT function on it. You may also add the Date field again to do a MIN(Date) function. Just make sure to give at least one Date column an alias. See Using the Group Function in a Query to see how to use MIN and COUNT functions.
  4. Create a new report, following the instructions above; use the same grouping fields as in the query.
  5. When you modify the report formatting, control-click on the COUNT(Date) field (not the label for the column, but on the dummy data) and choose Number Format.... In the Category section, select User-defined. In the Format Code box, type the following:

     [>=1]"☑";[=0]"";0

     It may be easiest to copy this code from a checkbox in a Boolean field in another report, paste it into the box, and add the > sign before the first =.

     Then click the green checkmark icon next to the box to add it to the list of User-defined formats. It will appear in the list as 1234, but in the report, it will show an empty box if there are no sightings, and a checkmarked box if there are sightings.

  6. If you included a MIN(Date) field, you will need to set its formatting to a date format for it to appear correctly.

Related External Links

• Solveig Haugland on Reports, part one
• Solveig Haugland on Reports, part two
• PDF by Solveig Haugland on Reports This is a download link; the file is pretty large.
• Display Report for Current Record A detailed tutorial-type post in the OpenOffice.org Community Forums; includes code for needed macros.