Creating a Report

From NeoWiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 03:39, 31 October 2007 (edit)
Lorinda (Talk | contribs)
m (→Variations - change numbering/bullet combinations)
← Previous diff		 Revision as of 21:36, 31 October 2007 (edit) (undo)
Lorinda (Talk | contribs)
m (correct typos; improve phrasing)
Next diff →
Line 1: Line 1:
{{TBBA Header}} {{TBBA Header}}
-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. +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== ==Creating a Report Based on a Table or Query==
#In the main database document window, click on the {{prefName|Reports}} icon in the left hand column. #In the main database document window, click on the {{prefName|Reports}} icon in the left hand column.
#Click on {{prefName|Use Wizard to Create Report...}} #Click on {{prefName|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 {{Menu|Report Wizard...}}+#:'''Note:''' you can also access the report wizard by control-clicking or right-clicking on a query or a table (when {{prefName|Queries}} or {{prefName|Tables}} is selected in the left hand column) and selecting {{Menu|Report Wizard...}}
-#When the {{Window|Report Wizard}} opens, select a query or table form the drop-down. In this case, choose {{prefName|Query: All Sightings}}.+#When the {{Window|Report Wizard}} opens, select a query or table from the drop-down. In this case, choose {{prefName|Query: All Sightings}}.
#We want all the fields in the {{Section|Available Fields}} section to be in the report. Click on the {{Button|>>}} to move them all at once. #We want all the fields in the {{Section|Available Fields}} section to be in the report. Click on the {{Button|>>}} to move them all at once.
#:To move one field over at a time, use the {{Button|>}} button #:To move one field over at a time, use the {{Button|>}} button
Line 19: Line 19:
#For this report, you want to leave the orientation set for {{prefName|Landscape}}. #For this report, you want to leave the orientation set for {{prefName|Landscape}}.
#Click {{Button|Next>}} #Click {{Button|Next>}}
-#In the final pane, give the report a name. I choose '''All Sightings By Bird Name"+#In the final pane, give the report a name. I choose '''All Sightings By Bird Name'''
#Leave the radio buttons set for {{prefName|Dynamic report}}. This way the report will update as data changes. #Leave the radio buttons set for {{prefName|Dynamic report}}. This way the report will update as data changes.
#Because the wizard makes some odd choices for column widths, select {{prefName|Modify report layout}}. #Because the wizard makes some odd choices for column widths, select {{prefName|Modify report layout}}.
Line 34: Line 34:
==Variations== ==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]]. *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''' 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'''.+*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'''. Then follow the instructions above, grouping instead by '''StateOrProvince''' and '''Location'''.
*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: *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:
*#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 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. *#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 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.
Line 45: Line 45:
*#:{{preBox|<nowiki>[>=1]"☑";[=0]"";0</nowiki>}} *#:{{preBox|<nowiki>[>=1]"☑";[=0]"";0</nowiki>}}
*#: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 =. *#: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.+*#: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.
*#If you included a '''MIN(Date)''' field, you will need to set its formatting to a date format for it to appear correctly. *#If you included a '''MIN(Date)''' field, you will need to set its formatting to a date format for it to appear correctly.

Revision as of 21:36, 31 October 2007

This article is part of the To Base and Back Again tutorial series.

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 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 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.
Retrieved from "https://neowiki.neooffice.org/index.php/Creating_a_Report"
Personal tools