Developing Effective Reports

1. Developing Effective Reports Chapter 5 “Nothing succeeds like reports of success.”—Sue Sanders Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

2. Chapter Introduction • Reports • Reflect the information in the database • Summarize business activities • Use reports to format data in an attractive and informative layout for printing • Often based on data in multiple tables Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

3. Understanding Reports • Report • Presents information from one or more database tables • Printed format • Provide most options and advantages for printing database content • Organize and format information to create professional presentation • Include numeric and textual data • Maintain flexibility when displaying summary information Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

4. Understanding Reports (continued) • Accomplish goals with reports • Create printed copy of information used regularly • Distribute information to others • Customize organization and appearance of printed information • Group or summarize information for • Reporting to others • Calculating running totals • Group totals • Grand totals • Percentages of totals Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

5. Comparing Forms and Reports Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

6. Choosing the Appropriate Type of Report • Determine report purpose and audience • Before developing • Report types • Detailed • Grouped • Summary • Mailing labels • Multiple-column • Types of reports can contain other reports • Called subreports Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

7. Examples of Access Reports Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

8. Types of Reports Available in Access Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

9. Types of Reports Available in Access (continued) Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

10. Planning Basic Reports • Start by creating reports for various functions within pharmacy • Managers of areas can review reports • Comment on usefulness and appearance Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

11. Creating and Modifying Basic Reports • AutoReport Wizard • Create report that displays all fields and records in single table or query • Report Wizard • Guides you through steps of creating report • Based on one or more tables or queries • Asks questions about • Record sources • Fields • Layout • Format Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

12. Creating a Report Using AutoReport • AutoReport Wizards: • AutoReport: Columnar • AutoReport: Tabular • One or other may be easier to read • Naming reports • Use rpt prefix • Choose meaningful name Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

13. Columnar Report Created Using an AutoReport Wizard Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

14. Results of AutoReport in Print Preview Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

15. Creating a Report Using the Report Wizard • Provides quick way to select only fields you want to display in report • Based on one or more tables or queries • Select one of several layouts and styles for report • Create report with Report Wizard • Customize report in design view • Provides options for selecting fields Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

16. Creating a Report Using the Report Wizard (continued) • Pages • Fields • Grouping • Sorting • Layout and orientation • Style Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

17. Modifying a Report in Design View • Can also create reports from scratch in Design view • Control • Small object such as text box • Displays data or line to separate one record from another • Modify report in Design view • Switch to Print Preview to see effects of changes Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

18. Report Controls Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

19. Three Report Views Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

20. Modifying a Report in Design View (continued) • Report sections: • Report header • Page header • Group header • Detail • Group footer • Page footer • Report footer Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

21. Modifying a Report in Design View (continued) • View property sheet for section • Double-click section bar • Report caption • Name of report in title bar • Report design checklist: • Is report in format chosen for all reports? • Does title label caption need to be modified? • Have I changed report caption? • Can I read complete column headings or are they truncated? Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

22. Modifying a Report in Design View (continued) • Report design checklist: • Does all data in detail area appear or is some truncated? • Is report so wide that it should use landscape orientation? • Is vertical spacing too spread out or too close? • If report has many numbers does it use gridlines to make reading it easier? • Do any extra items on report detract from its appearance? Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

23. Modifying a Report in Design View (continued) • Report design checklist: • Do any errors or blank pages appear when report printed? • Would data have more information content if grouped? • Would summary data add to information content of report? Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

24. Common Section Properties Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

25. Common Report Properties Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

26. Moving Detail Fields to Multiple Lines on a Report • Increase length of detail section to make room for second row of text by • Dragging page footer section bar down • Move fields • Add logos or other graphics • Using image button Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

27. Creating a Custom Style for Reports • Click AutoFormat button on report design toolbar • Create custom AutoFormat based on report Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

28. Creating a Grouped Report Using a Single Table • Grouped report • Groups records based on values in one or more fields • Makes report more informative • Calculate totals and other values for each group • Create using Report Wizard • Only allows four grouping levels • Create using Design view • Up to 10 levels allowed • Group level determines how groups nested Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

29. Using the Report Wizard to Created a Grouped Report Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

