Interview Questions and Answers 4

1. What are the components of a report?

LIVE PREVIEWER

DATA MODEL

              LAYOUT MODEL

              PARAMETER FORM

      2. What are the report triggers and the sequence of firing?

BEFORE PARAMETER FORM TRIGGER

AFTER PARAMETER FORM TRIGGER

BEFORE REPORT TRIGGER

BETWEEN PAGES TRIGGER

AFTER REPORT TRIGGER

3. How many types of report layouts are there?

TABULAR                             GROUP LEFT

     GROUP ABOVE                     FORM LIKE

MAILING LABEL                    MATRIX REPORT

     MULTIMEDIA                       OLE REPORT

4. What are the tools used in the report data model?

      SQLQUERY

      REFCURSOR QUERY

      EXPRESS QUERY

      SUMMARY COLUMN

      PLACE HOLDER COLUMN

      FORMULA COLUMN

5. What is a break report?

Establishes a breakpoint at the specified source line within a program unit.

Eg: -select empno, ename, job, sal, deptno from emp;

--Create one group

       --Select group left format

--Select the group field to break the report

6. What is an anchor?

Anchors are used to determine the vertical and horizontal positioning of child object relatively to the parent object.

The end of the anchor with a symbol on it is attached to the parent object.

Since the size of some layout objects may change when the report runs anchors are need to be defined to make the appearance of the object.

An anchor defines the relative position of an object to which it is anchored.

Anchor properties:

$  Child edge percentage to the child edge type

$  Child object name

$  Collapse horizontally

$  Collapse vertically

7. What is format trigger and conditional formatting?

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.

Usage Notes

           

                     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.

                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 appropriate output and the page will be      

included in the total number of pages.

8. What are group filter trigger and the properties of it?

                                A group filter is a PL/SQL function that determines which records to            

include in a group, if the Filter Type property is PL/SQL.  The function must return a       

boolean value (TRUE or FALSE).  Depending on whether the function returns TRUE or   

         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.

9. What is a matrix report and how many groups are required for a matrix report and what are they?

It is a dimensional report.

               It has minimum of 4 groups.

               Used for analysis.

               Report format is matrix or matrix with group.

        GROUP— row group

                  -- Column group

                   --Field

                   --Cross product

10. What is default parameter in a report? OR SYSTEM PARAMETERS

DESTYPE

DESNAME

DESFORMAT

BACK GROUND

COPIES

CURRENCY

DECIMAL

MODE

ORIENTATION

PRINT JOB

THOSANDS

     11. What are the systems defined parameters in the parameter form of a report 

builder?

DESTYPE

DESNAME

DESFORMAT

BACK GROUND

COPIES

CURRENCY

DECIMAL

MODE

ORIENTATION

PRINT JOB

THOSANDS

 

     12. What is the package used for running a report from the forms and define the

syntax of the package and the modes?

SRW. RUN_REPORT

13. What is a ref cursor query?

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:

         *More easily administer SQL

         *Avoid the use of lexical parameters in your reports

         *Share data sources with other applications, such as Form Builder

         *Increase control and security

         *Encapsulate logic within a subprogram

14. What is a data link?

A Data link relates the results of multiple queries.

A Data link causes the child query to be executed once for instance of its parent group

15. What is meant by user exits in the report?

When u want to pass control from report to a program u written, which performs some function and then returns the control to report builder.

16. How many types of user exits are there in a report and the uses of it?

Oracle pre-compiler user exits

Oracle call interface user exits (OCI)

Non-Oracle user exits

17. What is meant by SRW and what is its use in the report?

                         SRW—SERVER READ WRITE

                                                            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.

18. How do we perform SQl operations in the report?

19. What is meant by confine mode and flex 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.

20. Define the steps for generating a report manually?
21. What is the use of report triggers in the report?

USED TO ADD MORE FUNCTIONALITY TO THE REPORT

22. Describe some of the common SRW packages used in the report?

SRW.BREAK

SRW.RUN_REPORT

SRW.USER_EXIT

SRW.MESSAGE

SRW.REFERENCE

SRW.DO_SQL

23. When exactly before report trigger fires and explain what happens then?
24. How do we define flex fields in the report and what is the package used for that?

FND FLEXIDVAL/FND FLEXSQL

25. What is the use of between pages report trigger and how many times it will be fired for N number of pages in the report?

A FIRE BETWEEN EACH PAGE OF THE REPORT IS FORMATTED, EXCEPT THE VERY FIRST PAGE.

IT FIRES FOR (N-1) TIMES

26. How many types of columns are there and what are they in reports6i?

PLACE HOLDER COLUMN

                      SUMMARY COLUMN

                      FORMULA COLUMN

27. What is the classification of triggers present in report?

REPORT TRIGGERS--- BEFORE PARAMETER FORM TRIGGER

                                                    AFTER PARAMETER FORM TRIGGER

                                                    BEFORE REPORT TRIGGER

                                                    BETWEEN PAGES TRIGGER

                                                    AFTER REPORT TRIGGER

