Pfs-dao Tao Oracle Report

PFS §µo t¹o oracle report

1

Ch­¬ng tr×nh häc Giíi thiÖu X©y dùng report b»ng wizard Bµi thùc hµnh 1 X©y dùng report b»ng c¸c c«ng cô cña Report Builder 6 Bµi thùc hµnh 2 X©y dùng report nhiÒu phÇn Bµi thùc hµnh 3 2

Ch­¬ng tr×nh häc Giíi thiÖu X©y dùng report b»ng wizard Bµi thùc hµnh 1 X©y dùng report b»ng c¸c c«ng cô cña Report Builder 6 Bµi thùc hµnh 2 X©y dùng report nhiÒu phÇn Bµi thùc hµnh 3 3

1. Giíi thiÖu Giíi thiÖu chung Giíi thiÖu vÒ Report builder C¸c thµnh phÇn chÝnh trong Report builder

4

ThiÕt kÕ b¸o c¸o Tr­íc khi b¾t tay vµo lµm b¸o c¸o §Æc t¶ chung d÷ liÖu cÇn dïng ®Æc ®iÓm chÝnh

CÊu tróc

Khu«n d¹ng

C¸c lo¹i b¸o c¸o 1. D¹ng b¶ng Danh s¸ch s¶n phÈm M·s¶n phÈm M« t¶

Gi¸

C¸c lo¹i b¸o c¸o Danh s¸ch kh¸c hµng quan träng Tªn kh¸ch hµng M· s¶n phÈm Gi¸

Tªn kh¸ch hµng M· s¶n phÈm Gi¸

2. master + detail

C¸c lo¹i b¸o c¸o Thèng kª vÒ kh¸ch hµng Kh¸ch hµng Hµng mua chÝnh Hµng mua trong 6 th¸ng M· SF

Gi¸

Ho¸ ®¬n sèNgµy Tæng tiÒn

3. master + nhiÒu detail

C¸c lo¹i b¸o c¸o Danh môc kh¸c hµng M·Sp Kh¸ch hµng

4. Ma trËn

Retrieving and Sharing Data Keep database access to a minimum Consider report structure and number of queries Make effective use of common code and objects

1. Giíi thiÖu Giíi thiÖu chung Giíi thiÖu vÒ Report builder C¸c thµnh phÇn chÝnh trong Report buildÎ

11

Ch¹y mét b¸o c¸o  Sö dông ch­¬ng tr×nh RWRUN60 ch¹y tõ: Menuch­¬ng tr×nh Menu trong Form øng dông Nót bÊm trong Form øng dông Tõ dßng lÖnh: Start>Run>RWRUN60

Tham sè ch¹y report KÕt nèi víi CSDL (Oracle Server) NhËp c¸c tham sè

C¸c kiÓu thÓ hiÖn cña b¸o c¸o

Mµn h×nh: ®Þnh d¹ng theo font hiÓn thÞ Xem tr­íc: ®Þnh d¹ng theo font m¸y in File: Theo d¹ng file (.PDF .HTM .TXT...) Th­ ®iÖn tö: t­¬ng thÝch MAPI

Previewing Reports 1

2

3

4

Previewing Reports 1

2 3

4

Live Previewer lµ g×?  Xem tr­íc report cho phÐp söa trùc tiÕp trªn report ®ang xem Thùc Thùc sù lµ kiÓu WYSIWYG So¹n So¹n th¶o dÔ dµng: Nh×n thÊy! Click chuét! Söa ®æi! Cached Cached report data

Cöa sæ Live Previewer 1 2 3

4

Modifying a Report Common modifications Align Align columns Set Set format masks Manipulate Manipulate objects Edit Edit text Modify Modify visual attributes Highlight Highlight data using conditional formats Insert Insert page numbers and current date

Aligning Columns

Point and select

Choose “end justify”

Setting a Format Mask Change Change format masks from the stylebar

1 2 3 4 5

Manipulating Objects Clear Clear fields Move Move fields Resize Resize fields

• Flex mode adjusts layout during changes.

Modifying Visual Attributes Modify Modify text colors and fonts Edit Edit text Set Set fill, patterns, and colors Use Use the Parent Frame tool to select an enclosing object -Sales Report -

