1

Guide to Oracle10G

CHAPTERCHAPTER 7 7::Creating Database ReportsCreating Database Reports

77

2

Reports

Snapshot summary of database dataViewed on screenPrinted on paperPrinted to a file and distributed

electronically

77

3

Reports

A report can:Display SQL query dataDisplay summary columnsDisplay calculated values based on

formulas applied to database dataBe formatted to look like an invoice or

other business document

77

4

Type of Reports

Tabular (table-format)Form-like (looks like a form)Mailing labelsForm lettersMatrix

Data value appears at the intersection of a column and row heading

77

5

Types of Reports

Master-detail: Group left

77

Figure 7-2

6

Types of Reports

Master-detail: Group above

77

Figure 7-3

7

Creating a Report Using the Report Wizard

1. Enter the SQL query to retrieve the report data

2. Choose the display fields

3. Specify the report master-detail groups

4. Specify the field prompts

5. Select a predefined template77

8

Predefined Templates

Specifies report appearance characteristics Font sizes, types and colors Graphic images Background shading Page numbering Date report is created

77

9

Report file types

Reports: .rdf Templates: .tdf

77

10

Using Format Masks in Reports

Format masks can be applied to: NUMBER fields DATE fields

Format masks cannot be applied to text fields

77

11

Report Builder Windows

Live Previewer: Shows how report will look when displayed or printed

77

12

Report Builder Windows

Layout Model: Shows reports components symbolically

77

13

Report Builder Windows

Data Model: Shows report data components

77

Query

Recordgroups

14

Data Model Components

Record group: Set of records with the same column headings

Master-detail reports have multiple record groups

Individual fields are

called columns Default record group

name: G_<first column name>

77

Column

15

Report Sections

Clearwater Traders

Sales Report

*** End of Report ***

77

Header: optional title page

Main Section:report dataand computations

Trailer:optional final page

16

Report Sections

77

Margins

Main Section has margins for values that are displayed on each report page

Titles Dates Page numbers

Page 1 of 5

17

Navigating in the Report Sections

Section navigation buttons

77

Header

MainSection

Trailer

Main SectionMargins

18

Running a Report from a Form

Reports are often run from Form Builder applications in integrated database systems

Steps for running a report from a form:1. Create a bind parameter(s) in the report to accept an

input parameter(s) from the form

2. Create a parameter list in the form to pass the input parameter(s) from the form to the report

3. Run the report from the form using the RUN_PRODUCT procedure77

19

Running a Report from a Form

Creating a bind parameter Substitute one or more search conditions in the

report SQL query with a parameter name prefaced with a colon (:)

77bind parameters

Figure 9-95

20

Running a Report from a Form

Creating a parameter list1. Declare the parameter list variable in the DECLARE

section

2. Create the list

77

bind parametersdeclaring the list

creating

the list

21

Running a Report from a Form

Parameter lists can only pass character or record group data To add parameters to the parameter list using the

ADD_PARAMETER procedure:

ADD_PARAMETER(<list>, <key>, <paramtype>, <value>);

List: ID of the parameter list Key: Name of the parameter in the report Paramtype: Values can be TEXT_PARAMETER (for character

values) or DATA_PARAMETER (for record groups) Value: Data value to be passed in parameter list

77

22

Running a Report from a Form

Example of the ADD_PARAMETER procedure:

77

List Key Paramtype Value

Figure 9-96

23

Running a Report from a Form

Running the report using the RUN_PRODUCT procedureRUN_PRODUCT(<product>, <document>,

<communication mode>, <execution mode>, <location>, <parameter list ID>, <display>);

Product: Application to run Value can be REPORTS or GRAPHICS

Document: Complete path and filename of the report .rdf file Communication mode

SYNCHRONOUS: Control returns to the form only after report is closed

ASYNCHRONOUS: User can toggle between report and form77

24

Running a Report from a Form

Running the report using the RUN_PRODUCT procedureRUN_PRODUCT(<product>, <document>,

<communication mode>, <execution mode>, <location>, <parameter list ID>, <display>);

Execution mode RUNTIME: Called product's runtime environment is started BATCH: Called product is displayed directly on the form

Location FILESYSTEM: Report file stored in the filesystem DATABASE: Report file stored in the database

Parameter list ID: ID of the parameter list to be passed Display: Only used when running a GRAPHICS product

Value is always NULL when running a report77

25

Running a Report from a Form

Example of the RUN_PRODUCT procedure:

77

Product

Execution

Mode

Location Display

Document Communication

Mode

Parameter

List ID Figure 9-99