1. Summary column:-
A summary column performs a computation on another column's data. Sum, average, count, minimum, maximum, %, first, last,
standard deviation, variance.
2. Formula column:-
A formula column performs a user-defined computation on another column(s) data, including placeholder columns.
Note: Formula Column Should not be used to set value of Parameters.
3. Place Holder Column:-
A placeholder is a column for which you set the data type and value in PL/SQL that you define. You can set the value of a
placeholder column in the following places:
1. The Before Report Trigger, if the placeholder is a report-level column
2. A report-level formula column, if the placeholder is a report-level column
3. A formula in the placeholder's group or a group below it (the value is set once for each record of the group).
4. Types of Trigger:- 1.Report Trigger • Before Parameter Form • After Parameter Form • Before Report • Between
Pages • After Report 2.Validation Trigger 3.Format Trigger
5. Anchor: -
Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent. The end
of the anchor with a symbol on it is attached to the parent object..
Positioning is based on the size of the objects after the data has been fetched rather than on their size in the editor.
It should also be noted that the position of the object in the Layout editor effects the final position in the report
output.
6. Confine mode:-
To set or override Confine mode: In the Layout Model view, click in the toolbar to toggle the mode:
On: child objects cannot be moved outside their enclosing parent objects. Off: child objects can be moved outside their
enclosing parent objects.
ii)Flex mode:-
To set or override Flex mode:
In the Layout Model view, click in the toolbar to toggle the mode:
On: parent borders "stretch" when child objects are moved against them. Off: parent borders remain fixed when child
objects are moved against them.
7. Bit Map Vs Character:-
MODE specifies whether to run the report in character mode or bitmap. This enables you to run a character-mode report
from bit-mapped Report Builder or vice versa. For example, if you want to send a report to a Postscript printer from a
terminal (e.g., a vt220), you could invoke character-mode RWRUN60 and run the report with MODE=BITMAP. On Windows,
specifying MODE=CHARACTER means that the Report Builder ASCII driver will be used to produce editable ASCII output.
Syntax [MODE=]{BITMAP|CHARACTER|DEFAULT} Values BITMAP DEFAULT means to run the report in the mode of the current
executable being used. CHARACTER Default DEFAULT
8. Types of Report:-
1. Tabular
2. Form-like
3. Mailing Label
4. Form Letter
5. Group Left
6. Group Above
7. Matrix
8. Matrix With Group.
9. Srw.Package:-
To save you time, Report Builder is shipped with a built-in package (SRW), a collection of PL/SQL constructs, which
include many functions, procedures, and exceptions you can reference in any of your libraries or reports.
In addition to the Report Builder built-in package, Procedure Builder provides built-in PL/SQL packages that contain many
PL/SQL constructs you can reference while debugging your program units.
10. Matrix Report: -
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.
11. Matrix with Report:-
A matrix with group report is a group above report with a separate matrix for each value of the master group.
12. Srw.Do_sql: -
This procedure executes the specified SQL statement from within Report Builder. The SQL statement can be DDL
(statements that define data), or DML (statements that manipulate data). DML statements are usually faster when they
are in PL/SQL, instead of in SRW.DO_SQL.
Since you cannot perform DDL statements in PL/SQL, the SRW.DO_SQL packaged procedure is especially useful for performing
them within Report Builder, instead of via a user exit. For more information on DDL or DML statements, see the ORACLE8
Server SQL Language Reference Manual.
Syntax SRW.DO_SQL (sql_statement CHAR);
Parameters sql_statement Is any valid SQL statement. Remember to precede any Report Builder object names with a
colon (:).
Example:-
/* Suppose you want your report to create a table named CHECK ** just before the Runtime Parameter Form is displayed. **
Because CREATE TABLE is a SQL DDL statement (and PL/SQL ** cannot perform DDL statements), you need to use SRW.DO_SQL. **
Therefore, your PL/SQL could look like this in the Before Form trigger: */ /* Additional Information: If you use a table
created in this way for your ** report output, the table must exist before you create your query in the ** data model.
Otherwise, Report Builder would not be able to parse your query. */
FUNCTION CREATETAB RETURN BOOLEAN IS BEGIN SRW.DO_SQL('CREATE TABLE CHECK (EMPNO NUMBER NOT NULL PRIMARY KEY, SAL NUMBER
(10,2)) PCTFREE 5 PCTUSED 75'); RETURN (TRUE); EXCEPTION WHEN SRW.DO_SQL_FAILURE THEN SRW.MESSAGE(100, 'ERROR WHILE
CREATING CHECK TABLE.'); SRW.MESSAGE (50, 'REPORT WAS STOPPED BEFORE THE RUNTIME PARAMETER FORM.'); RAISE
SRW.PROGRAM_ABORT; END;
/* Suppose you want to create a "table of contents" by getting the ** first character of a column's value, and page number
on which its ** field fires to print. Assume that you want to put the "table of contents" ** into a table named SHIP.
You could write the following construct: */
DECLARE PAGE_NO NUMBER; PAGE_FOR INDEX NUMBER; SORT_CHAR CHAR(1); CMD_LINE CHAR(200); BEGIN SORT_CHAR :=
:SORT_NAME ; IF :CALLED = 'Y' THEN SRW.GET_PAGE_NUM(PAGE_FOR_INDEX); SRW.USER_EXIT('RWECOP PAGE_FOR_INDEX
P_START_PAGENO'); SRW.MESSAGE(2,TO_CHAR(:P_START_PAGENO)); END IF; SRW.GET_PAGE_NUM(PAGE_NO); CMD_LINE := 'INSERT INTO
SHIP VALUES ('''||SORT_CHAR||''','||TO_CHAR(PAGE_NO)||')';
SRW.MESSAGE(2,CMD_LINE); SRW.DO_SQL(CMD_LINE); COMMIT; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; WHEN
SRW.DO_SQL_FAILURE THEN SRW.MESSAGE(1,'FAILED TO INSERT ROW INTO SHIP TABLE'); WHEN OTHERS THEN COMMIT; END;
13. SRW.SET_MAXROW:-
This procedure sets the maximum number of records to be fetched for the specified query. This is useful when your
report formats (i.e., displays) fewer records than the query (or queries) that fetch them. Thus, with SRW.SET_MAXROW, you
can conditionally restrict data that is fetched for your report, enabling you to improve the report's performance. Syntax
SRW.SET_MAXROW (query_name CHAR, maxnum PLS_INTEGER);
Parameters query_name Is the query whose fetched records will be limited. maxnum Is maximum number of records you
want the query to fetch. Property Palette To define this attribute using the Property Palette, set the Maximum Rows to
Fetch property.
Example
/* Suppose your report has two queries, Q_Stocks and Q_Bonds. ** Suppose also, that you have a user-created parameter,
named ** WHICHDATA, that enables users to specify which data they want ** the report to display: either stocks or bonds.
In the ** Before Report trigger, you could use the SRW.SET_MAXROW ** procedure to ensure that only one query's data is
fetched: */
FUNCTION FETCHIT RETURN BOOLEAN IS BEGIN if :whichdata != 1 then srw.set_maxrow ('Q_Stocks', 0); else srw.set_maxrow
('Q_Bonds', 0); end if; RETURN (TRUE); END;
14. SRW.SET_FIELD_CHAR:-
Description This procedure sets the value of a character field. This is useful when you want to conditionally change a
field's character value.
Syntax SRW.SET_FIELD_CHAR (object_id, text CHAR);
Parameters object_id Is always 0. (The object must always set its own attributes.) text Is the character string
you want the field to display.
Example:- /* Suppose you want to conditionally change the value of a ** Character field, based on each employee's salary.
** In the format trigger for the field, you could type the following: */ FUNCTION CHGFIELD RETURN BOOLEAN IS BEGIN if :sal
>= 2000000 then srw.set_field_char (0, 'HIGH SALARY'); end if; RETURN (TRUE); END;
15. Format Trigger:-
Format triggers are PL/SQL functions executed before the object is formatted. The trigger can be used to dynamically
change the formatting attributes of the object. The function must return a Boolean value (TRUE or FALSE). Depending on
whether the function returns TRUE or FALSE, the current instance of the object is included or excluded from the report
output. You can access format triggers from the Object Navigator, the Property Palette, or the PL/SQL Editor.
On Failure Excludes the current instance of the object from the output.
Usage Notes
1 Format triggers do not affect the data retrieved by the report. For example, if a format trigger returns FALSE
for a field, the data for the field is retrieved even though the field does not appear in the output.
2 If a format trigger suppresses report output on the last page of the report, the last page will still be formatted
and sent to the
Example:-
/* Suppose that you want to create a comma-separated ** list for the names of employees. First, you create ** a field
inside a repeating frame with a Print ** Direction of Across. Next to the field, you create ** a boilerplate text object
that contains a comma ** followed by a space. To ensure that the comma does ** not appear after the last name in the
list, enter the ** following format trigger for the boilerplate object. ** LASTNAME is a summary column with a Source of
ENAME, ** a Function of Last, and Reset At of Report. */
function my_formtrig return BOOLEAN is begin if :ename <> :lastname then return (true); else return (false); end if; end;
16. Validation Trigger:-
Validation triggers are PL/SQL functions that are executed when parameter values are specified on the command line and
when you accept the Runtime Parameter Form. (Notice that this means each validation trigger may fire twice when you
execute the report.) Validation triggers are also used to validate the Initial Value property of the parameter. The
function must return a boolean value (TRUE or FALSE). Depending on whether the function returns TRUE or FALSE, the user
is returned to the Runtime Parameter Form. You can access validation triggers from the Object Navigator, the PL/SQL
Editor, or the Property Palette (Validation Trigger property).
Definition Level parameter On Failure The user is returned to the parameter value in the Runtime Parameter Form where
they can either change it or cancel the Runtime Parameter Form.
Example
/* This function prevents the runtime user from ** sending report output anywhere except a printer. ** The user will be
returned to the Runtime Parameter ** Form unless PRINTER is specified as the destination ** type (DESTYPE). */
function DESTYPEValidTrigger return boolean is begin IF UPPER(:DESTYPE) = 'PRINTER' THEN RETURN(TRUE); ELSE RETURN(FALSE);
END IF; end;
17. Bind Variable:- Description Binds a given value to a named variable in a SQL statement.
Syntax
PROCEDURE EXEC_SQL.Bind_Variable ([Connid IN CONNTYPE], Curs_Id IN CURSTYPE, Name IN VARCHAR2, Value IN
where
NUMBER DATE VARCHAR2
PROCEDURE EXEC_SQL.Bind_Variable ([Connid IN CONNTYPE], Curs_Id IN CURSTYPE, Name IN VARCHAR2,
Value IN VARCHAR2, Out_Value_Size IN PLS_INTEGER);
Parameters
Connid Is the handle to the connection you want to use. If you do not specify a connection, EXEC_SQL.Default_Connection
retrieves the primary Developer connection handle from the cache. Curs_Id Is the cursor handle in which to bind the
variable. Name Is the name of the variable in the SQL statement. Value For IN and IN/OUT variables, the value is the data
you want to bind to the named variable. For OUT variables, the data is actually ignored but you must still use
Bind_Variable to indicate the type of PL/SQL variable to be retrieved later by Variable_Value. Out_Value_Size The
maximum OUT value size in bytes expected for the VARCHAR2 OUT or IN/OUT variables. If no size is specified, the current
length of the Value parameter is used. Usage Notes Use placeholders in SQL statements to mark where input data is to be
supplied during runtime. You must also use placeholders for output values if the statement is a PL/SQL block or a call to
a stored procedure with output parameters. For each input placeholder, you must use EXEC_SQL.Bind_Variable to supply the
value. For each output placeholder, you must also use EXEC_SQL.Bind_Variable to specify the type of variable to use for
retrieving the value in subsequent EXEC_SQL.Variable_Value calls.
The input placeholder or bind variable in a SQL statement is identified by a name beginning with a colon. For example, the
string ':X' is the bind variable in the following SQL statement: SELECT ename FROM emp WHERE SAL > :X; The corresponding
EXEC_SQL.Bind_Variable procedure is: BIND_VARIABLE(connection_handle, cursor_handle, ':X', 3500);
Example:- PROCEDURE getData(input_empno NUMBER) IS connection_id EXEC_SQL.CONNTYPE; cursorID EXEC_SQL.CURSTYPE; sqlstr
VARCHAR2(1000);
...
BEGIN connection_id := EXEC_SQL.OPEN_CONNECTION(connect_str); cursorID := EXEC_SQL.OPEN_CURSOR(connection_id); -- -- the
statement to be parsed contains a bind variable -- sqlstr := 'select ename from emp where empno = :bn'; -- -- perform
parsing -- EXEC_SQL.PARSE(connection_id, cursorID, sqlstr, exec_sql.V7); -- -- the bind_variable procedure assigns the
value of the input argument to the named -- bind variable. Note the use of the semi-colon and the quotes to designate
the -- bind variable. The bind_variable procedure is called after the parse procedure. --
EXEC_SQL.BIND_VARIABLE(connection_id, cursorID, ':bn', input_empno); EXEC_SQL.DEFINE_COLUMN(connection_id, cursorID, 1,
mynum);
...
END;
18. Differences between Lexical and Bind parameters
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. Binds 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. 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 in a query you must have predefined the parameter and
given it an initial value. An example is:
SELECT ORDID, TOTAL FROM &ATABLE
19. SRW.SET_FIELD_NUM:-
Description This procedure sets the value of a number field. This is useful when you want to conditionally change a
field's number value. Syntax SRW.SET_FIELD_NUM (object_id, number NUM); Parameters
object_id Is always 0. (The object must always set its own attributes.) number Is the number you want the field
to display.
/* Suppose you want to conditionally change the number of a field, ** based on each employee's salary. In the format
trigger for the ** field, you could type the following: */
Example:-
FUNCTION CHGFIELD RETURN BOOLEAN IS TMP NUMBER; BEGIN if :sal >= 2000 then tmp := :sal * 1.08; srw.set_field_num (0,
tmp); else srw.set_field_num (0, '2500'); end if; RETURN (TRUE); END;
20. SRW.SET FIELD :-
This procedure sets the value of a character, number, or date field. This is useful when you want to conditionally
change a field's value.
Syntax SRW.SET_FIELD (object_id, text CHAR|number NUM|date DATE);
Parameters object_id Is always 0. (The object must always set its own attributes.) text number date Is the character,
number, or date string you want the field to display.
Usage Notes
For date values, you need to convert the second argument to a date value with the TO_DATE function. For example:
srw.set_field(0, to_date('01-JAN-99'));
If you omit quotes around the value, the value is assumed to be a number. For
example:
srw.set_field(0, 99);
Alternatively, you can use the quotes if you also use the TO_NUMBER function. For
example:
srw.set_field(0, to_number('99'));
If you use quotes without a function, the value is assumed to be a character string. Alternatively, you can use
the TO_CHAR function for consistency with number and date values:
srw.set_field(0, to_char('my string'));
21. SRW.SET_FORMAT_MASK:-
It is now more convenient to set this attribute using the SRW.SET_FORMAT_MASK procedure. Setting this attribute via
SRW.SET_ATTR is still supported for compatibility, but it is highly recommended that you use the new, simplified
procedure. Description Is the format mask for the DATE, or NUMBER field.
Syntax: SRW.SET_FORMAT_MASK('mask'); Parameters
Mask : Is a valid format mask. See Date Format Mask Syntax or Number Format Mask Syntax
Property Palette : To define this attribute using the Property Palette, set the Format Mask property.
22. i) Vertical Elasticity:-
The Vertical Elasticity property is how the vertical size of the object may change at runtime to accommodate the
objects or data within it:
1 For frames and repeating frames, elasticity defines whether the size of the frame or repeating frame should vary
with the objects inside of it.
2 For objects containing text, elasticity defines whether the field or boilerplate should vary with the size of the
text. Fixed size text will wrap within the defined size of the object and may be truncated if there is not enough room.
Number or date data will appear as asterisks if the data cannot fit within the defined size.
3 For images, drawings, and chart objects, Report Builder uses proportional scaling. The elasticity options for
images, drawings, and chart objects determine the scaling factor.
Values
Contract Means the vertical size of the object decreases, if the formatted objects or data within it are short
enough, but it cannot increase to a height greater than that shown in the Report Editor. Truncation of data may occur;
look at the examples. (You can think of this option as meaning "only contract, do not expand.")
Expand Means the vertical size of the object increases, if the formatted objects or data within it are tall enough, but
it cannot decrease to a height less than that shown in the Report Editor. (You can think of this option as meaning "only
expand, do not contract.") Fixed Means the height of the object is the same on each logical page, regardless of the
size of the objects or data within it. Truncation of data may occur; look at the examples. The height of the object is
defined to be its height in the . Variable Means the object may expand or contract vertically to accommodate the
objects or data within it (with no extra space), which means the height shown in the Report Editor has no effect on the
object's height at runtime.
If you create a chart object in your report with a Vertical and Horizontal Elasticity of Fixed, Report Builder
scales the Graphics Builder display to fit within the dimensions you defined for the object.
ii) Horizontal Elasticity:-
Description The Horizontal Elasticity property is how the horizontal size of the object will change at runtime to
accommodate the objects or data within it:
1 For frames and repeating frames, elasticity defines whether the size of the frame or repeating frame should vary
with the objects inside of it.
2 For objects containing text, elasticity defines whether the field or boilerplate should vary with the size of the
text. Fixed size text will wrap within the defined size of the object and may be truncated if there is not enough room.
Number or date data will appear as asterisks if the data cannot fit within the defined size.
3 For images, drawings, and chart objects, Report Builder uses proportional scaling. The elasticity options for
images, drawings, and chart objects determine the scaling factor.
Values
Contract Means the horizontal size of the object decreases, if the formatted objects or data within it are wide
enough, but it cannot increase to a width greater than that shown in the Report Editor. Truncation of data may occur; look
at the examples. (You can think of this option as meaning "only contract, do not expand.")
Expand Means the horizontal size of the object increases, if the formatted objects or data within it are wide enough, but
it cannot decrease to a width less than that shown in the Report Editor. (You can think of this option as meaning "only
expand, do not contract.")
Fixed Means the width of the object is the same on each logical page, regardless of the size of the objects or data
within it. Truncation of data may occur; look at the examples. The width of the object is defined to be its width in the
Report Editor.
Variable Means the object may expand or contract horizontally to accommodate the objects or data within it (with no
extra space), which means the width shown in the Report Editor has no effect on the object's width at runtime.
If you create a Graphics object in your report with a Vertical and Horizontal Elasticity of Fixed, Report Builder
scales the display to fit within the dimensions you defined for the object.
23. System Parameters:-
BACKGROUND Is whether the report should run in the foreground or the background.
COPIES Is the number of report copies that should be made when the report is printed.
CURRENCY Is the symbol for the currency indicator (e.g., "$").
DECIMAL Is the symbol for the decimal indicator (e.g., ".").
DESFORMAT Is the definition of the output device's format (e.g., landscape mode for a printer). This parameter is
used when running a report in a character-mode environment, and when sending a bitmap report to a file (e.g. to create PDF
or HTML output).
DESNAME Is the name of the output device (e.g., the file name, printer's name, mail userid).
DESTYPE Is the type of device to which to send the report output (screen, file, mail, printer, or screen using PostScript
format).
MODE Is whether the report should run in character mode or bitmap.
ORIENTATION Is the print direction for the report (landscape, portrait, default).
PRINTJOB Is whether the Print Job dialog box should appear before the report is run.
THOUSANDS Is the symbol for the thousand's indicator (e.g., ",").
------------------------------------------------------------------------------------------------------
---: Oracle Report Questions End :---