Sales Report by Area

Year 1993 1993 1993

Region Sales Americas 200 Europe 400 Asia Pacific 770

Year 1993 1993 1993

Region Sales Americas 200 Europe 400 Asia Pacific 770

Year 1994 1994

Region Americas Europe

Year 1994 1994

Region Americas Europe

Sales 210 390

Sales 210 390

Sales Report by Area Year

Region

Sales

1993 1993 1993 Year

Americas 200 Europe 400 Asia Pacific Sales 770 Region

1994 1994

Americas Europe

210 390

Applying Conditional Formatting

Inserting Page Numbers, Dates, and Times Inserted Inserted easily Customizable Customizable extensions

Before

After

1. Giíi thiÖu Giíi thiÖu chung Giíi thiÖu vÒ Report builder C¸c thµnh phÇn chÝnh trong Report builder

25

Data and Layout

Report Editor

Report Level Objects

Report

Properties

Data Model

Triggers

Layout Model

Parameter Form

PL/SQL program units

Data Model Objects Data Model

Queries Groups Columns

Columns

Data links

Parameters

Layout Model Objects Layout Model Repeating Frames Fields Boilerplate frames OLE2

Parameter Form Objects Parameter Form

Fields

Boilerplate

Object Interrelationship 1 2 3

Select ...

Ch­¬ng tr×nh häc Giíi thiÖu X©y dùng report b»ng wizard Bµi thùc hµnh 1 X©y dùng report b»ng c¸c c«ng cô cña Report Builder 6 Bµi thùc hµnh 2 X©y dùng report nhiÒu phÇn Bµi thùc hµnh 3 33

2. X©y dùng report b»ng report wizard

Môc ®Ých Sau Sau bµi häc nµy b¹n cã thÓ: T¹o T¹o mét b¸o c¸o ®¬n gi¶n b»ng Report Wizard BiÕt BiÕt mét sè c¸ch t¹o report query T¹o T¹o c¸c phÐp tÝnh tæng trªn cét gi¸ trÞ cña b¸o c¸o ChØnh ChØnh söa d¹ng vµ néi dung cña b¸o c¸o T¹o T¹o c¸c d¹ng b¸o c¸o kh¸c nhau cã s½n trong wizard

KÝch ho¹t Report Wizard

T¹o b¸o c¸o d¹ng b¶ng Wizard Wizard Pages Report Style Query Type Data Query Fields Totals Labels Template

Sö dông Query Builder

T¹o mét Query

C¸c tÝnh n¨ng trong Query Builder C¸c C¸c tÝnh n¨ng cña Query Builder ®­îc nhóng trong Report Builder Giao Giao diÖn dÔ sö dông: Kh«ng cÇn gâ c©u lÖnh query chØ dïng chuét 1 2 3 4

5

6

Chän c¸c tr­êng hiÓn thÞ (Fields)

TÝnh tæng vµ viÕt nh·n (Labels)

Chän mét Report Template Theo Theo chuÈn cña c«ng ty T¹o T¹o ra mét report cã kiÓu chuyªn nghiÖp mét c¸ch dÔ dµng

Xem kÕt qu¶ b¸o c¸o 1 3 2

Ghi l¹i b¸o c¸o (Report Definition) H·y H·y th­êng xuyªn ghi l¹i thay ®æi File—>Save

File—>Save As...

xxx.rdf

yyy.rdf

Söa l¹i Wizard • Vµo menu Tools>Report Wizaren • Sè l­îng tab sÏ kh¸c nhau víi mçi lo¹i b¸o c¸o • Wizard b¶o toµn mäi thiÕt ®Æt ®· chän

T¹o mét b¸o c¸o míi 1

2

T¹o mét b¸o c¸o nhiÒu cÊp C¸c kiÓu d¹ng b¸o c¸o: Group Left, Group Above

Cã thªm trang wizard Groups

Break Report Labels Group Left Region

Department

Last Name Title

xxxxx

xxxxxx

xxxxxx xxxxxxx

Group Group Above Region xxxxxxxx Department xxxxx Last Name Title xxxx xxxxxxxx xxxxxxxx xxxx

