Oracle Reports Material Part 2

Layout Objects

Ø  Frame:- Contains other objects and prints only once.

Ø  Repeating frame:-

ü  Contains other objects and prints once for each record of the associated group.

Ø  Field :-

ü  Contains data and other variable values and their formats.

ü  Fields are placeholders for parameters, columns, and such values as the page

number, current date, etc. If a parameter or column does not have an associated field, its values will not appear in the report output.

Ø  Boilerplate :-

ü  Contains text or graphics that may appear anywhere in the report. A boilerplate object is any text, lines, or graphics that appear in a report every time it is run.

Paper Layout view tool/layout editor palette

v  Graph : -

ü  Displays the Graph Wizard so that you can to define a graph that will be inserted into your layout.

v  Field : -Creates a field object.

v  Anchor : -Creates an anchor between two objects in your layout.

v  File Link: -

ü  Creates a link file object that you can use to link an external file to

your report.

v  Report Block: -

ü  Displays the Report Block wizard so that you can add a new report block to your layout.

v  Query

ü  A SQL SELECT statement that specifies the data you wish to retrieve from one or more tables or views of a database.

v  RDF file

ü  A file that contains a single report definition in binary format. .RDF files are used to both run and edit reports.

v  Record

ü  One row fetched by a SQL SELECT statement.

v  REP file

ü  A file that contains a single report definition in binary format. .REP files are used solely to run reports; you cannot edit a .REP file.

v  Template

ü  A skeleton definition containing common style and standards, and may include

graphics.

ü  A template provides a standard format to enable quick and easy development of professional standard look-and-feel reports.

Parameter Form Objects

Ø  The Parameter Form objects define the appearance of the run-time parameter form.

You create and modify run-time parameter form objects.

Objects:

Ø  Field : -Contains parameter values

Ø  Boilerplate: -

ü  Contains constant text or graphics that appear on the run-time parameter form.

Note: The Parameter Form controls the layout of the run-time parameter form. The objects are similar to layout objects. The source of a parameter field comes from a parameter that is a Data Model object. Parameters appear in the Object Navigator, not in the Data Model view.

v  About parameters  

v  A parameter is a variable whose value can be set at runtime . Parameters are especially useful for modifying SELECT statements and setting PL/SQL variables

at runtime.

System parameters: (CREATED BY REPORT BUILDER)

COPIES                      

CURRENCY                       PRINTJOB

DECIMAL                          THOUSANDS

DESFORMAT               ORIENTATION

DESNAME

DESTYPE

User parameters

Ø  A user parameter is a Data Model object that you create to hold a value that users can change at run time.

Ø  You can create your own parameters and use them to change the SELECT statement of your query at run time.

           user parameters are two types.

1)    Bind parameter/reference

2)    Lexical parameter/reference

v  You can reference a parameter anywhere in a query. For example:

• Restrict values in the WHERE clause of the SELECT statement

• Substitute any part of the SELECT statement, including the entire statement

• Substitute a single column or expression in the SELECT list.

Difference between system and user parameter: -

v  While you can delete or rename a user parameter, where as

You cannot delete or rename a system parameter.

You can create a user parameter in the following ways:

■ create a parameter in the Object Navigator

■ use a bind parameter reference in a query, which causes Reports Builder to

              Automatically create the parameter the first time it is referenced.

How to Create a User Parameter in the Object Navigator

1.    In the Obj. Nav choose the User Parameter node, and then choose the Create tool.

Note: If this is the first parameter, you can create it by double-clicking the User Parameter node.

2.    Rename the parameter and open the property palette.

3.    In Property palette, Under Parameter node, Verify the datatype and width.

4.    If required, enter an initial value and provide the list of values to the parameter and write the validation code in property palette.

Referencing Parameters in a Report Query

There are two ways to reference parameters in a query:

• Use a bind reference

• Use a lexical reference

What Is a Bind Reference?

ü  Bind references (or bind variables) are used to replace a single value or expression in SQL or PL/SQL, such as a character string, number, or date.

ü  To create a bind reference in a query, prefix the parameter or column name with colon (:).

ü  Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries.

Restrictions:-

ü  Bind references must not be the same name as any reserved SQL keywords.

ü  Bind references may not be referenced in FROM clauses or in place of reserved words or clauses.

ü  Replace any part of a FROM clause.

ü  Replace a column name in the SELECT clause, although you can reference a

value, such as the contents of a parameter:

ü  SELECT LAST_NAME, SALARY * :P_RATE FROM S_EMP.

Example 1: SELECT clause

v  SELECT CUSTID, NVL(COMMPLAN, :DFLTCOMM) COMMPLAN FROM ORD;

Example 2: WHERE clause

v  SELECT ORDID, TOTALFROM ORD WHERE CUSTID = :CUST;

Example 3: GROUP BY clause

