Oracle 9i Reports Developer Quick Notes

 

ORACLE REPORTS DEVELOPER 9i:

BUILD INTERNET REPORTS                        

Prepared by: Eng. Héba M. Noweir     

 

Oracle Reports Developer 9i: Build Internet Reports    •

CONTENTS

TOPIC TABULAR – WIZARD ………………………………………… GROUP LEFT – WIZARD ……………………………………. GROUP ABOVE – WIZARD …………………………………. TABULAR – MANUAL ………………………………………... MASTER-DETAIL – MANUAL ……………………………… DATALINK – MANUAL ……………………………………….. FORM – WIZARD …………………………………………….. FORM LETTER – WIZARD ………………………………….. MAILING LABEL – WIZARD ………………………………… MAILING LABEL – WIZARD ………………………………… MATRIX – MANUAL ………………………………………….. REPORT WITH IMAGE – MANUAL ………………………... GRAPHS ………………………………………………………. PARAMETERS ………………………………………………... (A) BIND PARAMETERS …………... (B) LEXICAL PARAMETERS ……… SRW, CONDITIONAL FORMATTING, SUMMARY ITEMS BOILER-PLATE TEXT ……………………………………….. TEMPLATES ………………………………………………….. GENERATING XML REPORT ………………………………. OPENING A REPORT FROM A FORM ……………………

Prepared by: Eng. Héba M. Noweir     

PAGE 1 1 1 2 3 3 4 4 4 4 5 5 6 6 6 7 7 8 8 8 9

Oracle Reports Developer 9i: Build Internet Reports 

•

(1) (2) (3) (4)

(5) (6)

Open Reports Builder I. TABULAR - WIZARD Designing: Use the Reports Wizard Create Web Layout only Tabular SQL Query: SELECT EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME NAME, SALARY, JOB_ID FROM EMPLOYEES WHERE DEPARTMENT_ID= 50 Displayed Fields: [Choose from Available Fields] Totals: Ex. Count (Employee_id), Sum (Salary),.. Notes: (1) Save Reports as .rdf file (2) Static Frame (Fixed): Contains all the Records that are displayed only one time in the Report like Titles. (3) Repeating Frame: Contains all the Records that will be repeated in the Report [Called from tables]

II. GROUP LEFT – WIZARD (1) Repeat Steps 1 & 2 Æ Group Left (2) SQL Query: SELECT DEPARTMENT_ID, FIRST_NAME || ' ' || LAST_NAME NAME, JOB_ID, SALARY FROM EMPLOYEES (3) Group Fields: Department_id (4) Displayed Fields: [Choose from Available Fields]

III. GROUP ABOVE – WIZARD (1) Repeat Steps 1 & 2 Æ Group Left (2) SQL Query: SELECT DEPARTMENT_NAME,JOB_ID,SALARY FROM DEPARTMENTS D JOIN EMPLOYEES E ON (E.DEPARTMENT_ID=D.DEPARTMENT_ID) (3) Group Fields: Level 1 Æ Department_name Level 2 Æ Job_id

Prepared by: Eng. Héba M. Noweir     

1 

Oracle Reports Developer 9i: Build Internet Reports 

¾

TASK: Create a Report displaying Regions Æ Countries Æ Locations Æ Departments Æ Employees

Group Above SQL Query: SELECT REGION_NAME, COUNTRY_NAME,CITY, DEPARTMENT_NAME, FIRST_NAME || ' ' || LAST_NAME NAME FROM REGIONS R JOIN COUNTRIES C ON (R.REGION_ID=C.REGION_ID) JOIN LOCATIONS L ON (C.COUNTRY_ID=L.COUNTRY_ID) JOIN DEPARTMENTS D ON (L. LOCATION_ID=D.LOCATION_ID) JOIN EMPLOYEES E ON (D.DEPARTMENT_ID=E.DEPARTMENT_ID)