xxxxx xxx

Creating Mailing Labels and Letters Wizard pages Style Data Text Template

Creating a Matrix Report Three additional Wizard pages Matrix rows Matrix columns Matrix cells

Summary Rapidly Rapidly create reports with the Report Wizard. Build Build queries using the Query Builder. Apply Apply templates to reports. Modify Modify reports by reentering the wizard. Create Create new reports of different styles.

Practice 4 Overview Creating Creating a Tabular report Modifying Modifying the report, to create a Group Above break report Creating Creating a Form Letter Creating Creating a Matrix report Creating Creating a Matrix with Group report

X©y dùng report b»ng c¸c c«ng cô

Objectives At the end of this lesson, you should be able to do the following Describe Data Model objects. Modify report hierarchy. Change the order of data in a group. Eliminate data from a report. Create supplemental rows of data, by adding queries. Link data from different queries. Reuse PL/SQL code to create a Ref cursor

The Data Model Objects

Modifying Properties of a Query

Modify SQL Query Statement

Add, rename, or delete columns.  Use column and table aliases. Remove or modify schema name. SELECT d.id, d.name, e.title, e.first_name||' '||e.last_name employee_name, e.salary*12 annual_salary FROM s_emp e, s_dept d WHERE e.dept_id = d.id

 Syntax error checks occur when:  Exiting SQL query statement  Compiling or executing a report

More Properties Aid maintenance and testing  Rename queries in complex reports  Add comments to describe queries  -- comment for single lines  /*comment*/ for multiple lines

 Set Maximum Rows to restrict data

Updating the Layout Update layout to reflect changes in Data Model. Choose Report Wizard. Alter tab pages.

Wizard destroys previous layout and creates new objects.

Groups Groups determine hierarchy and frequency Wizard creates default groups Default naming conventions You can change query name You can change group name

Developer-created groups for Control break reports Complex matrix reports

Group Hierarchy

1

2

Ordering Data in a Group

1 2 3

Query Modifications In a break report, data order is determined by: Break order columns in the break groups Columns that you specify in the ORDER BY clause SELECT d.id, d.name, e.last_name... FROM s_emp e, s_dept d WHERE e.dept_id = d.id ORDER BY 2, e.salary

Filtering Data in a Group

Using a Packaged Filter Reports provides two packaged filters: First Retrieves the first records for the group

Last Retrieves the last records for the group

Adding New Data

• Create query statement: – Enter valid syntax – Choose Query Builder – Choose Import SQL Query

Using an External Query Import an external query from a file Import SQL Query button

Create an external query file Notepad SQL*Plus Report Builder

Link to an external query source

Ref Cursor Queries

Oracle8 Objects: Object Columns

Oracle8 Objects: REF Columns

Oracle8 Objects: REF Columns

Viewing Output from Multiple Query Reports

Using Data Links  Equi-join S_Dept Id

Name

45

Operations

Last_Name Dept_Id

34

Sales

Dumas

45

Chang

34

Patel

34

id = dept_id

S_Emp

Using Data Links Compound join Multi-column primary or foreign key Define multiple equi-joins

Nonequi-join Primary key value between two other values Create two links:

s_emp.salary >= salgrade.losal s_emp.salary <= salgrade.hisal

Creating a Link

Other Data Link Methods Other methods of forming a link: Manual: Using a WHERE clause to form the link (SQL Queries + column objects) Manual: Using parameters to form the link (Ref Cursor Queries) Automatic: Using primary/foreign key constraints to form the link (SQL Queries)

Efficiency Implication of Multiple or Single-Query Outer joins Hierarchy Data ratio Client-Server environment

Objectives At the end of this lesson, you should be able to do the following: Describe various Data Model columns Display the contents of a file Identify characteristics of user-defined columns Create report summaries and subtotals Derive data, using a formula column Create and populate a placeholder

Data Model Columns

5 1 2 3 4

Maintaining Database Columns

1

Outputting Contents of a File

Creating a Column What type of value? Choose the correct column tool

• What frequency? Create in a group or at report level

C_xxx C_xxx

C_xxx

Summary Column Specific properties: Function Source Reset At Compute At