DATA TRIGGERS-------GROUP FILTER

                                                    REF CURSOR QUERY

                                                    FORMULA

                                                    VALIDATION TRIGGER

                 LAYOUT TRIGGERS-------FORMAT TRIGGER

                                                         ACTION TRIGGER

28. What is meant by bind parameter and lexical parameter and how they are defined in the report?

BIND PARAMETER:BIND PARAMETER IS USED TO REPEAT A SINGLE VALUE IN SQL/PLSQL STATEMENT. A BIND PARAMETER CAN BE CREATED USING A

COLON BEFORE THE COLON OR PARAMETER NAME

LEXICAL PARAMETER:  A Lexical parameter is a placeholder for text that can be embedded in a sql statement.

A lexical parameter can be created using an ampersand (&) before the column or parameter name.

29. What are the objects present in the report layout editor and their use?

Frame                                       Field

            Repeating frame                        Link file

            Text                                         Chart

            Free hand                                 Button

            OLE Object 

30. How to create conditional and unconditional report in a single report?
31. Describe the structure of matrix report and matrix report with group?
32. What is the restriction of lexical parameter?

SHOULD BE OF CHAR TYPE

33. What are the modules present in oracle reports?
34. What is the use of decode statement in the report query?

TO GET THE COLUMN CONSTANT

35. What is meant by boilerplate and what it consists of?
36. When the FND SRWINIT and FND SRWEXIT are called in the report and what is the purpose of calling them in the report?

FND SRWINIT sets your profile option values and allows oracle application

Object library user exit to detect that they have been called by an oracle reports program.

FND SRWINIT also allows your report to use the correct organization automatically.

FND SRWEXIT ensures that all the memory allocated for oracle object library user exits have been freed up properly.

37. What is the package used for running a report?

SRW.Run_Report

38. What is the default unit of measurement in report?

INCH

39. What are the global variables in reports?

40. What are bitmapped reports and how they are build?

Bitmapped reports are not text files and these are output as postscript files

The postscript is asset of instructions telling the printer exactly a landscape

Report, the postscript file must be generated as landscape

41. What is the purpose of frames and repeating frames in the report?

FRAME: Surrounds the objects and protect them from being over written or pushed by other objects.

REPEATING FRAME: Repeating frame surrounds all the fields that are created for group columns.

--The repeating frame prints once for each record of the group.

--For frames and repeating frames, the property elasticity defines whether the size of the   

frame or repeating frame should change with the objects inside of it at run time.

      42.Can we have more than one layout in report?

            --- Yes

43. Can we run the report without a parameter form in the report?

YES BY MAKING THE PARAMETER VALUE NULL

44. What is the difference between matrix break and nested matrix?
45. What is the option used for panel/print order property in report builder?

ACROSS UP/DOWN

46. Which script deletes report specific tables?

      ERASE

47. How do we debug reports?

SRW.DO_SQL

48. Why we get many pages of nonsense when we print the report?

The printer driver should recognize postscript code.

“enscript” program cannot be used for printing  

49. What is reference column error and describe it?

Cause:       The source column for the field is in a group below that of the repeating

frame, which encloses it. 

       Action:            Move the field into a repeating frame whose source group is at or above   

the group that contains the field's source column.  Otherwise, move the                    

field's source column into the enclosing repeating frame's group, if 

possible.

50. What is frequency error and describe it in detail?

Cause: The source column for the field is in a group below that of the                

repeating frame, which encloses it. 

                                                PART-2

In this tutorial you will learn about Introduction to Oracle Reports Builder, Report file storage formats, Oracle Reports Builder Tools, Report Wizard, Triggers in Reports, Types of Triggers and Case Study - Creating a Tabular report.

Introduction to Oracle Reports Builder

Oracle Reports Builder is a powerful enterprise reporting tool used to build reports that dynamically retrieve data from the database, format, display and print quality reports.Reports can be stored in File or Database (Report Builder Tables).

Report file storage formats

.rdf Report 
• Binary File Full report definition (includes source code and comments) 
• Modifiable through Builder. Binary, executable Portable if transferred as binary. 
• PL/SQL recompiles on Open/Run

.rep Report 
• Binary Run-Only File 
• No source code or comments. Not modifiable binary, executable. 
• Report Executables

RWBLD60 Report Builder 
RWRUN60 Report Runtime 
RWCON60 Report Converter/Compiler [File => Administration => Compile (rdf to rep)/Convert]

Oracle Reports Builder Tools

Oracle Reports Builder comes with the following components 
• Object Navigator 
• Property Palette 
• Data Model Editor 
• Layout Model Editor 
• Parameter Form Editor

Object Navigator 
The Object Navigator shows a hierarchical view of objects in the report. Each item listed is called a node and represents an object or type of object the report can contain or reference.

Property Palette 
A Property Palette is a window that displays the settings for defining an Oracle reportsobject.

Data Model Editor 
To specify data for a report, a data model should be defined. A data model is composed of some or all of the following data definition objects.

Queries 
Queries are SQL Select statements that fetch data from the oracle database. These statements are fired each time the report is run.

Groups 
Groups determine the hierarchy of data appearing in the report and are primarily used to group columns selected in the query. Oracle report automatically creates a group for each query.

