ORACLE APPS REPORTS INTERVIEW QUESTIONS
1.What is SRW Package?
Ans: The Report builder Built in package know as SRW Package (Sql Report Writer) This package extends reports, Control report execution, output message at runtime, Initialize layout fields, Perform DDL statements used to create or Drop temporary table, Call User Exit, to format width of the columns, to page break the column, to set the colors
Ex: SRW.DO_SQL, It’s like DDL command, we can create table, views , etc.,
SRW.SET_FIELD_NUM
SRW. SET_FIELD_CHAR
SRW. SET FIELD _DATE
2.What are Lexical Parameters and bind parameters?
Lexical Parameter is a Simple text string that to replace any part of a SELECT statement. Column names, the from clause, where clause or the order by clause. To create a lexical reference in a query we prefix the parameter name with an ampersand (ex. &.dname,)
3. What is User Parameters?
A parameter, which is created by user. For to restrict values with where clause in select statement.
Data type, width, input mask, initial value, validation trigger, list of values
We can use Lovs in use in user parameter with static and Dynamic Select Statement.
4. What is System Parameters: These are built-in parameters by corporation.
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., “,”).
5. How many Types of Reports available in Reports
Tabular
form-like
form – letter
Group left
Group above
matrix
Matrix with group
Mailing label
Matrix Report: Simple, Group above, Nested
Simple Matrix Report required 4 groups
1.Cross Product Group
2. Row and Column Group
3. Cell Group
4. Cell column is the source of a cross product summary that becomes the cell content.
Frames: 1.Repeating frame for rows (down direction)
2.Repeating frame for columns (Across)
3.Matrix object the intersection of the two repeating frames
6.What Types of Triggers are Available in Reports.
- Report level Triggers
- Data Model Triggers
- Layout Model Triggers
Report Level Triggers
Before parameter form:
If u want take parameters passed to the report and manipulate them so that they appear differently in the parameter form., this is where modification can be done for ex: when u want pass a deptno but show the dname selected , use a before parameter form trigger.
After parameter form & Before Report:
These two triggers are fired one after the other. No event occurs in between them. However the way the way that the reports product behaves when the triggers fail is quite different. If the After Parameter trigger fails the report will be put back into the parameter form. It’s useful to place code here to check whether values in your parameter form are valid. Even though the Before Report trigger is executed before the query runs, if this trigger fails it won’t fail until reports tries to display the first page of the report. This means that even if something goes wrong in the before report trigger (meaning that you may not want to run the query at all) It will run anyway
Between pages:
This Trigger fires before all pages except first page one. It will not fire after the last page of a report. If a report only has one page it will not fire at all. You can use this trigger to send specific control to the change the paper orientation or to do double sided printing
After report:
This trigger fires the report has printed or in the case of a screen report, after the report is closed following viewing. This trigger can be used to update a global variable if u r returning the number of pages in a report. It is also used to delete temporary table used to print the report
Data Model Triggers
Formula Column,
Group Filter,
Parameter values
Layout Model Triggers
7. What is Format triggers.
Format triggers enable you to modify the display of objects dynamically at run time or to suppress display altogether
For Headings, for repeating frames, for field, for boilerplate object
To format a column based on certain criteria for example
i) To format the max(Sal) for particular department.
ii) To format the Sal column with a Dollar($) prefix.
iii) To format Date formats….etc
8. What is Data Model?
Data Model is logically group of the Report Objects through query and Data model tools . Once query is compiled report automatically generates group. The queries build the groups ant then Groups are used to populate the report. The only function of queries in report is to create the groups. The Report Editor’s Data Model view enables you to define and modify the data model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships. To create the query objects for your data model, you can use the Report Wizard, Data Wizard, or the Query tools in the tool palette.
Layout Model is to Physically arrange Data model group objects on the Report. The Report Editor’s Layout Model view enables you to define and modify the layout model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships.
Ans: The Live Previewer is a work area in which you can preview your report and manipulate the actual, or live data at the same time. In the Live Previewer you can customize reports interactively, meaning that you can see the results immediately as you make each change.
To activate buttons in the Live Previewer, you must display the report output in the Runtime Previewer. In order to edit your report, such as changing column size ,move columns, align columns insert page numbers, edit text, change colors, change fonts set format masks, insert field the Live Previewer must be in Flex Mode.
Access
Title
Viewing region
Rulers
Grid
Toolbar
Style bar
Tool palette
Status bar
Ans: Parameters are variables for report that users can change at runtime immediately prior to the execution of the report. You can use system parameters to specify aspects of report execution, such as the output format, printer name , mailed or number of copies. We can also create own parameters through sql or Pl/sql at runtime.
The Parameter Form view is the work area in which you define the format of the report’s Runtime Parameter Form. To do this, you define and modify parameter form objects (fields and boilerplate).
When you run a report, Report Builder uses the Parameter Form view as a template for the Runtime Parameter Form. Fields and boilerplate appear in the Runtime Parameter Form exactly as they appear in the Parameter Form view. If you do not define a Runtime Parameter Form in the Parameter Form view, Report Builder displays a default Parameter Form for you at runtime.
The first thing in data model is the query. Through query we access database objects with sql query. Compiled query creates groups. We can create query through query builder, sql query and import query from o/s file or database.
13. What is Group?
Ans: Groups are created to organize the columns in your report. When you create a query, Report Builder automatically creates a group that contains the columns selected by the query. You create additional groups to produce break levels in the report, either manually or by using the Report Wizard to create a group above or group left report.
14 What is Repeating Frame?
Ans: Repeating frames surround all of the fields that are created for a group’s columns.
Repeating frames correspond to groups in the data model. Each repeating frame must to be associated with a group of data model The repeating frame prints (is fired) once for each record of the group.
15. What is Reference Cursor?
A ref cursor query uses PL/SQL to fetch data. Each ref cursor query is associated with a PL/SQL function that returns a strongly typed ref cursor. The function must ensure that the ref cursor is opened and associated with a SELECT statement that has a SELECT list that matches the type of the ref cursor.
You base a query on a ref cursor when you want to:
n more easily administer SQL
n avoid the use of lexical parameters in your reports
n share datasources with other applications, such as Form Builder
n increase control and security
n encapsulate logic within a subprogram
Furthermore, if you use a stored program unit to implement ref cursors, you receive the added benefits that go along with storing your program units in the Oracle database.
16. What is Template?
Ans: Templates define common characteristics and objects that you want to apply to multiple reports. For example, you can define a template that includes the company logo and sets fonts and colors for selected areas of a report. And properties of the objects also
Creation of Template: In Report editor , open a existing Template or Create a new Template and save it concerned directory. Then Edit CAGPREFS.ORA File , and Specify which type of Template are u going to develop.
Ex. Tabular, form, matrix Then give your developed template *.tdf file name.
Develop Report with Newly developed Template.
17 what is Flex mode and Confine mode?
Confine mode
On: child objects cannot be moved outside their enclosing parent objects.
Off: child objects can be moved outside their enclosing parent objects.
Flex mode:
On: parent borders “stretch” when child objects are moved against them.
Off: parent borders remain fixed when child objects are moved against them.
Ans: To limit the records per page.
Ans: The Page Protect property indicates whether to try to keep the entire object and its contents on the same logical page. Setting Page Protect to Yes means that if the contents of the object cannot fit on the current logical page, the object and all of its contents will be moved to the next logical page. Ex: if you set yes, the object information print another page.
The print condition type First, All, All but first, Last, All but last refer to the frequency with which you want to appear based upon the setting of the print condition object. A print condition object of Enclosing Object is whichever object encloses the current object (could be the parent or a frame within the parent), while Anchoring Object is the parent object (unless you have explicitly anchored the object in which case it is the object to which it is anchored). The key here is that this is about the pages on which the Print Condition Object appears, not the current object. Oracle views First as the first page on which any part of the Print Condition Object is printed, likewise Last is the last page on which any part of the Print Condition Object is printed. For objects inside a repeating frame, this condition is re-evaluated for each instance of the frame.
Ans: The Print Direction property is the direction in which successive instances of the repeating frame appear.
21 What is Vertical and Horizental Elacity
Ans: The Horizontal Elasticity property is how the horizontal size of the object will change at runtime to accommodate the objects or data within it:
Ans: A placeholder is a column is an empty container at design time. The placeholder can hold a value at run time has been calculated and placed in to It by pl/sql code from anther object. You can set the value of a placeholder column is in a Before Report trigger , A report level formula column(if the place holder column is at report level) A formula column in the place holder group or a group below it
Uses of place holder columns enables u to populate multiple columns from one piece of code. U can calculate several values in one block of pl/sql code in a formula column and assign each value into a different placeholder column. U therefore create and maintain only program unit instead of many.
Store a Temporary value for future reference. EX. Store the current max salary as records are retrieved.
23 What is Formula Column?
Ans: A formula column performs a user-defined computation on another column(s) data, including placeholder columns.
Ans: A summary column performs a computation on another column’s data. Using the Report Wizard or Data Wizard, you can create the following summaries: sum, average, count, minimum, maximum, % total. You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional summaries: first, last, standard deviation, variance.
Ans: Boilerplate is any text or graphics that appear in a report every time it is run. Report Builder will create one boilerplate object for each label selected in the Report Wizard (it is named B_
Column name). Also, one boilerplate object is sometimes created for each report summary. A boilerplate object is owned by the object surrounding it, unless otherwise noted.
26 What is Data Link
When we join multiple quires in a report the join condition is stored in the data link section
Data links relate the results of multiple queries. A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group. When you create a data link in the Data Model view of your report, Report Builder constructs a clause (as specified in the link’s Property Palette) that will be added to the child query’s SELECT statement at runtime. You can view the SELECT statements for the individual parent and child queries in the Builder, but can not view the SELECT statement that includes the clause created by the data link you define.
27 What is filter and Group Filter
28.What is Query Builder
Ans: it’s a gui tool to build a query in Report Wizard, Data Wizard or Data model.
Ans: We can break a column through data model , it Display once for a group
Ans: RUN_PRODUCT and RUN_REPORT_OBJECT
40. HOW CAN U CREATE TWO FORMATS
USING DISTRIBUTION WE CAN CREATE DIFFERENT FORMATS
45 HOW TO DISPLY ONE RECORD PER PAGE ( WHICH PROPERTY WE SHOULD SET)
Set Repeating Frame Properties : Maximum records per page=1 And it will override group filter property.
In Data model Layout , Group Property Through Filter Type & No of records to display
Property, Values are First, last, pl/sql
47. What is Header ,Body, Trailer, and Footer in Reports
Header: The header consist of on e or more pages that are printed before report proper. The type of
Information you might want to print title of the page ,company logo and address or chart the
Summarizes the report.
Trailer: The trailer consists of one or more pages that print after the report itself, usually used for nothing more than an end of report blank page, but also used for a report summary or chart.
Body: The body is where all the main report objects are placed
Margin: the report layout only governs the part of the pages designated for the main data portion of the report. The margins are can be used to specify page headers and page footers.
49. what are Executable file definitions in Reports
Report Builder (RWBLD60.EXE)
n Reports Runtime (RWRUN60.EXE)
n Reports Convert (RWCON60.EXE)
n Reports Background Engine (RWRBE60.EXE)
n Reports Server (RWMTS60.EXE)
n Reports Web Cartridge (RWOWS60.DLL)
n Reports CGI (RWCGI60.EXE)
n Reports Queue Manager (RWRQM60.EXE)
n Reports Launcher (RWSXC60.EXE)
n Reports ActiveX Control (RWSXA60.OCX)
Aggregated Information, Calculated information, A string Function
Can I highlight and change all the format masks and print conditions of a bunch of fields all at once?
You can. If you highlight a bunch of objects and then right click and select “properties..”, Oracle gives you a stacked set of the individual properties forms for each of the selected objects. While this may be useful for some things, it requires changing values individually for each object. However, instead you can select the group of fields and then select “Common properties” from the “Tools” menu which will allow you to set the format mask , print conditions etc. for the whole set of objects at once.
How do I change the printed value of a field at runtime?
Triggers are intended to simply provide a true or false return value to determine whether an object should be printed. It is generally not allowed to change any values held in the cursor, make changes to the database, or change the value of it’s objects value.
That being said, there is a highly unpublicized method of doing just that using the SRW.Set_Field_Char procedure.
The syntax is SRW.Set_Field_char (0,) and the output of the object that the current trigger is attached to will be replaced by .
There are also SRW.set_fileld_num and SRW.set_field_date for numeric or date fields.
While these options do work, they should only be used if a suitable NVL or DECODE statement in the original query is not possible as they are much, much slower to run. Also, note that this change of value only applies to the formatted output. It does not change the value held in the cursor and so can not be used for evaluating summary totals
While these options do work, they should only be used if a suitable NVL or DECODE statement in the original query is not possible as they are much, much slower to run. Also, note that this change of value only applies to the formatted output. It does not change the value held in the cursor and so can not be used for evaluating summary totals
The capability of producing multiple copies of a given report or portion of it in different output formats is referred to as report bursting.
Additional layout created for to different format using same query and groups without modifying default layout created by report wizard., we can use both layouts according to user requirement.
Ans: Current date, Page Number, Panel number, Physical Page Number, Total Pages,
Total Panels, Total Physical Pages.
Link File : Is a special type of boilerplate, that doesn’t have to remain constant for each report run
The type of file contents, can be Text, Image, CGM, Oracle drawing format, or image URL
Source filename :the name of the file the u want link to the report Through import Image from
No comments:
Post a Comment