Oracle Reports Material Part 1

ORACLE REPORTS MATERIAL

Report: - The presentation of the information in a required format is called report.

And report is for read-only purpose.

ü  Report6i is a GUI based tool, part of developer and one of the tools of D2k.

ü  Report6i is a window based tool, to design the reports for both OLTP and OLAP systems/environments.

ü  Report6i tool is to develop the report for both Graphical mode as well as Char mode.

ü  Reports6i tool is to design the reports from both client-server environment as well as for web architecture. i.e HTML format and XML format.

Reports Builder is the report-building component of Oracle Reports Developer,

a powerful enterprise reporting tool that used to rapidly develop and deploy sophisticated Web and paper reports against any data source (including an Oracle database, JDBC, XML, text files, and Oracle OLAP).

ü  One of the major new features of Oracle Reports is the incorporation of Java Server (JSPs).

Report6i mainly consists of 3 tools.

1)    Reports 6i Builder

2)     Reports 6i Compiler

3)    Reports 6i Runtime

Ø  Report 6i Builder:-  

ü  It is used to build the reports i.e. the basic definition to the layout designing and generate the .RDF file.

Ø  Report 6i Compiler:-

ü  It is used to compile the .RDF file and generate the executable file i.e.  .REF.

Ø  Report 6i Runtime:-  

ü  It is show the output of the report. Run time modes are in 2 types.

1)    Live previewer:

      It is an on-line editor allows you to format the report just before the printing.

      like alignment tools, color pallets, fonts, shapes, graphics etc.

2)    Run-mode or run-time previewer:

       It won’t allow you to edit the report.

About the Report Wizard:

Ø  The Report Wizard helps you to quickly and easily define a single-query report for

both Web and paper layouts.

Ø  Reports Builder uses what you specify on each page of the wizard to create a data model and layout for your report.

Report styles/layouts/Templates

1)    Tabular : - It allows  report in table format in the form of rows and cols.

(Displays more than 1 record per page).

2)    Form like : - (Displays one record per page).

      For ex :- Drafts, Cheques, Pay slips etc.

3)    Mailing Lables : - used to print address in lable format.

 (Display more than one record per page).

4)    Form Letter : - It allows to generate the documents by substituting the values of the database and used to prepare letters. Similar to Mail-Merge in MS-Word.

(Displays one record per page).

5)    Group Left :- used to arrange grouped results.(group function).

6)    Group Above : -    -do-  (Both are displays one set of data per page). 

      Note : - Above two are used for master-detail reports and also break reports.

7)    Matrix : - used to display report in rows and cols.

      Note : - Tabular is 2-D report where as Matrix is 3-D report.

8)    Matrix with Group :- same as above but support with grouping columns.

     Imp Point : - Break report (group above and group below) : - In this Table data is divided into multiple groups based on one or more columns values .

File Extensions in D6i:

v  FMB/FMX/ERR----Forms

v  MMB/MMX------Menus

v  PLL/PLX--------PL/SQL Libraries

v  OLB--------Object Libraries

v  OGD/OGR-------Object Graphics files (design and runtime file)

v  RDF/REP---------Oracle Reports

v  SQL-----Sql code

v  ICO-----Iconic files

v  TDF-------Report Template Definition File

Report Builder Components/Screens/Intefaces

1)    Object Navigator (F3)

ü  The Object Navigator provides a hierarchical display of all objects in a report including attached libraries and program units.

ü  It is also used to create and navigate between the objects in a report.

ü  Using the Object Navigator, you can:

■ create reports, parameters, PL/SQL program units, and    

     attached libraries.

■ Display Report Editor Views.

■ expand and collapse nodes.

■ search for objects.

■ view objects by hierarchy or type.

■ display properties.

■ Drag and drop PL/SQL program units.

2)    Layout Model (F2)

ü  The Layout Model editor lets you design the style, color, size, and arrangement of visual objects in the application.

ü  These layouts can include graphical objects and bitmapped images when running in a GUI environment.

                  Imp Note:- One Report can contain one or more layouts.

3)    Property Palette (F4)

ü  All objects in a module, including the module itself, have properties that you can see and modify in the property palette. Features include:

• Copy and reuse properties from another object

• Find field and icons, similar to Object Navigator.

4)    PL/SQL Editor (F11)

ü  The PL/SQL Editor lets you create and compile program units such as functions, procedures and packages within the current report.

The Report Editor

Ø  The Report editor contains two views to handle the data objects and layout objects separately:

ü  Data: Data structure and values to be displayed.

ü  Layout: Formatting information about how the values appear in the output.

The Report Module consists of many objects that fall into the following four categories:

1.    Report level

2.    Data Model

3.    Layout Model

4.    Parameter Form

View /Screens:

v  Data Model: - Displays a structural representation of the data in a report. The objects do not appear in the report output, but the structure determines the layout style, and the data objects provide the values that appear in the layout objects.

