Chapter 7: Creating Database Reports

1. Chapter 7: Creating Database Reports Guide to Oracle 10g

2. Lesson A Objectives After completing this lesson, you should be able to: • Use the Reports Builder report styles • Use the Report Wizard to create a report • Configure the appearance of a report • View a report in a Web browser • Create a master-detail report Guide to Oracle 10g

3. Lesson A Objectives (continued) • Create a custom template • Apply a custom template to a report Guide to Oracle 10g

4. Introduction to Reports Builder Database Reports • Reports Builder • Allows application developers to create reports that display data from Oracle 10g database • Developer10g • Reports Builder allows developers to preview and distribute reports in variety of different formats including: • Web pages • Portable document format (.pdf) files Guide to Oracle 10g

5. Introduction to Reports Builder Database Reports (continued) • Layout styles: • Tabular • Form • Mailing label • Form letter • Group left group above • Matrix • Matrix with group Guide to Oracle 10g

6. Using the Report Wizard to Create a Report • Steps: • Specify data that report displays • Select report style • Configure report properties and layout • Report Wizard pages: • Welcome • Report type • Style • Data source Guide to Oracle 10g

7. Using the Report Wizard to Create a Report (continued) • Report Wizard pages (continued): • Data • Fields • Shows data fields that SQL query returns • Totals • Labels • Template • Finish Guide to Oracle 10g

8. Configuring the Report Appearance • Use Reports Builder environment to: • Modify report appearance • Customize report features • Paper Design window • Default editing environment • Refine appearance of reports • Shows how report will appear on paper • Can preview as Web page Guide to Oracle 10g

9. Configuring the Report Appearance (continued) • Data Model button • Modify report data • Web Source button • Displays underlying HTML code • Defines how report will appear in Web browser • Paper Layout button • Opens report in Paper Layout view Guide to Oracle 10g

10. Configuring the Report Appearance (continued) • Paper Parameter Form button • View parameter form • Parameter form • User selects input parameter values to customize form appearance and functionality at runtime • Save report design specification using variety of formats • Reports Builder design files • Rdf extension Guide to Oracle 10g

11. Using the Report Wizard in Reentrant Mode • Reentrant mode • Modify SQL query • To use ORDER BY clause Guide to Oracle 10g

12. The Reports Builder Object Navigator Window • Reports Builder object navigator • View report components in hierarchical tree structure • Access different components in Reports Builder environment • Access components of individual report • Reports • Top-level node Guide to Oracle 10g

13. The Reports Builder Object Navigator Window (continued) • Other objects: • Templates • PL/SQL libraries • Debug actions • Stack • Built-in packages • Database objects Guide to Oracle 10g

14. Reports Builder Object Navigator Guide to Oracle 10g

15. Modifying the Report Appearance in the Paper Design Window • Report title appears in report margin • Area on page beyond where report data appears • Adjust column’s width • Select column • Drag to make it wider or narrower • Specify format masks for fields that display number and date data • Property Inspector Guide to Oracle 10g

16. Modifying the Report Appearance in the Paper Design Window (continued) • Import graphic images into reports • Same as importing graphic images into forms Guide to Oracle 10g

17. Closing and Reopening Reports • To close report: • Click File on menu bar • Click Close • To open report: • On Welcome to Reports Builder dialog • Click Open an existing report option button • Click OK Guide to Oracle 10g

18. Viewing the Report as a Web Page • Preview report as Web page • Click Run Web Layout button • Web page source code • File with .htm extension • Contains HTML commands and text to represent report content and formatting Guide to Oracle 10g

19. Viewing the Report as a Web Page (continued) • View formatting changes in Web page output • Preview report using either: • Paginated HTML • Paginated HTMLCSS format Guide to Oracle 10g

20. Creating a Master-Detail Report • One record has many associated detail records • Through foreign key relationship • Use Report Wizard to specify report style and data values • Report’s SQL query must retrieve all master and detail values • Report Wizard displays groups page Guide to Oracle 10g

21. Specifying the Style and Data • Create new report in object navigator • Select Reports node • Click Create button • Styles for creating master-detail reports • Group left • Group above Guide to Oracle 10g

22. Using the Groups Page to Specify Master-Detail Relationships • Data in master-detail report has multiple levels • Each data level represents group • Top-level (master) group is level 1 • Specify report groups • Move fields for each group from available fields list to group fields list Guide to Oracle 10g

23. Report Templates • Many reports that have similar appearance • Useful to create custom template to specify report appearance • Avoid performing same formatting tasks over and over again Guide to Oracle 10g

24. Creating a Custom Template • Custom template defines: • Font sizes and styles for report’s • Title • Column headings • Data values • Text and background colors • Boilerplate objects • Store template definition in template definition file that has .tdf extension Guide to Oracle 10g

25. The Paper Layout Template Editor Window • Environment within Paper Layout window • Used for editing templates • Similar to forms builder layout editor • Areas within painting region in report template • Margin • Body Guide to Oracle 10g

