Oracle Reports Tips

1
Oracle Reports Tips

• Created by College Information Systems
• Presented by Tara Welby
• Wednesday, April 14, 2004

2
Agenda

• Create a Wizard Report
• Review components
• Customize a report
• Create a report from scratch
• Tips
• Developer Session

3
How do I create a report with the Report Wizard?

• Style
• Matrix
• spreadsheet-like, very touchy!
• Use Discoverer instead
• Query (SQL or Build)
• Fields
• Totals
• Labels
• Template
• Finish
• Overwrites customizations!!

Use the Report Wizard to start the report, then
customize.
4
Sample Report Report Wizard
Demo
5
Ive got my data, but want to tweak the output.
Use the Live Previewer.

• WYSIWYG changes
• Good for
• Fonts
• Format mask
• Color
• Align
• Resize
• Conditional formatting (Right Click)
• Not good for deleting/adding
• Re-executes query

Use the Live Previewer to make minor formatting
changes.
6
Sample Report - Live Previewer
Demo
7
What makes up the layout? Check out the Layout
Model.

• Repeating Frames
• Frames
• Fields
• Boilerplate
• Section
• Header
• Main
• Trailer
• Edit Margin

8
Sample Report Layout Model
Demo
9
You can see all the objects in a report via the
Object Navigator.

• Find
• Property Palette (F4/Double-Click)
• Create/Delete
• Rename
• Move
• Copy

The Object Navigator is a useful report-wide view.
10
Sample Report Object Navigator
Demo
11
Wheres my query? The Data Model.

• Queries
• Columns
• Groups
• Parameters
• Data Links
• Parameters
• Master-Detail
• Can auto-join based on PK
• Computed Field

You can use the Data Model to enhance queries
build in the Wizard.
12
Sample Report Data Model
Demo
13
Create Report from Scratch
Creating a report from scratch provides a solid
foundation for modifying Wizard and SCT reports.
Demo
14
Tips

• Write query in SQLPlus/Toad first
• Tune
• Ensure getting correct data
• Write as much in SQL as possible (vs. tool)
• Single queries are more efficient
• More control
• Use Wizard to perform layout
• Save often and keep several versions
• Last step customize layout
• Use Obj Navigator to Select Frames

15
SQL Tips

• You dont have to SELECT a COLUMN to use it in
  the WHERE
• IN vs. ORs
• OR ACCT_CODE '50536' OR ACCT_CODE'50605
• ACCT_CODE IN ('50536,'50605)
• Single, non-smart, Quotes
• Cutting and pasting from Word may cause issues
• Oracle SQL for Reporting (Version 8i )
• Group by Rollup, Cube
• Rank
• Statistical Functions
• Toad/Reports may not handle some obscure SQL
• Handles GROUP BY ROLLUP (x,y,z)
• Does not handle GROUP BY (X, Y) ROLLUP Z

16
Advanced SQL Group by Rollup
17
Advanced SQL Group by Cube
18
Advanced SQL Hierarchies / Connect ByGood for
org chartsCan be slow - use with caution

• SYS_CONNECT_BY_PATH
• Oracle Pseudo Column
• / - Divider of Choice
• START WITH
• Level to Start With
• Ex Student Menu
• CONNECT BY PRIOR
• Child Parent
• Ex SubMenu Menu
• Ex EMP MGR

19
What if I want to make all my reports look
similar? Create a Template.

• Template Editor
• Create template similar to delivered
• Title
• Bridgewater State College Admissions Dept
• Page Numbers
• Date

Demo
20
Resources

• Help Manuals Quick Tour Report Builder
• Help Queue Cards
• Step by step instructions for common reports
• Oracle Documentation
• http//otn.oracle.com/documentation/index.html
• Report Builder Reference
• SQL Reference
• Oracle Metalink
• http//metalink.oracle.com
• Oracle Technology Network