2.3 Data Model Objects

The topics in this section build on the basic concepts discussed in Section 1.7, "Data Model Objects".

2.3.1 About summary columns

A summary column performs a computation on another column's data. Using the Report Wizard or Data Wizard, you can create the following summaries: sum, average, count, minimum, maximum,% total. You can also create a summary column manually in the Data Model view, and use the Property Inspector to create the following additional summaries: first, last, standard deviation, variance.

If your report requires a customized computation, for example, one that computes sales tax, create a formula column (see Section 4.8.10, "Creating or editing a formula column").

Note:

For group reports, the Report Wizard and Data Wizard create n summary fields in the data model for each summary column you define: one at each group level above the column being summarized, and one at the report level. For example, if a report is grouped by division, and further grouped by department, then a summary column defined for a salary total would create fields for the sum of salaries for each division and each department group (group-level summaries), and the sum of all salaries (report-level summary).

See also

Section 4.8.11, "Creating a summary column"

2.3.2 About formula columns

A formula column performs a user-defined computation on the data of one or more column(s), including placeholder columns. For example, :ITEMTOT *.07 is a formula that performs a computation on one column, while :SAL + :COMM performs a computation using two columns in a record. You create formulas in PL/SQL using the PL/SQL Editor.

Note:

Formula columns should not be used to set values for parameters.

See also

Section 4.8.10, "Creating or editing a formula column"

Section 2.6.8, "About formulas"

2.3.3 About placeholder columns

A placeholder is a column for which you set the datatype and value in PL/SQL that you define. Placeholder columns are useful when you want to selectively set the value of a column (for example, each time the nth record is fetched, or each time a record containing a specific value is fetched, and so on). You can set the value of a placeholder column in the following places:

  • the Before Report trigger, if the placeholder is a report-level column

  • a report-level formula column, if the placeholder is a report-level column

  • a formula in the placeholder's group or a group below it (the value is set once for each record of the group)

See also

Section 4.8.12, "Creating or editing a placeholder column"

Section 2.6.8, "About formulas"

2.3.4 About referencing columns and parameters

You can reference user parameters, system parameters and columns as either bind references or lexical references.

2.3.4.1 About bind references

