Creating a Report

From NeoWiki

Forms are one way to arrange the data in our database in a more helpful manner. But forms often work best on the screen. Reports are another way to arrange our data. Reports are more amenable to printing. We can create reports using 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 form 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

1. 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.
2. Suppose you wanted to group instead by StateOrProvince and Location, 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. The follow the instructions above, grouping instead by StateOrProvince and Location.
3. Say you wanted to printed a report that listed all the birds in the AOU birdlist that included 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 [Using An Outer Join in a Query|Left or Right Outer Join] to join the AOU_Birdlist and Sightings tables, make sure the join type selects all the records of the AOU_Birdlist table.

      if you have from the AOU_Birdlist table, you will need to first make a side query combining the order, family, and English name 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 ID and BirdName (or 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 (or Bird Name)

   3. Include Voice and Sight fields in the query, and use the Count function on both. See Using the Group Function in a Query.
   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(Voice) 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 and paste it into the box.