Data Columns 
Data columns contain the data values for a report. Default data columns, corresponding to the table columns included in each query’s SELECT list are automatically created byoracle reports. Each column is placed in the group associated with the query that selected the column.

Formula Columns 
Formulas can be entered in formula columns to create computed columns. Formulas can be written using PL/SQL syntax. Formula columns are generally preceded by CF_ to distinguish from other columns.

Summary Columns 
Summary columns are used for calculating summary information like sum, average etc. This column uses a set of predefined oracle aggregate functions. Summary columns are generally preceded by CS_ to distinguish them from other columns.

Data Links 
Data links are used to establish parent-child relationships between queries and groups via column matching.

Layout Model Editor 
A report layout editor contains the following layout objects

Frames 
Frames surround other layout objects, enabling control of multiple objects simultaneously

Repeating Frames 
Repeating frames acts as placeholders for groups (I.e repeating values) and present rows of data retrieved from the database. Repeating frames repeat as often as the number of rows retrieved.

Fields 
Fields acts as placeholders for columns values. They define the formatting attributes for all columns displayed in the report.

Boilerplate 
Boilerplate consists of text (label of the column) and graphics that appear in a report each time it is run.

Parameter Form Editor 
Parameter form is a runtime form used to accept inputs from the user. 

Parameters 
Parameters are variables for a report that accept input from the user at runtime. These parameter values can then be used in the SQL select statements to retrieve data conditionally. Oracle reports creates a set of system parameters at runtime namely report destination type, number of copies etc.

Report Wizard

• When we create a default Tabular Report using report wizard, the wizard will take you through the below mentioned pages 
• Report Style Tabular, Form-Like, Mailing Label, Form Letter, Group Left, Group Above, Matrix, Matrix with Group 
• Query Type Choose whether to build a SQL query or an Express query. 
• Data Enter a SELECT statement to retrieve the report data 
• Displayed Fields Select the fields that you want to display in the output. 
• Fields to Total Select the fields that you want to summarize. 
• Labels for Fields Alter the labels that appear for each field and the width of each field.
• Template Select the template that you want to use for this report. A template contains standard information such as company logo, date, and so on.

Note: The above steps are different for each report style. 

Group Left & Have an additional page: ‘Groups’ 
Group Above styles 
Matrix Reports styles Have 3 additional pages: ‘Matrix Rows’ ‘Columns’ ‘Cells’ 
Mailing Label & Have 4 pages: ‘Report Style’ ‘Data’ 
Form Letter styles ‘Text’ ‘Template’

The difference between Mailing Labels and Form Letters is, Mailing Label shows multiple records on one page while Form Letter shows one record on each page.

Triggers in Reports

Types of Triggers

Formula Triggers: Formula triggers are PL/SQL functions that populate columns of type Formula.

Format Triggers: Format triggers are PL/SQL functions executed before the object is formatted. These triggers are used to dynamically change the formatting attributes and used to conditionally print and not to print a report column value. These triggers return Boolean values TRUE or FALSE. If the return value of the format trigger is FALSE, the value is not displayed.

Action Triggers: Action triggers are used to perform user-defined action. These triggers do not return any value.

Validation Triggers: Validation triggers are PL/SQL functions that are executed when a parameter value is entered and the cursor moves to the next parameter. These triggers return Boolean value TRUE / FALSE.

Report Triggers: Report triggers enable execution of PL/SQL functions at specific time during execution and formatting of report.

Before Parameter Form 
Fires before the Runtime Parameter Form are displayed. Can access the PL/SQL global variables, report level columns and manipulate accordingly.

After Parameter Form 
Fires after the Runtime Parameter form are displayed. Used to validate the parameter values. 

Before Report 
Fires before the report is executed but after the queries is parsed and date is fetched. 

Between Pages 
Fires before each page of the report are formatted, except the very first page. This page is used to customize page formatting. 

After Report 
Fires after the report previewer are exited, or after report output is sent to a specified destination.

Case Study - Create a Tabular report

After invoking the report builder and connecting to the database invoke Report wizard.

Click on Tools…Report Wizard… to start the report wizard for a new report. Report wizard shows the following tab pages to enter information required for report.

Style Totals Data 
Labels Fields Template

In the Style tab select ‘Tabular’ as the report style and Click Next

The Data tab allows creation of an SQL statement using Query Builder or to enter the SQL statement in the multi line edit box provided in the Data tab. 
Click Next

This will take you to the next tab if your SQL statement syntax is correct.

Fields tab is used to specify the fields that must be displayed in tabular format. Select all fields by clicking on >> icon. 
Click Next

The Totals tab is displayed that allows creation of Summary columns using aggregate functions. 
This report does not include totals for the selected fields and thus Click Next. 

The Labels tab is displayed that enable us to change the labels of the columns. 
Click Next 

The Templates tab is displayed that enable us to create report from templates. There are number of pre-determined templates available in Oracle Reports Builder.

Select Corporate 1 as the template. Click Finish

Click on File. Save to save the report, specify the report name and click OK.

Run the report!!