Bind references (or bind variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. 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. Bind references may not be referenced in FROM clauses or in place of reserved words or clauses.

You create a bind reference by typing a colon (:) followed immediately by the column or parameter name. If you do not create a column or parameter before making a bind reference to it in a SELECT statement, Oracle Reports Builder will create a parameter for you by default.

Restrictions

Bind references must not be the same name as any reserved SQL keywords. For more information, see the Oracle Server SQL Language Reference manual.

Examples

Example 1: SELECT clause

In the following example, the value of DFLTCOMM replaces null values of COMMPLAN in the rows selected.

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

Example 2: WHERE clause

The value of CUST is used to select a single customer.

SELECT ORDID, TOTAL
  FROM ORD
  WHERE CUSTID = :CUST;

Example 3: GROUP BY clause

All non-aggregate expressions such as NVL(COMMPLAN, :DFLTCOMM) in the SELECT clause must be replicated in the GROUP BY clause.

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

Example 4: HAVING clause 

The value of MINTOTAL is used to select customers with a minimum total of orders.

SELECT CUSTID, SUM(TOTAL) TOTAL
  FROM ORD
  GROUP BY CUSTID HAVING SUM(TOTAL) > :MINTOTAL;

Example 5:  ORDER BY clause

The value of SORT is used to select either SHIPDATE or ORDERDATE as the sort criterion. Note that this is not the same as ORDER BY 1 because :SORT is used as a value rather than to identify the position of an expression in the SELECT list. Note that DECODE is required in this example. You cannot use a bind variable in an ORDER BY clause unless it is with DECODE.

SELECT ORDID, SHIPDATE, ORDERDATE, TOTAL
  FROM ORD
  ORDER BY DECODE(:SORT, 1, SHIPDATE, 2, ORDERDATE);

Example 6:   CONNECT BY and START WITH clauses

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

procedure double is begin; :my_param := :my_param*2; end;

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

2.3.4.2 About lexical references

Lexical references are placeholders for columns or parameters that you embed in a SELECT statement. You can use lexical references 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.

You cannot make lexical references in a PL/SQL statement. You can, however, use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL, as shown in the example below.

You create a lexical reference by typing an ampersand (&) followed immediately by the column or parameter name. A default definition is not provided for lexical references. Therefore, you must do the following:

  • Before you create your query, define a column or parameter in the data model for each lexical reference in the query. For columns, you must set the Value if Null property, and, for parameters, you must set the Initial Value property. Oracle Reports Builder uses these values to validate a query with a lexical reference.

  • Create your query containing lexical references.

Restrictions

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

  • If a column or parameter is used as a lexical reference in a query, its Datatype must be Character.

  • If you want to use lexical references in your SELECT clause, you should create a separate lexical reference for each column you will substitute. In addition, you should assign an alias to each lexical reference. This enables you to use the same layout field and boilerplate label for whatever value you enter for the lexical reference on the Runtime Parameter Form.

  • If you use lexical references in your SELECT clause, you must specify the same number of items at runtime as were specified in the report's data model. Each value you specify for your lexical references at runtime must have the same datatype as its Initial Value.

  • If you use lexical references in your SELECT clause, the width of the column is derived from the Initial Value property of the parameter. Consequently, you should ensure that the Initial Value of the parameter corresponds to the widest column that you intend to use.

  • A Oracle Reports Builder link should not depend upon a lexical reference. That is, neither the child column of a link or its table name should be determined by a lexical reference. To achieve this functionality, you need to create a link with no columns specified and then enter the SQL clause (for example, WHERE) for the link directly in the query. For example, your parent and child queries might be written as follows:

    Parent Query:

    SELECT DEPTNO FROM EMP
    

    Child Query:

    SELECT &PARM_1 COL_1, &PARM2 COL_2
      FROM EMP
      WHERE &PARM_1 = :DEPTNO
    

    Note how the WHERE clause makes a bind reference to DEPTNO, which was selected in the parent query. Also, this example assumes that you have created a link between the queries in the Data Model view with no columns specified.

  • A lexical reference cannot be used to create additional bind variables after the After Form trigger fires. For example, suppose you have a query like the following (note that the WHERE clause is replaced by a lexical reference):

    SELECT ENAME, SAL FROM EMP
    &where_clause
    

    If the value of the where_clause parameter contains a reference to a bind variable, you must specify the value in the After Form trigger or earlier. You would get an error if you supplied the following value for the parameter in the Before Report trigger:

    WHERE SAL = :new_bind
    

    If you supplied this same value in the After Form trigger, the report would run.

Examples

Example 1: SELECT clause

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

P_ENAME, P_EMPNO, and P_JOB can be used to change the columns selected at runtime. For example, you could enter DEPTNO as the value for P_EMPNO on the Runtime Parameter Form. Note that in this case, you should use aliases for your columns. Otherwise, if you change the columns selected at runtime, the column names in the SELECT list will not match the Oracle Reports Builder columns and the report will not run.

Example 2: FROM clause

SELECT ORDID, TOTAL
  FROM &ATABLE;

ATABLE can be used to change the table from which columns are selected at runtime. For example, you could enter ORD for ATABLE at runtime. If you dynamically change the table name in this way, you may also want to use lexical references for the SELECT clause (look at the previous example) in case the column names differ between tables.

Example 3: WHERE clause

SELECT ORDID, TOTAL
  FROM ORD
  WHERE &CUST;

CUST can be used to restrict records retrieved from ORD. Any form of the WHERE clause can be specified at runtime.

Example 4: GROUP BY clause

SELECT NVL(COMMPLAN, DFLTCOMM) CPLAN, SUM(TOTAL) TOTAL
  FROM ORD
  GROUP BY &NEWCOMM;

The value of NEWCOMM can be used to define the GROUP BY clause.

Example 5: HAVING clause

SELECT CUSTID, SUM(TOTAL) TOTAL
  FROM ORD
  GROUP BY CUSTID HAVING &MINTOTAL;

The value of MINTOTAL could, for example, be used to select customers with a minimum total of orders.

Example 6: ORDER BY clause

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

The value of SORT can be used to select SHIPDATE, ORDERDATE, ORDID, or any combination as the sort criterion. It could also be used to add on to the query, for example to add a CONNECT BY and START WITH clause.

Example 7: CONNECT BY and START WITH clauses

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

Example 8: Multiple clauses

SELECT &COLSTABLE;

COLSTABLE could be used to change both the SELECT and FROM clauses at runtime. For example, you could enter DNAME ENAME, LOC SAL FROM DEPT for COLSTABLE at runtime.

SELECT * FROM EMP &WHEREORD;

WHEREORD could be used to change both the WHERE and ORDER BY clauses at runtime. For example, you could enter WHERE SAL > 1000 ORDER BY DEPTNO for &WHEREORD at runtime.

Example 9: PL/SQL and SQL 

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

BREAK_COL is used to change both the SELECT list and the GROUP BY clause at runtime. The Initial Value of the parameter &BREAK_COL is JOB. At runtime, the user of the report can provide a value for a parameter called GROUP_BY_COLUMN (of data type Character).

In the Validation Trigger for GROUP_BY_COLUMN, you call the following PL/SQL procedure and pass it the value of GROUP_BY_COLUMN:

procedure conv_param (in_var IN char) is
begin
  if upper(in_var) in ('DEPTNO','EMPNO','HIREDATE') then
    :break_col := 'to_char('||in_var||')' ;
  else
    :break_col := in_var;
  end if;
end;

This PL/SQL ensures that, if necessary, a TO_CHAR is placed around the break column the user chooses. Notice how in SQL, you make a lexical reference to BREAK_COL. In PL/SQL, you must make a bind reference to BREAK_COL because lexical references are not allowed in PL/SQL.

2.3.4.3 Differences between bind and lexical references

Bind references are used to replace a single value in SQL or PL/SQL. 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. Bind references may not be referenced in the FROM clause. An example is:

SELECT ORDID,TOTAL 
  FROM ORD
  WHERE CUSTID = :CUST

Lexical references are placeholders for text that you embed in a SELECT statement, when you want the parameter to substitute multiple values at runtime. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH. You cannot make lexical references in PL/SQL. Before you reference a lexical parameter for a column or table, you must have predefined the parameter and given it an initial value. An example is:

SELECT ORDID, TOTAL
  FROM &ATABLE

2.3.5 About non-linkable queries

A non-linkable query is a detail query that contains column objects that prevent the query from being linked to through a column-to-column link (when you create a column-to-column link, Oracle Reports Builder adds a WHERE clause to your query). If you attempt to create such a link, a message dialog box displays, which prompts you to choose whether to create a group-to-group query (using the parent groups), or to cancel the operation. A non-linkable query displays the non-linkable query icon in its title bar.

Instead, you can create a group-to-group link (when you create a group-to-group link, Oracle Reports Builder does not add a WHERE clause to your query) between the two queries and add a WHERE clause to the child query's SELECT statement, using a bind variable to reference the parent column. See Section 4.8.9, "Creating a data link".

For example, suppose you want to create a column-to-column link between the ADDRESS.STREET column in your child query and the LOC1 column in your parent query. You can create a group-to-group link, and then modify the child query SQL statement to say:

SELECT * FROM EMP E WHERE E.ADDRESS.STREET = :LOC1

See also

Section 1.7.4, "About data links"

Section 1.7.1, "About queries"

Section 2.3.4.1, "About bind references"

2.3.6 About links versus groups

In Oracle Reports Builder, data is defined independent of format (layout). Therefore, you should be aware of when to use data links instead of groups.

The layouts of a master/detail report that uses two queries and a data link, and a group report that uses one query and two groups can be identical. Following is an example of a default master/detail report and a group report that query the same data. Notice the difference between the two reports: unlike the group report, the master/detail report displays department 40. This is because the data link in the master/detail report causes an outer-join: the link automatically fetches unrelated data. If you are designing a group report that requires an outer-join, explicitly add it to your SELECT statement with (+).

Figure 2-3 Default master/detail and group report that query same data

Default master/detail and group report that query same data
Description of "Figure 2-3 Default master/detail and group report that query same data"

A master/detail/detail report, as shown in the figure below, is a report that contains three groups of data: for each master group, two unrelated detail groups are displayed. To produce a master/detail/detail report or any variation of it, you must use data links. If you try to produce this report with a control break using a single query and three groups the query will establish a relationship between the two detail groups.

Figure 2-4 Master/detail/detail report

Master/detail/detail report
Description of "Figure 2-4 Master/detail/detail report"

See also

Section 1.7.2, "About groups"

Section 1.7.4, "About data links"

2.3.7 About matrix objects

A matrix object merely defines a relationship between two repeating frames: it isn't really owned by any object, nor does it own any object. A matrix object is created only for layouts with a Matrix layout style. A report may have multiple matrices within it, provided that the data model contains the necessary groups. Oracle Reports Builder creates one matrix object for each pair of intersecting, perpendicular repeating frames.

The repeating frames are the dimensions of the matrix and the matrix object contains the field that will hold the "filler" or values of the cell group. One of the repeating frames must have the Print Direction property set to Down and the other must have the Print Direction property set to Across in order to form a matrix.

Note:

When running a JSP-based Web report that includes a matrix object, the JSP <rw:include> tag expects the matrix object to format on only one page. However, if the cells in the across repeating frame expand, the column headers of the matrix may be forced onto a second page regardless of the page size. Therefore, these matrix headers will not appear in the report output. Additionally, in this situation, the paper layout does not display correctly as header fields move to subsequent pages where the cell values remain on the first page. To resolve this, it is important to carefully define in the Report Editor the space available to the across repeating frame to allow for displaying the largest values, then set the Vertical Elasticity property of the across repeating frame to Fixed.

Restrictions

  • The down repeating frames must be below the across repeating frames in a matrix.

  • A matrix object must always be on top of the repeating frames that form it (that is, it must be one or more layers above its horizontal and vertical repeating frames). Oracle Reports Builder prevents you from moving the matrix below its horizontal and vertical repeating frames.

  • Moving a matrix also causes its two repeating frames to move.

  • A matrix object cannot be anchored to another object and other objects cannot be anchored to it (that is, a matrix object cannot be the parent or child object for an anchor).

  • To copy a matrix, you must select the matrix and its two repeating frames. If you select the matrix object by itself, nothing will be copied to the paste buffer. If you select the matrix and one of the repeating frames, only the repeating frame is placed in the paste buffer.

  • A matrix object can only be resized by resizing its associated repeating frames.

  • You cannot use Alignment or Size Objects from the Layout menu on matrix objects.

  • The source groups of the repeating frames that make up the dimensions of a matrix must be from the same cross-product group.

  • Repeating frames whose source groups are in the same "family" hierarchy (that is, are descendants or ancestors of each other) must have the same Print Direction. Parent-child relationships within a cross-product group are used to create nesting in the matrix. As a result, the repeating frames associated with such groups must print in the same direction on the page.

  • You can put a border on a matrix object just as you would any other object, but the width will always be the minimum width possible. You cannot widen the border due to the closeness of the objects in a matrix layout.

Example

Suppose that you have a group named Group1 that contains a column called C_DEPTNO, which gets its values from the database column DEPTNO. A group called Group2, contains column C_JOB, which gets its values from the database column JOB, and column C_DEPTNO1, which is used for linking to Group1's query. A group called Group3 contains a column called SUMSAL, which is a summary of the database column SAL.

                    Job 
          Analyst  Clerk  Manager 

      10           $1300   $2450 
Dept  20    $6000  $1900   $2975 
      30           $ 950   $2850 

In this example:

  • The Vertical Repeating Frame is the repeating frame that contains Group2 (the job titles).

  • The Horizontal Repeating Frame is the repeating frame that contains Group1 (the department numbers).

  • The Cross Product Group is Group4 (the group that is the parent of Group1 and Group2).

If you need to build a more complex matrix, you can do so by adding more columns to Group1 and Group2. For example, instead of having Group1 just contain department numbers, it could also contain the locations (LOC) of the departments. The matrix might then look something like this:

                              Job
 Loc        Dept    Analyst  Clerk  Manager
 
 New York    10              $1300   $2450
 Dallas      20      $6000   $1900   $2975
 Chicago     30              $ 950   $2850

See also

Section 1.3.7, "About matrix reports"

Section 2.1.7, "About nested matrix reports"

Section 2.1.8, "About matrix with group reports"

Section 4.9.1.3, "Creating a matrix object"

Section 4.8.8, "Creating a matrix (cross-product) group"

Section 4.5.3, "Creating a nested matrix report"