IV. TABULAR – MANUAL (1) Build a new Report Manually (2) You will enter the Data Model View: Create SQL Query (3) SQL Query: SELECT EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME NAME, SALARY, JOB_ID FROM EMPLOYEES (4) Go to Paper Layout view a. Create a Static Frame b. Double Click on the Static Frame you’ve just created – opens Property Inspector Vertical Elasticity: Expand c. Put Titles [Emp_id, Name, Job, Salary] in Text items on the Static Frame d. Examine the Toolbar: Flex Off, Confine Off e. Create a Repeating Frame inside the Static Frame f. Double Click on the Repeating Frame you’ve just created – opens Property Inspector Vertical Elasticity: Expand Source: Select the Group g. Create a Field in the Repeating Frame under each title [This is where the columns will appear] – Property Inspector Source: [Select Columns]

Prepared by: Eng. Héba M. Noweir     

2 

Oracle Reports Developer 9i: Build Internet Reports 

(1) (2)

(3) (4)

V. MASTER-DETAIL – MANUAL Create a Report manually SQL Query: SELECT DEPARTMENT_NAME, FIRST_NAME || ' ' || LAST_NAME NAME,JOB_ID, SALARY FROM EMPLOYEES E JOIN DEPARTMENTS D ON (E.DEPARTMENT_ID=D.DEPARTMENT_ID) In Data Model View: Drag & Drop [Name, Job_id, Salary] into another Group In Paper Layout View: Create the following schema

(5) Choose Sources: 1st Repeating Frame: Department_Group 2nd Repeating Frame: Employees_Group (6) Create Fields and choose sources

VI. DATALINK – MANUAL (1) Create a Report manually (2) SQL Query(1): SELECT DEPARTMENT_ID, DEPARTMENT_NAME FROM DEPARTMENTS (3) SQL Query(2): SELECT DEPARTMENT_ID, FIRST_NAME || ' ' || LAST_NAME NAME,JOB_ID, SALARY, EMPLOYEE_ID FROM EMPLOYEES (4) In Data Model View: Create a DataLink between Department_id of Query(1) & Department_id of Query(2) (5) In Paper Layout View: Create the following schema (6) Choose Sources: 1st Repeating Frame: Department_Group 2nd Repeating Frame: Employees_Group (7) Create Fields and choose sources Note: The difference between Data-Link Master-detail is that DataLink acts like an outer join.

Prepared by: Eng. Héba M. Noweir     

3 

Oracle Reports Developer 9i: Build Internet Reports 

4  VII. FORM – WIZARD (1) Create a Report using Wizard (2) Create Paper Layout only Æ Form (3) SQL Query: SELECT EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME, JOB_ID, SALARY FROM EMPLOYEES

VIII. FORM LETTER – WIZARD (1) Create a Report using Wizard (2) Create Paper Layout only Æ Form Letter (3) SQL Query: SELECT FIRST_NAME, LAST_NAME,JOB_ID, DEPARTMENT_ID, SALARY, SALARY * 1.5 "NEW SALARY" FROM EMPLOYEES (4) Form Letter Text: Dear Mr. / Ms. & & , Thanks to your efforts in department & as &<JOB_ID> ; your salary will be raised from &<SALARY> to & (5) You can also insert an image

IX. MAILING LABEL – WIZARD (1) Create a Report using Wizard (2) Create Paper Layout only Æ Mailing Label (3) SQL Query: SELECT FIRST_NAME || ' ' || LAST_NAME NAME,JOB_ID, DEPARTMENT_ID FROM EMPLOYEES (4) Mailing Label Text: Name: & Department: & Job: &<JOB_ID> (5) Property Inspector: Print Direction: Across / Down [For Repeating Frames]

X. MATRIX – WIZARD (1) Create a Report using Wizard (2) Create Paper Web Layout only Æ Matrix (3) SQL Query: SELECT DEPARTMENT_ID, JOB_ID, COUNT(*) FROM EMPLOYEES GROUP BY DEPARTMENT_ID, JOB_ID

Prepared by: Eng. Héba M. Noweir     

