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