Datatype depends on Source datatype Page summaries: not supported in the wizard

Displaying Subtotals Reset At: G_dept Report

1

2

Displaying Percentages Reset At: G_emp Compute At: G_dept

1

2

G_emp Report

Resetting Summary Values Reset At

Data Model REPORT

G_DEPT

G_EMP

Grand Total

XXXX

XXXX

Sub Total

XXXX

G_DEPT

Running Total

G_EMP

Running Total

Running Total

Record Total

Group REPORT

Formula Column Performs a user-defined computation Executes a PL/SQL function Must return a value Can be Character, Number, or Date Returned value must match datatype function CF_SALCALCFormula return Number is begin return(my_function(:salary)); end;

Placeholder Column

CP_xxx

CF_xxx

An empty container at design time Populated by another object at run time Before Report trigger Formula column at report level Formula column in same group or below placeholder

Populating a Placeholder

Summary Column types Summary levels Reset At—resets to zero Compute At—% of Total only Formula columns return a value Placeholder columns hold a value

Objectives At the end of this lesson, you should be able to do the following: Distinguish the report regions and sections Design multipanel reports Describe the layout objects and relationships Modify an existing report layout Create variable length lines Create buttons and explicit anchors

Viewing the Regions Body/ Margin

Compensation Report

Header Section

Page 1 of 1

Main Section

Body/ Margin Body/ Margin

End of report

Trailer Section

Avoid Layout Errors Confine Mode

Flex Mode

Report Processing BEGIN M_G_master... f_xxx

f_xxx

REPEAT R_G_master

f_xxx

f_xxx

BEGIN M_G_detail... REPEAT R_G_detail f_xxx END M_G_detail

END M_G_master

Creating Layout Objects The tool palette contains: Standard GUI drawing tools Frame, repeating frame, field objects Other layout objects 1 6 2

7

3

8

4

9

5

10

Layout Tools • Pin a tool: double-click • Shift for constrained objects • Magnify • Frame Select • Additional Default Layout

Flex Lines Flex lines adjust to fit variable frames and repeating frames

Line Stretch with Frame

Creating Explicit Anchors Objects in the push path have implicit anchors (not visible in layout model).

• Explicit anchors override implicit anchors.

• All anchors appear in Object Navigator. Implicit (shaded) Explicit (solid)

Removing Spacing Layout F_LAST_NAME

Output F_JOB

SCHWARTZ

Store Clerk

PRINT FOR ALL EMPLOYEES

PRINT FOR ALL EMPLOYEES

PRINT FOR SALES PEOPLE ONLY

PRINT FOR ALL EMPLOYEES

PRINT FOR ALL EMPLOYEES

Create anchor from child to parent Collapse Vertically = Yes

Objectives At the end of this lesson, you should be able to do the following: Create a link file Modify common layout properties Modify specific layout properties

Layout Object Properties Four objects with common properties: Frame Repeating Frame Field Boilerplate

Some common properties affect: Sizing Pagination Frequency of display

Sizing Objects Elasticity fixed expand

contract variable

Layout icons Vertical Horizontal

Layout Object Relationships

1

2 3

Pagination Icons in the Layout

Using Page Break Before -1-

1 -2-

xxx xxx -1-

2

xxx xxx

-2-

Using Page Break After 1

xxx xxx

-1-

-1-

2

xxx xxx

-2-

Using Page Protect -1-

f_dept_id Employee f_ename

40 xxxxxxxx xxxxxxxx

-2-

41 xxxxxxxx xxxxxxxx xxxxxxxx xxxxxxxx

Controlling Print Frequency -1-

Summit Goods

Summit Goods

-2-

Summit Goods

-3-

Summit Goods

Print Object On = All Pages Base Printing On = Enclosing Object

Properties of a Field Fields define formatting attributes and logic Wizard creates a field for each column You create additional fields in Layout editor Source: Column, Parameter, or System Variable Format Mask: Standard Oracle format symbols

System Variables

Page Numbering Page 4 of 4 Example 1 Page 3 of 4 End Page 2 of 4 Page 1 of 4 Report Title

Example 2

