Creating Database Reports

1
Creating Database Reports

• Chapter 7

2
Lesson A Objectives

• Understand 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
• Create a custom template
• Apply a custom template to a report

3
Introduction to Reports Builder Database Reports

• Reports Builder allows developers to distribute
  reports as
• Web pages
• .pdf files
• Printed
• And other formats
• Layout styles include
• Tabular
• Form
• Mailing Label
• Form Letter
• Matrix

4
Sample Matrix Report
5
Using the Report Wizard to Create a Report

• Basic steps using Report Wizard
• Specify the data that the report displays
• Select the report style
• Configure the report properties and layout

6
Paper Design Window
7
Modifying the Report Appearance

• Use Reports Builder environment to modify the
  report appearance and customize the report
  features
• Paper design window shows how report appears on
  paper
• To view as web page
• preview paper design as web page
• Make sure form displays correct values before
  editing design
• Use the Paper Design window to change fonts, item
  sizes, etc.

8
Reports Builder Object Navigator
9
Viewing the Report as a Web Page

• To preview a report as a Web page
• Click the Run Web Layout button on the Reports
  Builder toolbar
• Creates the Web page source code for the report
• Displays as Page Design view without any
  formatting changes
• To view formatting changes preview the report
  using either a Paginated HTML or a Paginated
  HTMLCSS format

10
Viewing a Report as a Web Page
11
Creating a Master-Detail Report

• Report can display master-detail data
• SQL query must retrieve master and detail data
• Use Groups page to define master and detail
  groups
• Two report styles
• Group left master records appear on the left
  side of the report detail records appear in
  columns to the right of the master records
• Group above detail records appear below the
  master records
• Place column names in Levels on Available Columns
  page

12
Example Grouped Report
13
Creating Groups/Levels
14
Report Templates

• Custom templates store common formatting
  information such as
• Font
• Background color
• Graphics
• Stored in .tdf file
• Use Paper Layout Template Editor to edit

15
Paper Layout Template Editor
16
Applying Custom Templates to Reports

• Templates are applied to reports using Reports
  Wizard Template page
• Use Template file specification option button to
  select a custom template
• Or register template as a predefined template
• Modify the Developer user preferences file, so
  the custom template appears in the Predefined
  Templates list
• Copy the template file to the Reports Builder
  templates folder

17
Lesson B Objectives

• Understand 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

18
Report Components

• Data Model
• Specifies the data that the report displays
• Paper Layout view
• Displays the report components as symbolic
  objects
• Report frames
• Group related report objects

19
Data Model Window
20
Data Model Window

• Modify data model properties using Property
  Inspector
• Can modify the reports SQL query
• Create a group filter to limit the number of
  records that a report query retrieves
• Assign a value to the Filter Type property in the
  report record groups Property Inspector
• Faster to limit retrieved values with SQL query
  where clause

21
Paper Layout Window
22
Report Display

• Reports have a header, main section and footer
• Frames
• Containers for grouping related report objects
• Use to set specific properties for a group of
  objects
• Record group
• Have corresponding group frame
• Encloses a repeating frame and an optional header
  frame

23
Components of a Master-Detail Report

• Master-detail reports
• Display master-detail data
• Multiple record groups master in one, detail in
  another
• Multiple group frames

24
Master-Detail Data Model
25
Master-Detail Report Layout
26
Master-Detail Group Frame Relationships
27
Report Parameters

• Specifies how a report appears
• Specifies the reports behavior when it runs
• System parameters
• Specify properties that control how the report
  appears in the user display and how the report
  application environment behaves
• Examples currency symbol, whether the print
  dialog box opens when the user prints the report
• User parameters
• Allow the user to select values that specify the
  data that the report displays
• Example allow the user to select a specific term
  ID value from a list, and the report would then
  display class lists for that term only

28
System Parameters
29
User Parameters

• User selects from parameter list
• Inserted into search condition
• To create
• Create the user parameter
• Create the parameter list
• Modify the report query so it uses the parameter
  as a search condition

30
Lesson C Objectives

• Display image data in a report
• Manually create queries and data links
• Create summary columns
• Create formula columns
• Create reports that display formatted data in a
  Web browser window

31
Displaying Image Data In Reports

• Retrieve and display LOB image data on reports
• Use Property Inspector to change File Format to
  Image to display image data

32
Report with Images from the Database
33
Creating Report Queries and Data Links Manually

• To create a query manually
• Open the Data Model window
• Select the SQL Query tool on the Data Model tool
  palette
• Click in the painting region, and type the SQL
  query
• To create a data link
• Select the Data Link tool on the Data Model tool
  palette
• Draw a link between linked objects
• Types of data links
• Query to Query
• Group to Group
• Column to Column

34
Manually Creating a Query and Data Link
35
Creating Reports that Display Calculated Values

• Formula columns
• Display values that PL/SQL functions calculate
  using report data field values as input
  parameters
• Summary columns
• Perform summary functions (such as SUM,AVG, or
  MAX) on report data fields

36
Example of Calculated Values
37
Creating Formula Columns

• Formula columns
• Display a value that a user-defined function
  returns as a result of performing computations on
  report data values
• To create
• Create the formula column in the report Data
  Model
• Write the user-defined function that returns the
  calculated value for the formula column
• Create a layout field in the report to display
  the formula column value

38
Syntax for Formula Column
39
Creating Summary Columns

• Summary columns
• Return a summary value, such as the sum or
  average, of a series of data fields in a
  repeating frame
• Create summary columns for any report data field
  using the Totals page in the Report Wizard
• Can also create summary columns manually

40
Creating Summary Columns with Reports Wizard
41
Displaying Formatted Reports as Web Pages

• Use Oracle9i Application Server to generate
  reports dynamically
• See Figure 7-70

42
Summary

• Reports Builder creates formatted reports using
  database data
• Reports are drawn using the Paper Design Window
• Templates can be created to format reports
• Reports can be created to display master-detail
  data
• Report columns can be set to display
  calculations, formulas and summary information
• Reports can be generated in paper, pdf, or html
  format
• Dynamic reports are generated using Oracle9i
  Application Server