Column Mode Property in Reports

The Column Mode property controls how Report Builder fetches and formats data for instances of repeating frames.  With Column Mode set to yes, the next instance of a repeating frame can begin formatting before the previous instance is completed. With Column Mode set to No, the next instance cannot begin formatting before the previous instance is completed. Column Mode is used mainly for master repeating frames or repeating frames that contain fields that may expand vertically or horizontally (i.e., elasticity is Variable or Expand). 

Lexical & Bind Parameters in Reports

Lexical Parameters: Lexical references are placeholders for text 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.

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.  Look at the example below.

You create a lexical reference by entering 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 enter Value if Null, and, for parameters, you must enter Initial Value.  Report Builder uses these values to validate a query with a lexical reference.

Ø  Create your query containing lexical references.

Bind Parameters: 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 entering 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, Report Builder will create a parameter for you by default.

Matrix Reports (Matrix By Groups)

A matrix (cross tab) report contains one row of labels, one column of labels, and information in a grid format that is related to the row and column labels.  A distinguishing feature of matrix reports is that the number of columns is not known until the data is fetched from the database. 

To create a matrix report, you need at least four groups: one group must be a cross-product group, two of the groups must be within the cross-product group to furnish the "labels," and at least one group must provide the information to fill the cells.  The groups can belong to a single query or to multiple queries. 

A matrix with group report is a group above report with a separate matrix for each value of the master group.

A nested matrix (cross tab) report is a matrix report in which at least one parent/child relationship appears within the matrix grid. 

           The new Child Dimension property of the nested group enables you to eliminate empty                              rows and/or columns in your single-query nested matrix.

Types of Matrix Reports

Simple Matrix Report:

Is a matrix with only two dimensions

Nested Matrix Report: Has multiple dimensions going across and/or down the page

Multi-Query Matrix with Break: Is similar to a nested matrix report in that it has more than two dimensions. Does not display records that do not contain data

Matrix Break Reports: Contains a new matrix for each master record

Flex & Confine Mode in Reports

Confine mode:

1. Switched on by default; change via View® View Options® Layout...
2. It prevents operations which would cause a report not to work e.g. moving a field outside its parent repeating frame

Flex mode:

Moves the object it’s enclosing objects and objects in their push path simultaneously to maintain the same overall relationship in the report. E.g. if you try to move a field outside its repeating frame, the Repeating Frame will grow to accommodate the field and so will any objects around the repeating frame.

Only one object can be moved/resized at one time in flex mode - if you try more than one only one whose control point is clicked on will be done, the other objects will be de-selected.

Objects can be moved/resized horizontally or vertically; not diagonally. 

Static & Dynamic LOV/Format Triggers (What are they)

1.     Static & Dynamic LOV

The static LOV contains the predetermined values while the dynamic LOV contains values that come at run time

2.     Format Triggers (What are they)

A format trigger is a PL/SQL function executed before an object is formatted.  A trigger can be used to dynamically change the formatting attributes of the object. 

Report Level Triggers (Sequence)

• Before parameter form
• After parameter form
• Before Report
• Between Pages
• After Report