v  SELECT NVL(COMMPLAN, :DFLTCOMM) COMMPLAN, SUM(TOTAL) TOTAL

                   FROM ORD GROUP BY NVL(COMMPLAN, :DFLTCOMM);

Example 4: HAVING clause

v  SELECT CUSTID, SUM(TOTAL) TOTAL FROM ORD

                   GROUP BY CUSTID HAVING SUM(TOTAL) > :MINTOTAL;

Example 5: ORDER BY clause

v  SELECT ORDID, SHIPDATE, ORDERDATE, TOTAL FROM ORD

                   ORDER BY DECODE(:SORT, 1, SHIPDATE, 2, ORDERDATE);

Example 6: CONNECT BY and START WITH clauses

v  References in CONNECT BY and START WITH clauses are used in the same way as they are in the WHERE and HAVING clauses.

Example 7: PL/SQL

v  procedure double is

begin

     :my_param := :my_param*2;

 end;

ü  The value of myparam is multiplied by two and assigned to myparam.

Lexical Reference?

ü  A lexical reference is a text string and can replace any clause of a SELECT statement, such as column names, the FROM clause, the WHERE clause, or the ORDER BY clause (or) even to replace the entire statement.

ü  To create a lexical reference in a query, prefix the parameter or column name with an ampersand (&).

ü  If the parameter object does not exist, Report Builder automatically creates it for you and displays a message. In this case, the parameter default datatype is

CHARACTER, not NUMBER. (This stmt is applicable for both Bind and Lexical).

ü  Ensure that the number of values and datatypes match at run time.

About lexical references

ü  Lexical references are placeholders for text that you embed in a SELECT stmt.

ü  You can use these to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.

ü  Use a lexical reference when you want the parameter to substitute multiple

values at runtime.

Restrictions

1)    You cannot make lexical references in a PL/SQL statement.

2)    If a column or parameter is used as a lexical reference in a query, its Datatype

      must be Character.

Example 1: SELECT clause

v  SELECT &P_ENAME NAME, &P_EMPNO ENO, &P_JOB ROLE FROM EMP;

Example 2: FROM clause

v  SELECT ORDID, TOTAL FROM &ATABLE;

Example 3: WHERE clause

v  SELECT ORDID, TOTAL FROM ORD WHERE ‘&CUST’;

Example 4: GROUP BY clause

v  SELECT NVL(COMMPLAN, DFLTCOMM) CPLAN, SUM(TOTAL) TOTAL

                FROM ORD GROUP BY &NEWCOMM;

Example 5: HAVING clause

v  SELECT CUSTID, SUM(TOTAL) TOTAL

                   FROM ORD GROUP BY CUSTID HAVING &MINTOTAL;

Example 6: ORDER BY clause

v  SELECT ORDID, SHIPDATE, ORDERDATE, TOTAL FROM ORD ORDER BY &SORT;

Example 7: CONNECT BY and START WITH clauses

Example 8: PL/SQL and SQL

v  SELECT &BREAK_COL C1, MAX(SAL) FROM EMP GROUP BY &BREAK_COL;

Example 9: WHERE AND ORDER BY CLAUSES

To specify both above clauses at run time (as two separate parameters):

v  SELECT NAME, SALES_REP_ID

                   FROM S_CUSTOMER

                   &P_WHERE_CLAUSE

                   &P_ORD_CLAUSES

        NOTE: Create lexical parameters explicitly in the Object Navigator (Report Builder creates bind parameters if necessary).  Always use column aliases when substituting column names with lexical references.

Difference Between Bind and Lexical Parameters are,

1)    Bind parameter can hold only one parameter value at a time where as Lexical parameter can hold more than one.

2)    In Bind parameter, we can use : (colon)  as prefix where as In Lexical parameter, & (ampersand) as prefix.

3)    Bind parameter accepts condition clause where as Lexical parameter accepts query clause.

REPORTS EXECUTABLES

        All executables can be run from the command line.

Ø  Reports Builder (rwbuilder)

ü  An Oracle Reports executable that starts Reports Builder to enable report

developers to create and maintain report definitions.

Ø  Reports Runtime (rwrun)

ü  An Oracle Reports executable that runs a report using the OracleAS Reports Services in-process server.

Ø  Reports Servlet (rwservlet)

ü  An Oracle Reports executable that translates and delivers information between

HTTP and the Reports Server, enabling you to run a report dynamically from    

Your Web browser.

EXTENSIONS

v  .rdf : - (binary+text) Full report definition (includes source code and comments) modifiable through Builder binary, executable.

Portable if transferred as binary (PL/SQL recompiles on open and run).

v  .rep : - (binary) No source code or comments not modifiable binary, executable.

Portable as binary if no PL/SQL is present

v  .rex : - (text) Full report definition not modifiable ASCII text, not executable.

                    100% portable.

                     Note :- Save the report module in File system or  Database.

                    Save reports to the database for these reasons:

ü  Export from one database to another and Document your reports.

ü  Use as a central repository during the build process.