1) What
are the types of layouts in reports?
1) There are 8 default layout
style provided.
a) Tabular - It is the most basic
type of report where each column corresponds to a column
selected from the database.
b) Form-like - It is a report one
record per page. The field values are placed to the field labels.
c) Mailing Label - A mailing label
report prints mailing labels in multiple columns on each page. The printing can
be a landscape or a portrait.
d) Form Letter - A Form letter
report contains database values embedded into any text that is entered or
imported into the report editor.
h) Group Left - A group left
report divides the rows of a table into sets, based on a common
value in one of the columns. This
style is used to restrict a column from repeating the same
value several times while values
of related columns change.
g) Group Above - A group above
repot contains two or more groups of data. For every value of
the master group, the related
values of the detail group(s) are fetched from the database.
h) Matrix - A matrix (cross tab)
report contain one row of tables, one column of labels and
information in a grid format that
is related to the row and column labels.
A distinguished feature of matrix
report is that the number of columns is not known until the data is fetched
from the database.
To create a matrix report, at
least four groups are required. 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
provide to a single query or to multiple
queries.
i) Matrix with Group: A matrix
with group report is a group above report with a separate matrix for each value
of the master group.
2) What
are the different layout objects?
2) There are 4 types of layout
objects.
a) Repating Frames.
b) Frames.
c) Fields.
d) Bilerplate.
a) Frames: Frames surrounds other
layout objects, enabling control of multiple objects simultaneously, ensuring
that they maintain their positions relative to each other in the report. a
frame might be used to sorround all objects owned by a group, to surround
column labels.
b) Repating Frames: Repeating
frames act as placeholder for groups (repeating values) and present rows of data retrived from the database.
Repating frames repeat as often as the number of rows retrieved.
c) Fields: Fields act as
placeholder for column values, They define the formatting attributes for all
columns displayed in the report. A field is one of the objects that can be
located inside a frame or repeating frame.
d) Bilerplate: Boilerplate
consists of text and graphics that appear in a report each time it is run;
e.g., a label appearing above a column of data is boilerplate text. Graphics
drawn in the layout as well as text added to the layout are boilerplate.
2) What
is data model?
2) To specify the data for the
report, a data model should be defined. A data model is composed of some or all
of the following data definition objects.
a) Queries - Queries are
ANSI-standard SQL SELECT statements that fetch data from a standard
database such as Oracle, DB2 etc.
These SELECT statements are fired each time the report is run.
You can select any number of
queries to select data from any number of tables.
b) Groups - Groups determine the
hierarchy of data appearing in the report, and are primarily used to create
breaks in the report. Oracle report automatically creates a group for each
query, but you are not limited to this default. You can create a new group in
the data model and included a column that you want to use as the break column.
c) Column - Column contain the
data values for a report. Default report columns, corresponding to the table
columns included in each query's SELECT list are automatically created by the
table Oracle Report, then each column is placed in the group associated with
the query that selected the column. If you want to perform summaries and
computations on the database column values, you can create new columns. You can
also reassign one or more columns to a groups you've created.
d) Parameters - Parameters are
visibles for your report that enable you to change seletion criteria at
runtime. Oracle Report automatically creates a set of system parameters at
runtime, but you can create your own as well. You can create parameters to
replace either single literal values or entire expressions in any part of a
query. You can reference parameters elsewhere in the report, such as in PL/SQL
constructs providing conditional lagic for the report.
e) Data Link - Data link are used
to establish parent-child relationship between queries and groups via column
matching.
3) What
is anchor?
3) Anchors fasten an edge of one
object to an edge of another object, ensuring that they maintain their relative
positions. For example, you can anchor boilerplate text to the edge of a
variable-sized repeating frame, guaranteeing the boilerplate's distance and
position in relation to the repeating frame, no matter how the frame's size
might change.
4) What
types of triggers are there in report?
4) As a general rule,any
processing that will affect the data retrieved by the report should
be performed in the Before
Parameter Form and After Parameter Form trigger.(These are the two report
trigger that fire before anything is parsed or fetched). There are five global
report triggers. You cannot create new global report triggers. The trigger
names indicate at point trigger fires:
1. Before Report: Fires before the
report is executed but after queries are parsed.
2. After Report: Fires after you
exit the Previewer, or after report output is sent to a specified destination,
such as a file, a printer, or an Oracle Office userid. This trigger
can be used to clean up any
initial processing that was done, such as deleting tables. Note,
however, that this trigger always
fires, whether or not your report completed successfully.
3. Between Pages: Fires before
each page of the report is formmated, except the very first
page. This trigger can be used for
customized page formatting. In the Previewer, this trigger only fires the first
time that you go to page. If you subsequently
return to the page, the trigger
does not fire again.
4. Before Parameter Form: Fires
before the Runtime Parameter Form is displayed. From this trigger, you can
access and change the values of parameters, PL/SQL global variables, and
report-level columns. If the Runtime Parameter Form is suppressed, this trigger
still fires. Consequently, you can use this trigger for validation of command
line parameters.
5. After Parameter Form: Fires
after the Runtime Parameter Form is displayed. From this trigger, you can
access parameters and check their values. This trigger can also be used to
change parameter values or, if an error occurs, return to the Runtime Parameter
Form. Columns from the data model are not accessible from this trigger. If the
Runtime Parameter Form is suppressed, the After Parameter Form trigger still
fires. Consequently, you can use this trigger for validation of command line
parameters or other data.
4) What
is the order of execution of report trigger?
4) Order of execution of report
trigger:
1. Before Parameter Form trigger
is fired.
2. Runtime Parameters Form appears
(if not suppressed).
3. After Parameters Form trigger
is fired(unbless the user cancels from the Runtime Parameter Form).
4. Report is "compiled".
5. Queries are parsed.
6. Before Report trigger is fired.
7. Set TRANSACTION READONLY is
executed(if specified via the READONLY argument or setting).
8. The report is executed and the
Between Pages trigger fires for each page except the last one. (Note that data
can be fetched at any time while the report is being formatted).
5) What
is Group filter?
5) A group filter is a PL/SQL
function that determines which records to include in a group, if the Filter
Type property is POL/SQL. The function must return a boolean value. Depending
on whether the function returns TRUE of FALSE, the current record is included
or excluded from the report. You can access group filters from the Object
Navigator, the Property Palette(the PL/SQL Filter property), or the PL/SQL
Editor.
Definition Level: Group
OnFailure: Excludes the current
record from the group.
Example:
function filter_comm return
boolean is
begin
if :comm IS NOT NULL then
if :comm < 100 then
return(FALSE);
else
return(TRUE);
end if;
else
return(FALSE); -- for rows with NULL commissios
end if;
end;
6) What
is Formula Column?
6) Formula are PL/SQL functions
that populate formula or placeholder columns. You can access the PL/SQL for
formulas from the Object Navigator, the PL/SQL Editor, or the Property
Palette(i.e. PL/SQL Formula property) . A column of datatype Number can only
have a formula that returns a value of datatype Number. A column of datatype
Date can only have a formula that returns a value of datatype Date. A column of
datatype Character can only have a formula that returns a value of datatype
CHARACTER, VARCHAR, or VARCHAR2.
Definition Level: column
On Failure: No values is returned
for the column.
For example1: formula for adding
values
function salcomm return NUMBER is
beign
return(:sal + :comm);
end;
For example1: formula with
condition
function calcomm return NUMBER is
temp number;
beign
if :comm IS NOT NULL then
temp := :sal + :comm;
else
temp := :sal;
end if;
return(:temp);
end;
7) What
is Validation trigger?
7) Validation trigger 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
trigger are also used to validate the
Initial Value property of the parameter. The function must return a boolean
value.
Definition Level: parameter
On Failure: The user is return to
the parameter value in the Runtime Parameter Form where they can either change
it or cancel the Runtime Parameter Form.
For Example
/* This function prevent the
runtime user from sending report output anywhere except a printer*/
function DESTYPEValidTrigger
return boolean is
begin
if upper(:DESTYPE) = 'PRINTER' Then
Return(true);
else
Return(False);
end if;
end;
On Failure: No values is returned
for the column.
8) What
is Format trigger?
8) 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.
Definition Level: layout object
On Failure: Excludes the current instance of the object
from the output.
Format trigger example
(highlighting a value)
/* Suppose that you are building a
banking report and would like it to indicate if a customer is overdrawn. To do
so, you give the repeating frame around the customer information a format
trigger that causes it to have a border only if a customer's account balance is
less than 0 (or the required minimum balance). */
function my_formtrig return
BOOLEAN is
begin
if :bal < 0 then
srw.attr.mask := SRW.BORDERWIDTH_ATTR;
srw.attr.borderwidth := 1;
srw.set_attr (0, srw.attr);
end if;
return (true);
end;
8) What
is Action trigger?
8) Action triggers are PL/SQL
procedures executed when a button is selected in the Runtime Previewer. The
trigger can be used to dynamically call another report (drill down) or execute
any other PL/SQL. You can access action triggers from the Object Navigator, the
Property Palette (PL/SQL Trigger property), or the PL/SQL Editor.
Definition Level: button
9) What
is Ref cursor query?
9) A ref cursor query uses PL/SQL
to fetch data for the report. In a ref cursor query, you specify a PL/SQL
function that returns a cursor value from a cursor variable.
Definition Level: query
On Failure: No data is returned to
the query .
Package with ref cursor and
function example This package spec and body define a ref cursor type as well as
a function that uses the ref cursor to return data. The function could be
referenced from the ref cursor query, which would greatly simplify the PL/SQL
in the query itself. If creating this
spec and body as a stored procedure in a tool such as SQL*Plus, you would need
to use the CREATE PACKAGE and
CREATE PACKAGE BODY commands. */
PACKAGE cv IS
type comp_rec is RECORD
(deptno
number,
ename varchar(10),
compensation number);
type comp_cv is REF CURSOR return comp_rec;
function emprefc(deptno1 number) return comp_cv;
END;
PACKAGE BODY cv IS
function emprefc(deptno1 number) return
comp_cv is
temp_cv cv.comp_cv;
begin
if deptno1 > 20 then
open temp_cv for select deptno, ename,
1.25*(sal+nvl(comm,0)) compensation
from emp where deptno = deptno1;
else
open temp_cv for select deptno, ename,
1.15*(sal+nvl(comm,0)) compensation
from emp where deptno = deptno1;
end if;
return temp_cv;
end;
END;
10) How
many types of columns are there and what are they
10) 1. Formula columns: For doing
mathematical calculations and returning one value. formulas column compute
their values using PL/SQL expressions. Formula can operate on multiple values
per record
(e.g. :avg_price*:quantity).
2. Summary Columns: For doing
summary calculations such as summations etc.
3. Place holder Columns: These
columns are useful for storing the value in a variable.
11) Can u
have more than one layout in report?
11) It is possible to have
more than one layout in a report by using the additional layout
option in the layout editor.
12) Can u run the report with out a parameter form?
12) Yes it is possible to run the
report without parameter form by setting the PARAM value to Null
13) What is the lock option in reports layout?
13) By using the lock option we
cannot move the fields in the layout editor outside the frame. This is useful
for maintaining the fields.
14) What is Flex?
14) Flex is the property of moving
the related fields together by setting the flex property on.
15) What is default Unit of Measurement in Report?
15) Inch.
16) How many types of Parameters are available in Reports?
16) There are two types of
Parameters available in Reports
One is System Parameter and
another is User Parameter.
Parameters can be used to:
• Restrict values in a WHERE
clause SELECT NAME, SALES_REP_ID FROM S_CUSTOMER WHERE ID =User Parameters<a
value>
• Substitute any part of select
statement SELECT NAME, SALES_REP_ID FROM S_CUSTOMER
<a where clause>
• Substitute a single column or
expression SELECT <a column/expression> FROM S_CUSTOMER
There are two ways to reference
parameters in a query:
• Use a bind reference.
• Use a lexical reference.
17) What are bind variables?
17) Variable that 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.
A bind reference replaces a single
value or expression. To create a bind reference in a query, prefix the
parameter name with a colon (:). 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.
18) Which Procedures displays message number and text that you specify?
18) SRW.MESSAGE: This procedure
displays a message with the message number and text that you specify. The message is displayed in the format
below. After the message is raised and
you accept it, the report execution will continue.
19) What are lexical parameters?
19) You can define lexical
parameters in your reports. Lexical parameters can dynamically replace clauses
in the Select statement in the data model and even the whole select statement=
A lexical reference replaces any
part of a SELECT statement, such as column names, the FROM clause, the WHERE
clause, the ORDER BY clause.
To create a lexical reference in a
query, prefix the parameter name with an ampersand (&).
If the parameter object does not
exist, Report Builder does not create. You must always create the parameter for
a lexical reference in the Object Navigator.
Using Lexical References:
Use a lexical reference to replace any clause in a SELECT statement, or
evenreplace the entire statement.
Examples
• The following statements use
lexical references to substitute parts of the query
at run time.
SELECT NAME, SALES_REP_ID FROM
S_CUSTOMER &where_clause
• To specify a WHERE clause, ORDER
BY clause, or both at run time (as two separate parameters).
SELECT NAME, SALES_REP_ID FROM
S_CUSTOMER &where_clause1, &where_clause2
• To specify a WHERE clause, ORDER
BY, or both clause at run time (as one parameter).
SELECT NAME, SALES_REP_ID FROM
S_CUSTOMER &where_clause
• To specify two column names and
the table name(s) at run time:
SELECT &P_CUSTNAME CUST,
&P_SALESREP REP FROM &P_TABLE
Note: When you use lexical
references in the SELECT list, you must, at run time, specify the same number
of items of the correct datatype, as defined in the Data Model.
No comments:
Post a Comment