Oracle Reports Developer 9i: Build Internet Reports  (4) Matrix Row Fields: Job_id (5) Matrix Column Fields: Department_id (6) Matrix Cell Fields: Count XI. MATRIX – MANUAL (1) After creating the Matrix Report using a Wizard; (2) Go to Data Model: Put job_id in a group with count, drag count from this group. Put a Cross-Product on Department_id, Job_id group. (3) Go to Paper Layout View: Release (Delete) all Static Frames and titles and leave only the 3 Repeating Frames as shown:

ROW  Field

  COLUMN  Field

 

CELL

 

Field

(4) This is: a) b) c) d) e)

Create a Repeating Frame (Across) Æ Column Create a Repeating Frame (Down) Æ Row Select the 2 Frames and Layout-Matrix Create a Repeating Frame at the Intersection Horizontal & Vertical Elasticity: Variable

(5) Save this Matrix Layout as a template to be used when needed

XII. REPORT WITH IMAGE – MANUAL (1) Create a Report manually (2) SQL Query: SELECT EMPLOYEE_ID, LAST_NAME, IMAGE FROM EMP1 (3) In Paper Layout View: a. Create a Repeating Frame and Choose its source:Emp b. Print Direction: Down/Across c. Create 3 Fields and choose their sources d. In the image fields Æ Property Inspector Æ File Format: Image

Prepared by: Eng. Héba M. Noweir     

5 

Oracle Reports Developer 9i: Build Internet Reports 

6 

XIII. GRAPHS (1) Create a Report using Wizard (2) SQL Query: SELECT JOB_ID, COUNT (*) FROM EMPLOYEES GROUP BY JOB_ID (3) From Paper Layout, Right Click Æ Graph Wizard (4) Choose the Graph Type (5) X-Axis Categories: Job_id (6) Y-Axis Categories: Count

XIV.

PARAMETERS (A) BIND PARAMETERS (Where Clause) Example (1): (1) Create a Report using Wizard (2) SQL Query: SELECT EMPLOYEE_ID, LAST_NAME,SALARY, DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID=:P_DEPT Example (2): (1) Repeat Example (1) (2) F5: Object Navigator Æ Data Model Æ User Parameters Æ P_DEPT Æ Property Inspector Data Type: Number List of Values (Select Statement): SELECT DEPARTMENT_ID, DEPARTMENT_NAME FROM DEPARTMENTS Example (3): (1) Repeat Example (1) (2) SQL Query: SELECT EMPLOYEE_ID, LAST_NAME,SALARY, DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID BETWEEN :P_PAR1 AND :P_PAR2 (3) F5: Object Navigator Æ Data Model Æ User Parameters Æ P_PAR2 Æ Property Inspector List of Values (Select Statement): SELECT LAST_NAME FROM EMPLOYEES ORDER BY EMPLOYEE_ID DESC (4) F5: Object Navigator Æ Data Model Æ User Parameters

Prepared by: Eng. Héba M. Noweir     

Oracle Reports Developer 9i: Build Internet Reports  Æ P_PAR1 Æ Property Inspector List of Values (Select Statement): SELECT LAST_NAME FROM EMPLOYEES (B) LEXICAL PARAMETERS Since we could not put a column name or table name in Bind Parameters, we need Lexical Parameters (1) Create a Report manually (2) SQL Query: SELECT EMPLOYEE_ID, LAST_NAME, SALARY, DEPARTMENT_ID FROM EMPLOYEES (3) F5: Object Navigator Æ Data Model Æ User Parameters Æ Create Parameter P_1 Initial Value: WHERE DEPARTMENT_ID=50 (4) Return to the SQL Query: Append [From Employees &P_1] Notes: a. If you opened a trigger on any parameter by mistake, delete it b. Try Paper Parameter Form c. NVL(:P_DEPT, DEPARTMENT_ID)

XV.

