Lesson 12 – Building Advanced Reports

1. Lesson 12 – Building Advanced Reports

2. Lesson Objectives • Create a report using a wizard. • Create a report with a subreport. • Add expressions to a report. • Add PivotCharts to a report. • Work with image controls.

3. Creating a Report Using a Wizard Exercises • Create a Report using a Wizard • Format Controls • Add Gridlines • Add Custom Colors

4. Create a Report Using a Wizard • Quick way to create a report • Can add special grouping intervals

5. Format Controls • Make sure all data can be seen

6. Add Gridlines • Gridlines are available if controls are part of a control layout

7. Add Custom Colors • Color Builder uses two methods • RGB (Red, Green, and Blue) • HSL (Hue, Saturation, and Luminosity) • Each setting has a range of 0-255

8. Add Custom Colors • Preview the finished report

9. Creating a Report with a Subreport Exercises • Create a Subreport • Prepare a Main Report • Add a Subreport • Modify a Subreport

10. Create a Subreport • Subreports • Embedded into Main report • Share a common field and relationship • Main report – One side of the relationship • Subreport – Many side of the relationship

11. Prepare a Main Report • Users should create the main report before trying to add the subreport

12. Add a Subreport Control • New subreport has an attached label

13. Add a Subreport Control • For data to change in the subreport when the main report moves from record to record, there must be a common field

14. Modify a Subreport • Main and subreports should match formatting

15. Adding Expressions to a Report Exercises • Create a Concatenated Expression • Add an UCase Function • Add an IIF Function • Add an IsNumeric Function • Format the Report

16. Create a Concatenated Expression • Syntax is the set of rules by which the words and symbols in an expression are correctly combined • To Concatenate is to join text

17. Add an UCase Function • In the past you could just use the “>” format to show all capitals letters • In expression use the UCase function

18. Add an IIF Function • IIF Function • Evaluates an “True/False” expression • If true do one action • If false do another action

19. Add an IsNumeric Function • IsNumeric function • Returns “True” if data is a number • Returns “False” if data is not a number

20. Format the Report

21. Adding PivotCharts to a Report Exercises • Create a PivotChart • Create a Report using Report Design • Add PivotCharts to a Report • Modify Sections

22. Create a PivotChart • PivotCharts • Can include aggregate functions • Summarize data • Can be dynamic • Reports don’t have a PivotChart View • Forms have a PivotChart View • Subforms can be added to reports

23. Create a PivotChart • Modify titles and scales before adding a PivotChart to a report

24. Create a Report Using Report Design • Main reports don’t need to be elaborate when you plan to add a subform

25. Add PivotChart to a Report • Subreports need a common field if data in the subreport needs to change when the main report changes records

26. Modify Sections • Subreport display information differently depending on which section it is located • Format the Page Number control

27. Working with Image Controls Exercises • Add a Watermark • Add a Logo • Add an Image Control

28. Add a Watermark • A Watermark is an image that displays in the background • It can make it hard to see data • Used for cover pages • Use Print Preview to view this feature

29. Add a Logo • This control defaults to the Report Header • Image controls have different size modes • Clip mode – Original size • Stretch mode – Fits to frame • Can distort image • Zoom mode – Fits to height or width • Don’t distort image

30. Add an Image Control • Image controls are not bound to the recordset

31. Lesson 12 Summary • Reports are the database object most often seen by people inside or outside the company. • Using the Report Wizard is often the easiest way to create a simple report. • Creating professional-looking reports requires efforts to format all controls and sections properly. • When controls are part of a control layout, you can add gridlines to separate the controls visually with borderlines. • Color Builder is an Access tool for selecting colors from a palette or creating custom colors. • A common method of organizing hierarchical information in a report is to include a subreport.

32. Lesson 12 Summary • A subreport is a control inserted into a main report. • The main report and subreport should be formatted to appear as a single unified report. • Expressions allow you to create or use values that are not included in your data. • The UCase function returns a string that has been converted to uppercase text. • An IIF function evaluates a “true/false” expression and then performs one of two actions. • Functions that require numeric values to perform calculations cannot use text values for input. • Reports that display numeric values should be formatted to follow standardized accounting standards.

33. Lesson 12 Summary • A PivotChart, similar to a PivotTable, summarizes information from selected fields in a data source. • A PivotChart is added to a report as a subreport control. • The information displayed in a PivotChart depends on the section in which it is placed. • A watermark is an image control that displays in the background of a page on a form or report.