Reports:
1. What is an Oracle Reports?
Oracle Reports is a tool for developing, displaying, and printing production-quality reports. It is designed for application developers who are familiar with SQL and PL/SQL.
2. What are Report-level Objects?
Report-level objects are used to build reports but cannot be referenced by other modules. An example of a report-level object is a field, which defines how a column's data appears in the report output.
Report-level objects are created either by you or by Oracle Reports. If you create an object, it is called a user-created object; if Oracle Reports creates an object, it is called a default object.
Report-level objects can belong to any one of three functional sets:
Data Model Objects : Data model objects, which define the data, calculations, and parameters (i.e., variables) for the report.
Layout Objects : Layout objects which define the appearance of the report.
Parameter Form Objects : Parameter form objects, which define the appearance of the Runtime Parameter Form (i.e., the dialog that appears at runtime in which users can specify parameter values)
3. What are the different file extensions that are created by oracle reports?
REP file and RDF file.
Live Previewer:
Data Model Editor:
4. Define Data Model for a Report and What it composed of?
A data model is composed of some or all of the following data definition objects:
System Parameters :
BACKGROUND, COPIES, CURRENCY, DECIMAL, DESFORMAT, DESNAME,
DESTYPE, MODE, ORIENTATION, PRINTJOB, THOUSANDS
User Parameters : Created by the user for a parameter form.
Queries : Queries are ANSI-standard SQL SELECT statements that fetch data from a standard database such as ORACLE, DB2, or SQL/DS. You can use any number of queries to select data from any number of tables, located in any number of databases on any number of machines.
Groups : Groups determine the hierarchy of the data appearing in a report, and are used primarily to create breaks. Oracle Reports automatically creates a group for each query, but you are not limited to these defaults. Occasionally a new group is necessary. For example, if you want to create a break to group the data in the output, you can create a new group in the data model and include the column you want to use as the break column. In addition to break reports, you also create groups to define the cross products of matrix reports.
Columns : Columns contain the data values for a report. Default report columns corresponding to the table columns included in each query's SELECTlist are automatically created by Oracle Reports, 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 database column values, you can create new columns. You can also reassign one or more columns to a group or groups you've created.
Data Links : Data links are used to establish parent-child relationships between queries and groups via column-matching.
5. How many types of columns are there and what are they?
Formula Columns : For doing mathematical calculations and returning one value
Summary Columns : For doing summary calculations such as summations etc.
Place holder Columns : These columns are useful for storing the value in a variable
6. When I create a link between queries, does the link have to be an equi-join ?
No, Reports allows the links to be (=, < ,<= ,<> ,>, >=, LIKE, NOT LIKE).
7. What are the differences between lexical and bind parameters ?
Bind reference are used to replace the single value in SQL, PL/SQL statements a bind reference can be created using a (:) before a column or a parameter name. 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.
Eg:
SELECT ORDID,TOTAL
FROM ORD
WHERE CUSTID = :CUST
Lexical reference is placeholder for text that can be embedded in a sql statement. Created using ‘&’ before the column or parameter name. 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.
Eg:
SELECT ORDID, TOTAL
FROM &ATABLE
8. What is a 'Break Group' and how do you create one?
A break group is used to create a report in which an identical values in sequential rows is printed only once for its related records. (i.e. A break group divides a query's columns into sets.)
To create a break group:
a. In the data model editor, drag the group that contains the column at which you want to break your report down from the query object about 2 inches.
b. Select the column that you want to use to divide your report (e.g. if you want to group a list of employee data by department number, select the department number column) and drag it out of and above the group to create a new group.
c. Double-click on the title bar of the new group object to display its property sheet. Replace the name in the GENERAL:Name Field if you do not want to use the default. Fill in the rest of the property sheet as necessary and accept it.
9. What is the difference in using Group Filter vs Maximum Rows?
Maximum Rows in the Query property sheet restricts the actual number of records fetched by the query.
A Group Filter determines which records to be include or exclude, after all the records have been fetched by the query.
Maximum Rows actually restricts the amount of data retrieved, it is faster than a group filter in most cases. If you use a Filter of Last or Conditional, Reports must retrieve all of the records in the group before applying the filter.
Also, you should be aware that when using Maximum Rows for queries. It can effect summaries in other groups which depend on this query. For example, if you set Maximum Rows to 8 any summaries based on that query will only use the 8 records retrieved.
Layout Editor:
10. Explain different kinds of Reports?
To specify the report's layout, that is to position objects as you want them to appear in the report output. Oracle Reports provides 8 layout styles from which we can choose:
a. Tabular : Default Report, where column in a table are organized horizontally
b. Form Like : Similar to Tabular report, but columns are arranged vertically.
c. Mailing Label : Helps to address labels on envelopes
d. Form Letter : Database values embedded in boiler text
e. Group Left : Divides the rows of a table into ‘sets’ based on a common value in one of the columns. Used to restrict a column from repeating the same value several times while values of related column change.
f. Group Above : Contains two or more groups of data. For every value of the master group, the related values of the detail group are fetched from the database.
g. Matrix : Represents a grid, which relates both the row and the column, labels. The number of reports is not known until the rows are fetched from the database. To create a, matrix report atleast four group must be there.
h. Matrix with Group : Builds a matrix report with a group.
11. Can u have more than one layout in report
It is possible to have more than one layout in a report by using the additional layout option in the layout editor.
12. Explain Layout for a Report?
A report layout can contain any of the following layout objects :
a. Frames
b. Repeating Frames
c. Fields
d. Boilerplate
e. Anchors
Frames : Frames surround other layout objects, enabling you to control multiple objects simultaneously; e.g., ensuring that they maintain their positions relative to each other in the output.
Repeating Frames : Repeating frames act as placeholders for groups, and present rows of data retrieved from the database. Repeating frames repeat as often as the data is retrieved.
Fields : Fields act as placeholders for columns. 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.
Boilerplate : 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 are boilerplate, as well as text added to the layout, and Windows users can create OLE2 boilerplate to contain OLE2 objects.
Anchors : 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.
There are two types of anchors in Oracle Reports :
a. Implicit (anchors that Oracle Reports creates when a report is run)
b. Explicit (anchors you create)
13. What is the lock option in reports layout?
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?
Flex is the property of moving the related fields together by setting the flex property on
15. What is Button in Reports?
Buttons are layout objects on which users can click on the Previewer to do the following:
Display videos, sounds, or images that are fetched by a query.
Display videos, sounds, or images that are stored in files.
Execute PL/SQL to perform some action (such as launch a detail report for the current record in the Previewer).
There are two types of buttons:
Default Buttons : When you accept the Default Layout dialog box, Oracle Reports automatically generates buttons for video and sounds in the database.
User-Created Buttons : Create a button in the Layout editor by clicking on the Button tool, dragging a region, then specifying its properties in its property sheet.
16. What is OLE2 Objects in Reports?
OLE2 objects are Oracle Reports layout objects that display objects (e.g., spreadsheets, or drawings) from OLE2-aware applications. The OLE2 objects can be static or dynamic (i.e., displayed based on report values). Such objects are available only on platform supporting OLE2.
There are two types of OLE2 objects :
* Default
* User-created
17. Is there an easy way to have the Odd page numbers appear on the right side of the Report page and the Even Page numbers to appear on the left side of the Report page so when you put the pages together like a book they will appear on the correct side?
You have to create two fields, one on the right side, one on the left side. In the format trigger of each field, you test if it is an odd or an even page by using SRW.GET_PAGE_NUM. And then, you display or not the field.
18. Is it possible from the layout editor to see implicit anchors?
No, you will only ever see the explicit anchors created from the layout editor. There is however an option under Navigator Options to show Anchoring Information. If this option is chosen, then from the Object Navigator you can see all information on both implicit and explicit anchors.
Triggers / Timers:
19. Will the Before Report Trigger be the first trigger fired? It appears so from the order the triggers are listed in the Object Navigator?
No, the order in which the report level triggers fire is:
Before Parameter Form
After ParameterForm
Before Report
Between Pages
After Report
20. Can u run the report with out a Parameter Form?
Yes it is possible to run the report without parameter form by setting the PARAM value to Null
Program Units:
21. What are different types of PL/SQL Package in SRW?
Oracle Reports is shipped with a package, a collection of PL/SQL constructs--that contains many functions, procedures, and exceptions you can reference in any of your libraries or reports. The name of Oracle Reports' package is SRW. As a result, any time you reference a construct in the SRW package, you must prefix it with SRW, for example, SRW.DO_SQL.
The Oracle Reports Package contains the following:
SRW.BREAK SRW.RUN_REPORT_BATCHNO
SRW.CONTEXT_ FAILURE SRW.SET_FIELD_CHAR
SRW.DO_SQL SRW.SET_FIELD_DATE
SRW.DO_SQL_FAILURE SRW.SET_FIELD_NUM
SRW.GETERR_RUN SRW.SET_MAXROW
SRW.GET_PAGE_NUM SRW.TRACE_ADD_OPTION
SRW.INTEGER_ERROR SRW.TRACE_END
SRW.MAXROW_INERR SRW.TRACE_REM_OPTION
SRW.MAXROW_UNSET SRW.TRACE_START
SRW.MESSAGE SRW.TRUNCATED_VALUE
SRW.NULL_ARGUMENTS SRW.UNKNOWN_QUERY
SRW.PROGRAM_ABORT SRW.UNKNOWN_USER_EXIT
SRW.REFERENCE SRW.USER_EXIT
SRW.RUN_REPORT SRW.USER_EXIT20
SRW.SET_ATTR SRW.USER_EXIT_FAILURE
SRW.RUN_REPORT_FAILURE
No comments:
Post a Comment