Page 1 of 1 End Page 2 of 2 Page 1 of 2 Report Title

Valid Source Columns G_Region R_Region F_1

G_Dept

G_Emp

R_Dept R_Emp

F_REPORT

Properties of a Repeating Frame

Records per Page 1

2 -1-

-1-2-3-

-2-

Spacing Between Records Mailing Labels

1 2

Minimum Widow Records Without widow control

With widow control (Min Widow Records = 2)

Page 1

Page 1

Page 2

Page 2

Column Mode Column Mode Off Page 1 10

Page 2 20

20

Column Mode On Page 1 10 20

Page 2 30

20

30

Objectives At the end of this lesson, you should be able to do the following: Create and display a simple chart Display an existing chart Modify chart data dynamically

Creating a Chart

Image shows chart type Subtypes depend on type

Choosing the Chart Data

Positioning the Chart

Embedding an Existing Chart

1 2

D_empchart

3

disp1.ogd

Objectives At the end of this lesson, you should be able to do the following: Describe the different types of triggers Describe sample uses of triggers Write and reference common code Create a PL/SQL library

Types of Triggers in Reports Report: Five triggers Report Trigger node in Object Navigator

Data Model: Formula (column) Filter (group) Parameter validation

Layout: Format trigger on most objects

Trigger Code Parameter

boolean

Boolean: true false

Column

Number

Character Number Date

Using Report Triggers Before Parameter F Form 2

1

if readonly=yes

F After Parameter Form

3

if > 1 page Before Report F

page1

Between page2 Pages F

After Report

Using Report Triggers After Parameter Form Example: build dynamic WHERE clause FUNCTION AfterPForm RETURN BOOLEAN IS BEGIN IF :p_customer IS NULL THEN :p_where_clause := ''; ELSE :p_where_clause := 'where id >= :p_customer'; END IF; RETURN(TRUE); END;

• Query syntax: SELECT ID, NAME FROM CUSTOMER &p_where_clause ORDER BY NAME

Using Data Model Triggers Group Filter Restrict records in a group Perform PL/SQL for each record FUNCTION G_empGroupFilter RETURN BOOLEAN IS BEGIN IF :name = 'Operations' AND :salary > 3000 THEN RETURN(my_function); ELSE RETURN(TRUE); END IF; END;

• PL/SQL filters result in ALL records being fetched

Using Data Model Triggers Parameter Validation • Example: do not allow report output to be sent directly to a printer FUNCTION DESTYPEValidTrigger RETURN BOOLEAN IS BEGIN IF :DESTYPE = 'Printer' THEN RETURN(FALSE); ELSE RETURN(TRUE); END IF; END;

You cannot reassign values to parameters or columns in this trigger

Using Layout Triggers Format triggers: Exist on most layout objects Can suppress an entire layout section (master group frame): no records fetched Can suppress the display of individual records (repeating frame): all records fetched

Displaying a Text String in Place of Column Headings Dept:

Dept: 10

f_deptno

Name

1

Salary

Name Salary No employees in this department Dept: 40 No employees in this department

2

Dynamically Altering the Display of Records Employee Details

Employee Details f_name f_date f_salary f_name

08-MAR-90 2500

Biri

07-APR-91 1100

Employee 1000 Details Magee 14-MAY-90

f_date

2 1

Ngao

3

Ngao

08-MAR-90

Biri

07-APR-91

Magee

14-MAY-90

Dynamically Hiding Fields

Employee Details f_name f_date f_salary

Employee Details Ngao

08-MAR-90 2500

Biri

07-APR-91 1100

1

Employee 1000 Details Magee 14-MAY-90 Ngao 08-MAR-90 Biri

07-APR-91

Magee14-MAY-90

2

Suppressing Null Fields in a Letter Heading f_name f_address1

1

f_address2 f_address3 f_address4

2

FUNCTION label_field RETURN BOOLEAN IS BEGIN :address2 IS NULL THEN We are writing IF to advise .... RETURN(FALSE); ...... ELSE ...... RETURN(TRUE); END IF; END; Dear Sir,

Inserting Spacing Between Groups of Records f_1

f_2

f_3