26. Paper Layout Template Editor Guide to Oracle 10g

27. Editing Template Margins • Open margins for editing • Click Margin button Guide to Oracle 10g

28. Editing the Template Body • Report body has two types of attributes: • Default • Override • Frame • Object that encloses similar objects within report • Parent frame • Frame that directly encloses object Guide to Oracle 10g

29. Applying Custom Templates to Reports • Modify existing report’s template by • Opening Report Wizard in reentrant mode • Changing template specification Guide to Oracle 10g

30. Applying Templates by Specifying the Template Filename • Select Template file specification option button on Report Wizard template page • Enter full folder path and filename of template file • Including drive letter • Quick and easy way to apply custom templates • Disadvantage • Template file must be available at specified file location whenever report file opened Guide to Oracle 10g

31. Registering Custom Templates in Reports Builder • Appears in predefined templates list on template page • Advantage • Don’t have to specify path to template file • File does not always have to be available • Two-step process: • Modify developer user preferences file • Copy template file to Reports Builder templates folder Guide to Oracle 10g

32. Registering Custom Templates in Reports Builder (continued) • User preferences file • Template description • Template filename • Template page displays thumbnail images of predefined templates • Create thumbnail image of custom template • Save screenshot as Bmp file Guide to Oracle 10g

33. Lesson A Summary • Report • Summary view of database data that users can view on screen or print on paper • Create report • Specify data to appear in report • Select report style • Configure report properties and layout • Paper design window • The Reports Builder Object Navigator Guide to Oracle 10g

34. Lesson A Summary (continued) • Create master-detail report using Report Wizard • Report template • Defines report appearance Guide to Oracle 10g

35. Lesson B Objectives After completing this lesson, you should be able to: • Describe the components of a report • Modify report components • Modify the format of master-detail reports • Create parameters to allow the user to customize report data Guide to Oracle 10g

36. Report Components • Data Model • Specifies data that report displays • Paper Layout view • Displays report components as symbolic objects • Report frames • Group related report objects Guide to Oracle 10g

37. The Data Model Window • Shows report’s SQL query and associated record groups • Report record group • Set of records represents data fields that query retrieves • Simple tabular report has single record group • Master-detail report has multiple record groups • Components in report Data Model have properties • Inspect using Property Inspector Guide to Oracle 10g

38. Report Record Group Column Types Guide to Oracle 10g

39. The Data Model Window (continued) • Group filter • Uses some criteria to limit number of records that report query retrieves • To create: • Assign value to filter type property Guide to Oracle 10g

40. Understanding Report Objects • Repeating rows • Each row shows same data fields with different data values • Report sections: • Header • Main • Trailer Guide to Oracle 10g

41. Report Frames • Frames • Containers for grouping related report objects • Set specific properties for group of objects • Rather than having to set property for each item • Group frame • Encloses repeating frame and optional header frame Guide to Oracle 10g

42. Report Frames (continued) • Repeating frame • Encloses repeating data rows • Has variable sizing • Header frame • Encloses all column headings for record group • Move object outside enclosing frame • Error message appears in Reports Builder Guide to Oracle 10g

43. Report Frames (continued) • Report Wizard derives frame names from names of associated record groups • Select specific report frame in Paper Layout window • Select item that in frame • Select item’s parent frame by clicking Select Parent Frame button • Change frame properties on frame Property Inspector Guide to Oracle 10g

44. Components of a Master-Detail Report • Multiple record groups • Multiple group frames Guide to Oracle 10g

45. Master-Detail Data Model • More complex than Data Model for single-table report • Contains multiple record groups • Data Model groups report record groups according to master-detail relationships • Each data link between two record groups represents master-detail relationship Guide to Oracle 10g

46. Master-Detail Data Model Guide to Oracle 10g

47. Master-Detail Report Frames • Each record group has associated group frame • Group frames for more detailed record groups nested inside group frames for less detailed record groups • Frame relationships can be hard to see • Many frames appear directly on top of each other • Open frame Property Inspector immediately after selecting frame • To check frame name Guide to Oracle 10g

48. Modifying Master-Detail Report Properties • Page break between sets of repeating records • Open Property Inspector for repeating frame • Change Maximum Records per Page property to 1 • Confine mode • Determines objects can be moved outside enclosing frames • Always safest to leave enabled Guide to Oracle 10g

49. Modifying Master-Detail Report Properties (continued) • Flex mode • Enclosing frame automatically becomes larger • When enclosed object moved beyond enclosing frame’s boundary • Overrides confine mode • When moving report layout field • Automatically resizes all surrounding frames • Works well when to make a frame longer • But not wider Guide to Oracle 10g

50. Modifying Master-Detail Report Properties (continued) • Elasticity • Determines whether field’s size fixed • Or whether field can expand or contract automatically • Depending on height and width of retrieved data value Guide to Oracle 10g