v  Layout Model:-Displays the layout objects in a report and allows you to make many modifications to any layout object. All layout objects have properties that you can modify in a property palette. The hierarchy of the layout objects is determined by the Data Model.

View/Screens:

v  Live Previewer - Displays report output as it appears when printed and allows you to make most commonly required changes without having to open the Layout Model.

OR

v  A view of report output that allows live editing of text and layout attributes.

The Live Previewer is a WYSIWYG editor.

                                     Modifying a Report in the Live Previewer:

ü  The most commonly required changes that you need to make to reports, after creating the initial definition with the wizard:

§  Aligning columns

§  Setting format masks

§  Manipulating objects

§  Editing text

§  Modifying visual attributes

§  Highlighting data using conditional formatting

§  Inserting page numbering

§  Inserting current date and time

v  Parameter Form - Displays the layout of the parameter form at run time, allows

User-input of parameter values.

Report-Level Objects

Ø  The report objects define the measurements, dimensions, triggers, and PL/SQL

program units of a report. The report object itself consists of the following:

Objects:

ü  Properties:- Define page dimensions and Previewer settings.

ü  Triggers:-Allow PL/SQL to be executed at different stages of the report execution.

ü  PL/SQL Program Units:- Contain functions and procedures that can be called from Report-level objects in the same report.

Data Model Objects

ü  The Data Model Displays a structural representation of the data in a report.

ü  Data Model objects appear in the Data Model editor and the Object Navigator.

ü  Data Model objects can be of the following types:

Objects:

Ø  Query :- Select the data for your report.

You can create a report with one or more queries. The Report Wizard       

creates a single-query report. Use the query tool or the Data Wizard to

create additional queries.

Ø  Group :- Organize the data to form the required hierarchical structure.

           Each group is owned by a query. By default, Reports creates one group

for each query.

Ø  Columns :- Contain individual data values; database columns exist by default  and contain data from the database columns . you can also create Formula,  Summary, and Placeholder column types.

Columns are owned by the group to which they belong. By default, the group  

contains one column for each select expression in the query.

Ø   Data Link: - Creates a link between the columns in the queries.

Links join a parent group to a child group. You can create links to form a

relationship between groups from different queries. Links are never created by

default.

Ø  Parameter: - You can create parameters that allow users to enter value restrictions at run time. Reports also provide a number of system parameters by default. You can also create User Parameters.

o    Parameters do not appear in the editor. You create and modify parameters in the Object Navigator only.

Data Model view tool palette (9i)

v  Summary Column: - It is used to evaluate the summary results within the report  using Max, Min, Avg, Count, Sum.(Default Name----- CS_1)

A summary can be defined at 3 different levels.

1)    Report-level

2)    Group-level

3)    Page-level

v  Formula Column: -

ü  Uses a formula you have written in PL/SQL to calculate a value from one or more other columns. (D.Name----- CF_1).

ü  Every formula column itself is a function.(single-row function) mainly used to calculate the values at the Report level not at the Database level.

§  Performs a user-defined computation

§  Executes a PL/SQL function

§  Must return a value

§  Data type Can be Character, Number, or Date (any).

§  Returned value must match datatype.

v  Cross Product: - Creates a matrix (cross-product) group.

v  Placeholder: -

ü  It acts as a Global variable in the datamodel. If you want to return morethan one value then we can use placeholder column in the formula col.

Has its value set from another object at run time. (D.Name----- CP_1)

ü  It is almost same as Formula-column only. i.e used for report level calculations.

ü  A Placeholder column is acts as a global variable, hold the report result.

ü  Placeholder column can reduce the code-redundancy.

Note:- The code for placeholder column is mainly defined on Group.

v  Data Link : -

ü  Creates a link between the columns in the queries. (Between one query’s column/group and another query’s column/group).

ü  A data link defines a parent/child relationship between a group and a query by way of a primary key and foreign key. The parent/child relationship causes the child query to be executed for each instance (record) of the parent group.

v  SQL Query : -

ü  Displays the SQL Query Statement dialog box (Data Source Difinition) where you can enter a SQL query SELECT statement or use Query Builder to create a query.

v  Ref Cursor: -

ü  Displays the PL/SQL Editor where you can type a ref cursor query.

v  XML Query : -

ü  Displays the Define XML Query dialog box, where you can specify the XML data definition and data source.

v  JDBC Query : -

ü  Displays the JDBC Query dialog box, where you can define the

SQL or stored procedure to define the data for the query.

v  Text Query: -

ü  Displays the Text Query dialog box, where you can specify a text

data definition and data source.

v  Express Server Query: -

ü  Displays the Express Server Query dialog box, where

you can specify an Oracle Express data definition and data source.

Note: - Non-Database columns are Formula, Place holder, Summary columns.

 List of data sources (which have query word) that have been installed. (shown in screens 3 and 4).