1 3 FUNCTION spacing RETURN BOOLEAN IS BEGIN IF MOD(:count_column, 3) = 0 THEN RETURN(TRUE); ELSE RETURN(FALSE); END IF; END;

2 4

Writing Common Code At Report level Object Navigator, Program Units Menu: Tools—>PL/SQL Editor

In a library Object Navigator, PL/SQL Library File—>New: create new library File—>Open: add to existing library Attach library to report

Summary Trigger types Report, Data Model, Layout

Examples Building a dynamic WHERE clause Validating a parameter value Dynamically altering record display Suppressing null fields

Common code Report-level program units PL/SQL libraries

Summary Test: Trigger Return Values Character Formula column

Number Date

Report trigger... Group filter...

…abort execution True

False Validation trigger... False? Format trigger...

...do not process record ...return to parameter form ...do not format object

Objectives At the end of this lesson, you should be able to do the following: Describe the package contents Output messages at run time Execute a drill-down report Create and populate temporary tables Modify visual attributes dynamically

Contents of the SRW Package

SRW.BREAK SRW.SET_ATTR

SRW.DO_SQL SRW.RUN_REPORT

SRW.REFERENCE SRW.USER_EXIT SRW.SET_MAXROW

SRW.MESSAGE SRW.TRACE SRW.PROGRAM_ABORT

Outputting Messages Warning WHEN <exception> THEN SRW.MESSAGE(999, 'Warning: report continues');

Error WHEN <exception> THEN SRW.MESSAGE(999, 'Error: report terminated'); RAISE SRW.PROGRAM_ABORT;

Exceptions SRW.INTEGER_ERROR SRW.NULL_ARGUMENTS

Executing a Report 1

BLOGGS.LIS

xxx xxxxxxx xxx xxxxxxx

BLOGGS SMITH

SMITH.LIS

yyy yyyyyyy yyy yyyyyyy

JONES

JONES.LIS

SRW.RUN_REPORT

zzz zzzzzzz zzz zzzzzzz

2

Executing a Report Example

SRW.RUN_REPORT ('Report=EMPS DESTYPE=FILE DESNAME='||:LAST_NAME||'.LIS BATCH=YES MGRNO='||TO_CHAR(:ID));

Exceptions SRW.RUN_REPORT_FAILURE SRW.RUN_REPORT_BATCHNO

Function SRW.GETERR_RUN

Executing a Drill-Down Report 1 41

2 Details

42

Details

43

Details

42 xxxxxxxxx SRW.RUN_REPORT xxxxxxxxx xxxxxxxxx xxxxxxxxx

Restricting Data Before Report trigger SRW.SET_MAXROW('Q_EMP',3);

Q_EMP

ID LAST_NAME

G_EMP

1 2 3

ID LAST_NAME

BLOGGS SMITH JONES

Initializing Fields Layout editor Page:

F_NEWPAGE

Output: logical page no. = 2 Page: 5

1 FUNCTION F_LOGICAL_PAGEFormatTrigger RETURN BOOLEAN IS my_page number; BEGIN SRW.GET_PAGE_NUM(my_page); SRW.SET_FIELD_NUM(0,my_page+3); RETURN(TRUE); END;

2

Performing DDL Statements Example SRW.DO_SQL('CREATE TABLE SRW_LOG (RPT_NAME VARCHAR2(40), REC_NUM NUMBER, MSG_TEXT VARCHAR2(80))'); SRW.DO_SQL('INSERT INTO SRW_LOG (RPT_NAME, REC_NUM, MSG_TEXT) VALUES (''PAY_REPORT'', TO_CHAR(:ID), :LAST_NAME||''PAY REPORT RUN'')');

Exception SRW.DO_SQL_FAILURE

Setting Format Attributes borderwidth Tuesday, 01 Sep

fill pattern format mask printer tray control

Car Policy

define a bookmark

Setting Format Attributes Visual attributes SRW.SET_FILL_PATTERN('solid'); SRW.SET_BORDER_WIDTH(250); SRW.SET_FORMAT_MASK('Day, Month yyyy');

Printer tray control SRW.SET_PRINTRER_TRAY('letterhead');

Define a bookmark SRW.SET_BOOKMARK('Car Policy');