SRW, CONDITIONAL FORMATTING, SUMMARY ITEMS (1) Create a Report using Wizard (2) SQL Query: SELECT EMPLOYEE_ID, LAST_NAME, SALARY FROM EMPLOYEES (3) Conditional Formatting on Salary Column: Right Click on Column Æ Conditional Formatting Æ New IF VALUE OF SALARY IS GREATER THAN 10 000 Æ … Property Inspector Æ Format Mask (1) Go to Query (2) Create a Summary item outside the Query Block [Sum_Sal] Function: Sum Source: Salary (3) Paper Layout Æ Create a Field and Choose Source [Sum_Sal] (4) Create a new Summary item outside the Query Block [Count_Emp] Function: Count Source: Employee_id (5) Paper Layout Æ Create a Field and Choose Source [Count_Emp] SRW Built-In Package for Reports (1) Paper Layout Æ Repeating Frame [Employees] Æ Property Inspector Format Trigger: function F_EMPLOYEE_IDFormatTrigger return boolean is begin IF MOD(:SERIAL.2)=0 THEN SRW.SET-BACKGROUND-FILL-COLOR('BLUE');

Prepared by: Eng. Héba M. Noweir     

7 

Oracle Reports Developer 9i: Build Internet Reports  return (TRUE); end; Note: Where SERIAL is a Summary Item Type:Count

XVI.

BOILER-PLATE TEXT (1) Create Data-Link (2) Create a Summary Item (Departments) inside the 1st Query Source: Employee_id Function: Count Reset: Department_id (3) Go to Paper Layout View: a) Enlarge the main Static Frame. b) Put a Text Item on the Repeating Frame (Departments), “No Employees in this Department” c) Go to Property Inspector: Format Trigger “IF :COUNT_EMP=0 THEN RETURN (TRUE); ELSE RETURN (FALSE); END IF;”

Note: RETURN (TRUE) on item = ‫إﻇﻬﺮهﺎ‬ RETURN (FALSE) on item = ‫إﺧﻔﻴﻬﺎ‬

XVII.

TEMPLATES (1) From Object Navigator (F5) Æ Templates (2) Create a Report based on Template (Choose Template File) (3) From Toolbar: Edit Margin (4) Insert Date & Time, etc…

XVIII.

GENERATING XML REPORT (1) Install XML Publisher (2) Open Reports Builder and Create a Report using Wizard (3) SQL Query: SELECT LAST_NAME, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES (4) From Menu Bar Select Æ File: Generate to File XML (5) Open MS Word: Data: Load XML Data Insert: Table Using Wizard Save File Æ Template.rtf (6) Preview: PDF

Prepared by: Eng. Héba M. Noweir     

8 

Oracle Reports Developer 9i: Build Internet Reports  Note: On the Repeating Frame Æ F: Start & E: End

XIX.

OPENING A REPORT FROM A FORM (1) Put Report.fmb into the Default Folder MyForms (2) Examine the When-Button-Pressed Trigger on the Push-Button ------------------------------------RUN REPORT---------------------------------------v_state := run_report_object('Report_Name',pl_id); No need for the Parameter Part unless you are going to use Parameters (3) From Forms Builder Object Navigator: on Report.fmb Æ Create Report (4) F4 on the Report you’ve just created Filename: Take the full path of the Report.rdf Report Server: Write the Report Server name Ex. “MyName” (5) Run the Reports Server RUN: rwserver server=MyName (6) Make Sure that the repserver name in the Trigger is the same: VC_URL:='/reports/rwservlet/getjobid'||substr(v_state,LENGTH('MyNam e')+2)||'?server=MyName'; Note: Repserver must be run for Reports as OC4J for Forms

Prepared by: Eng. Héba M. Noweir     

9 

System Variables System.record_status

System.block_status

System.form_status

Get_Block_Property System.Cursor_Block System.Trigger_Block Find_Built Ins

Get_Record_Property System.Cursor_Record System.Trigger_Record Name_In

Set_Record_Property System.Cursor_Value System.Trigger_Item

•

How to open Forms from Browser?

http://127.0.0.1:8889/forms/frmservlet? Form = form_name.fmx Userid = hr/hr@orcl Formsweb.cfg [logon] Form = sign_in_formname.fmx Userid: hr/hr @ orcl Colors = blaf In URL after ?config = logon Administrator username:ias_admin Instance Name: OAS Ias_admin Password: OAS10g In Reports: Right Click on the Shortcut Target: hr/hr @ orcl Æ always connected

Prepared by: Eng. Héba M. Noweir