30. Creating Labels Using the Label Wizard • Store name and address information in access database • Create simple report formatted to look like mailing label • Report extracts address data from table or query • Organizes it to print label for each address in record source • Use Label Wizard • Specify record source for mailing label report • Select type of label • Customize mailing labels using design view Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

31. Creating Labels Using the Label Wizard (continued) • Build prototype of label • By selecting necessary fields one at a time • Press enter key to move to next line • Specify sort order for labels Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

32. Level 1 Summary • Report • Printed version of data • Create using • AutoReport Wizard • Report Wizard • Use design view to customize appearance and data • Save design as AutoFormat Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

33. Level 2 Objectives:Developing Management Reports to Support Decision Making • Create a custom report • Add calculations to a report • Look at design view and properties Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

34. Creating Custom Reports • Custom reports • Require data from more than one table • Have calculated fields • Use summary statistics • Require parameter input at time report run Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

35. Creating a Report Using Fields from Multiple Tables • Limit records included in report or use parameter values • Base report on query • Resolving error messages in mailing labels • May see error message when previewing or printing mailing labels • Before modifying column widths • Check to see if data in labels all printing • Modify column widths to eliminate errors Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

36. Creating a Report Using Fields from Multiple Tables (continued) • Parameter queries allow user input • Dialog box requesting information to be used as criteria for retrieving records • Enter parameter [Prompt?] Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

37. Adding or Modifying Grouping Levels to a Report • Add grouping level after creating original report • Sort fields can also serve as grouping fields • Group header • Includes name of group • Group footer • Includes count or subtotal for records in group • Use sorting and grouping button • Each report can have up to 10 sort fields • Any sort field can also be grouping field Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

38. Sorting And Grouping Dialog Box Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

39. Improving the Information Content of Grouped Reports • Group header • Area on report used to indicate start of new group • Text or controls located in group header shown once for group • Group footer section • Shown only once at end of any group detail • Most often used to contain summary data such as subtotals for data in group Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

40. Improving the Information Content of Grouped Reports (continued) • Tabular reports that have large amounts of data • Often contain more information content if report uses grouping • Organize data into smaller segments • Easier to comprehend • Calculated fields • General format • Use equal sign followed by calculation • Most developers recommend creating calculation in query • Use query as basis for report Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

41. Grouping on Date and Time • Accounting reports • Usually prepared for monthly quarterly and annual time periods • Report Wizard has built-in grouping options for date/time fields • Use same date field for multiple time periods • Create query that shows only last refill for any customer within household • Use totals button Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

42. Creating a Sales Report Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

43. Grouping on Date and Time (continued) • Last function • Returns value for last row encountered for each group Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

44. Changing the Record Source and Adding Fields • Report designers often need to change source of data for report after it is created • Change record source property in report’s property sheet • Copy object such as query form, label, or macro • Modify it for another use • May need to remove fields not included in new record source • Add field from underlying table or query to report • Drag field name from field list Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

45. Forcing a Page Break After Each Group • Print data for one person only • Insert page break control to force page break • Use page break button • Insert page break in group footer • Keep together setting in sorting and grouping dialog box • Keep heading and at least part of detail together Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

46. Level 2 Summary • Custom reports • Grouping organizes information • Group on any sort field • Group header/footer • Modify report in design view • Change data source • Add fields Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

47. Level 3 Objectives:Designing Reports for Data Analysis • Define conditional formatting rules in a report • Develop reports with subreports • Develop graphs Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

48. Creating Advanced Reports in Design View • Create report in Design view • Click Reports on Objects bar • Click New button • Click Design view • Specify Data Source • Can also specify later • Add report header and footer if desired • Drag fields to detail section from field list • Add grouping and/or sorting in sorting and grouping dialog box Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

49. Adding Charts to Reports • Access charts • Helpful for analyzing performance data over time series • Use Chart Wizard Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

50. Modifying the Features of a Chart • Microsoft MSGraph • Built-in program to create charts in Chart Wizard • Use to modify charts • Save chart created by Form Chart Wizard as report • Use Save As dialog box • Click As list arrow • Click Report Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach