Reports 6i INTERVIEW QS?

Reports 6i

 INTERVIEW QS? 

1.What are the various types of reports ?
* Tabular, Master Detail, Form, Form Letter, Mailing Labels, Matrix

2. What is the difference between Master - Detail Report and report created by breaks ?
* Master/detail data models are very similar to break report data models. However, a master/detail data model is created using two queries, each of which owns at least one group, and a data link. A break report data model is created using one query and at least two groups. While reports based on a single query are usually more efficient than reports based on multiple queries, sometimes the structure of your data tables may require you to link multiple tables.

3. What are Anchors ?
* An anchor defines the relative position of an object to the object to which it is anchored. Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent. Since the size of some layout objects may change when the report runs (and data is actually fetched), you need anchors to define where you want objects to appear relative to one another.

4. What are the various types of anchors in Reports ?
A There are two types of anchors in Oracle Reports:
* implicit (anchors that Oracle Reports creates when a report is run)
* explicit (anchors you create)
Implicit Anchors : At runtime, Oracle Reports generates an implicit anchor for each layout object that does not already have an explicit anchor. It determines for each layout object which objects, if any, can overwrite it, then creates an anchor from the layout object to the closest object that can overwrite it. This prevents the object from being overwritten. The implicit anchor functionality saves you from having to define the positioning of each object. Implicit anchors are not visible in the Layout editor. However, you can specify that the Object Navigator display anchoring information using the Object Navigator Options dialog.
Explicit Anchors : Create an anchor in the Layout editor by clicking on the Anchor tool, dragging from one edge of the child to the one of the parent's edges, then specifying the anchor's properties in its property sheet. Any anchor you create for an object will override its implicit anchoring. Explicit anchors are always visible in the Layout editor unless you specify otherwise via the Layout Options dialog

9. What are the various report triggers ? What is their order of firing ?
A There are eight report triggers. Of these there are five global triggers called the Report Triggers. They are fired in the following order :
* Before Parameter Form
* After Parameter Form
* Before Report
* Between Pages
* After Report
Apart from the above Five Report Triggers, there are three other types of triggers :
* Validation Triggers
* Format Triggers
* Action Triggers
Before 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. (Note : If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters).
After 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. (Note : If the Runtime Parameter Form is suppressed, the After Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data).

Before Report : Fires before the report is executed but after queries are parsed and data is fetched.
Between Pages : Fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. (Note : In the Previewer, this trigger only fires the first time that you go to a page. If you subsequently return to the page, the trigger does not fire again.)
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*Mail 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.
Validation Triggers : Validation Triggers 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 Triggers are also used to validate the Initial Value of the parameter in the Parameter property sheet.
Format Triggers : 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.
Action Triggers : Action Triggers are PL/SQL procedures executed when a button is selected in the Previewer. The trigger can be used to dynamically call another report (drill down) or execute any other PL/SQL.

19. What are Placeholder Columns ?
A A placeholder is a "dummy" column for which you can conditionally set the datatype and value via PL/SQL or a user exit. Placeholder columns are useful when you want to selectively populate a column with a value (e.g., each time the nth record is fetched, or each time a record is fetched containing a specific value, etc.).

20. What are the various Module Types in Reports ?
A You can build three types of modules with Oracle Reports:
* external queries, which are ANSI-standard SQL SELECT statements that can be referenced by modules
* external PL/SQL libraries, which are collections of PL/SQL source code that can be referenced by modules
* reports, which are collections of report-level objects and references to external queries and PL/SQL libraries (optional) that can be referenced by modules

22. What are Physical and Logical pages in Reports ?
A A report page can have any length and any width. Because printer pages may be smaller or larger than your report's "page," the concept of physical and logical pages is used.
Physical Page : A physical page (or panel) is the size of a page that will be output by your printer.
Logical Page : A logical page is the size of one page of your actual report; one logical page may be made up of multiple physical pages. The Previewer displays the logical pages of your report output, one at a time.

23. What are the various page layout sections in Oracle Reports ?
A A report has three sections : the report header pages, report body/margin pages, and report trailer pages.

27. What are various types of parameters ?
A There are two types of parameters:
* default (called system parameters)
* user-created (called bind and lexical parameters)

30. How do you reference parameters and columns in reports ?
A In two ways :
* As bind references
* As lexical references

31. What are Bind Referencing and Lexical Referencing ?
* Bind Referencing : Bind references are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Use bind reference when you want the parameter to substitute only one value at runtime. 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.

Lexical Referencing : Lexical references are placeholders for text that you embed in a SELECT statement. Use Lexical reference when you want the parameter to substitute multiple values at runtime. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.

33. Can you create a group without any break columns ?
* No.

35. Types of Matrix report ?
* 1. Single query 2. Multi query 3. Nested Query 4. Matrix Break

37. In Reports, how can you print one record per page in the output ?
* Set the Maximum records per page property of the Repeating frame to 1.

38. How do you print a Report 2.5 report in character mode ?
* Set MODE = 'Character' in the Parameter form

39. What are widow lines ?
* Widow lines are the minimum number of lines of the boilerplate text or field that should appear on the logical page where the text starts to print. If the number of lines specified for this property cannot fit on the logical page, then all lines of the boilerplate are moved to the next page.

40. What are widow records ?
* Widow records are the minimum number of instances (records) that should appear on the logical page where the repeating frame starts to print. If the number of instances specified for this property cannot fit on the logical page where the repeating frame is initially triggered to print, then the repeating frame will start formatting on the next page.

41. What is 'page protect' property for objects ?
* Page protect property for an object indicates whether to try to keep the entire object and its contents on the same logical page. Checking Page Protect 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.

42. What is the 'Print Condition Type' property ?
* 'Print Condition Type' property specifies the frequency with which you want the object to appear in the report. The Print Condition Type options indicate the logical page(s) on which the object should be triggered to print with regard to the Print Condition Object.

44. What are the various values of the 'Print Condition Type' property in Reports ?
* The various values are :
All : All means the object and all of its contents will be printed on all logical pages of the Print Condition Object. The object will be repeated on any overflow pages of the Print Condition Object and will be truncated at the logical page boundary, if necessary.
All but First : All but First means the object and all of its contents will be printed on all logical pages of the Print Condition Object except the first logical page. The object will be formatted only on overflow pages of the Print Condition Object and will be truncated at the logical page boundary, if necessary.
All but Last : All but Last means the object and all of its contents will be printed on all logical pages of the Print Condition Object except the last logical page. The object will be repeated on any overflow pages of the Print Condition Object except the last one and will be truncated at the logical page boundary, if necessary.
*Default : Default means that Oracle Reports will use object positioning to set the Print Condition Type to either *First or *Last for you. (The asterisk indicates that Oracle Reports specified the setting for you.)
First : First means that the object and all of its contents will only be printed on the first logical page of the Print Condition Object. The object will be formatted and will overflow to subsequent pages, if necessary.
Last : Last means that the object and all of its contents will only be printed on the last logical page of the Print Condition Object. The object will be formatted after the Print Condition Object and will overflow to subsequent pages, if necessary.
* For repeating frames, the print condition type property refers to every logical page of every instance (record) of the repeating frame. If you specify a Print Condition Type setting of All and a Print Condition Object setting of Enclosing Object for a field F_1, it prints in every instance of repeating frame R_1 on every logical page. If you specify a Print Condition Type setting of All but First in the above case , then the field will be printed only in those cases where a single instance (row or record) of the repeating frame spans across more than one page (which is normally not the case since many records are there in a single logical page and not one record in many pages). In such a case the field will be printed on all subsequent pages of this instance except the first page.

43. What is the 'Print Condition Object' property ?
* 'Print Condition Object' property specifies the object on which to base the Print Condition Type of the current object. For example, if you specify a Print Condition Type of All and a Print Condition Object of Anchoring Object, the current object will be triggered to print on every logical page on which its anchoring object (parent object) appears.

45. What are the various values of the 'Print Condition Object' property in Reports ?
* The various values are :
Anchoring Object : Anchoring Object is the parent object to which the current object is implicitly or explicitly anchored.
Enclosing Object : Enclosing Object is the object that encloses the current object.

46. What is the horizontal of vertical sizing property of objects ?
* Horizontal of vertical sizing property specifies how the horizontal or vertical size of the object may change at runtime to accommodate the objects or data within it.

47. What are the various values of the horizontal of vertical sizing property ?
* The various values are :
Contract : Contract means the vertical size of the object decreases, if the formatted objects or data within it are short enough, but it cannot increase to a height greater than that shown in the editor. Note : Truncation of data may occur. (You can think of this option as meaning "only contract, do not expand.")
Expand : Expand means the vertical size of the object increases, if the formatted objects or data within it are tall enough, but it cannot decrease to a height less than that shown in the editor. (You can think of this option as meaning "only expand, do not contract.")
Fixed : Fixed means the height of the object is the same on each logical page, regardless of the size of the objects or data within it. Note : Truncation of data may occur. The height of the object is defined to be its height in the editor.
Variable : Variable means the object may expand or contract vertically to accommodate the objects or data within it (with no extra space), which means the height shown in the editor has no effect on the object's height at runtime.

51. How do you display a message in reports ?
*

SRW.Message

54. What are the various values of 'Print Panel Order' property of report ?
* The various values are :
Across/Down : Across/Down means the physical pages of the report body will print left-to-right then top-to-bottom.
Down/Across : Down/Across means the physical pages of the report body will print top-to-bottom and then left-to-right.

55. What is the 'Print Direction' Property of Repeating frames ?
* 'Print Direction' Property specifies the direction in which successive instances of the repeating frame appear.

56. What are the various values of the 'Print Direction' Property of Repeating frames ?
* The various values are :
Across : Across means that each instance of the repeating frame subsequent to the first instance is printed to the right of the previous instance across the logical page.
Across/Down : Across/Down means that each instance of the repeating frame subsequent to the first instance is printed to the right of the previous instance until an entire instance cannot fit between the previous instance and the right margin of the logical page. At that time, Oracle Reports prints the instance below the left-most instance on the logical page, provided there is enough vertical space left on the logical page for the instance to print completely.
Down : Down means that each instance of the repeating frame subsequent to the first instance is printed below the previous instance down the logical page.
Down/Across : Down/Across means that each instance of the repeating frame subsequent to the first instance is printed below the previous instance until an entire instance cannot fit inside the bottom margin of the logical page. At that time, Oracle Reports prints the instance to the right of the topmost instance on the logical page, provided there is enough horizontal space left on the logical page for the instance to print completely.

59. What is the 'Keep with Anchoring Object' object property ?
* 'Keep with Anchoring Object' object property indicates whether to keep an object and the object to which it is anchored on the same logical page. Checking Keep with Anchoring Object means that if the object, its anchoring object, or both cannot fit on the logical page, they will be moved to the next logical page.

60. What is 'Page Break Before' object property ?
* 'Page Break Before' object property indicates that you want the object to be formatted on the page after the page on which it is initially triggered to print. Note that this does not necessarily mean that all the objects below the object with Page Break Before will move to the next page.

61. What is 'Page Break After' object property ?
* 'Page Break After' object property indicates that you want all children of the object to be moved to the next page. In other words, any object that is a child object of an anchor (implicit or explicit) to this object will be treated as if it has Page Break Before set. Note that this does not necessarily mean that all the objects below the object with Page Break After will move to the next page.

63. What is the 'Break Order' property of columns ?
* The 'Break Order' property is the order in which to display the column's values. This property applies only to columns that identify distinct values of user-created groups (i.e., break groups). The order of column values in a default group is determined by the ORDER BY clause of the query. For column values in user-created groups, however, you must use Break Order to specify how to order the break column's values.

64. What are the various types of links ?
* The Data Link tool draws a link between a parent group and a child query. Creating a link is a drag and drop operation. Clicking and dragging from one column to another creates a link between those two columns (column to column link). Clicking and dragging from one query to another creates all possible links between columns selected by the queries based on database constraints (query to query link). Clicking and dragging between two groups creates a group-to-group link (i.e., a link with no columns).
65. Name some of the procedures in the SRW package ?
* SRW.Message, SRW.User_Exit, SRW.Do_Sql, SRW.Run_Report

66. What are the various report layout regions ?
* There are three report regions in the Layout editor :
* header
* body/margin
* trailer
Header : The report header pages appear once at the beginning of each report on a set of separate pages. They can contain text, graphics, data, and computations.
Body/Margin : The body/margin pages appear between the header and trailer pages, and are the bulk of the report. Each physical page in this section consists of a body and a margin. The body contains the majority of the report's text, graphics, data, and computations.
A top and bottom margin appear on each page, until all data within the body has been formatted. A margin may include text, graphics, page numbers, page totals, and grand totals. The default margin size is one half inch each for the top and bottom margins and zero for the left and right margins.

Trailer : The report trailer pages appear once at the end of each report on a set of separate pages. They can contain text, graphics, data, and computations.

70. What is more efficient : Maximum rows or Group Filter ?
* Maximum Rows in the Query property sheet restricts the number of records fetched by the query. A group filter determines which records to include and which records to exclude. Since Maximum Rows actually restricts the amount of data retrieved, it is faster than a group filter in most cases. If you are using a Filter of Last or Conditional, Oracle Reports must retrieve all of the records in the group before applying the filter criteria. As a result, Maximum Rows or a Filter of First is faster.

/********************************************************************************************************/

Qns: How will you developed reports from scratch to Registrations.

Ans:

STEP1: Write Query according to Specs.

STEP2: Add User Parameter in Query.

STEP3: Design Data Model.

STEP4: Design Layout according to specs.

STEP5: Defined Mandatory Parameter P_CONC_REQUEST_ID in report.

STEP6: Use User exit in reports.

STEP7: FTP the report the appropriate top in reports/US folders.

STEP8: registered reports in Oracle Apps (Note Explain all steps of apps registrations)

Qns: What is Difference between D2k and APPS Reports?

Ans:

D2K

Apps Reports

No User Exit

User Exit

P_Conc_Request_id not Required

P_CONC_REQUEST_ID Mandatory Parameter

Qns: What is Format Trigger?

Ans: Format trigger is used to print conditionally printing of Layout. Format trigger return Boolean values.

Qns: What do you mean by Anchoring in Reports?

Ans : With the help of Anchoring Relative position of two objects will be fixed.

Qns: What are Reports Trigger and Their Firing Sequence?

Ans:

• 
  
  BEFORE PARAMETER FORM
• 
  
  AFTER PARAMETER FORM
• 
  
  BEFORE REPORT
• 
  
  BETWEEN PAGES
• 
  
  AFTER REPORT

Qns: What do you mean by Confine Mode in Reports?

Ans : Confine Mode Means Locking of Frames. If Confine Mode is on then you can not move the Object outside the Frame. If it is off you can move the objects.

Qns: Whats is Difference between Formula Column, Summary and Placeholder Column.

Ans:

• 
  
  Formula Column: Formula Column is used to compute Values. Formula Column always returns the Values.
• 
  
  Summary Column: Summary Column is used for Calculating summary function like Sum, Max. Min, Count.
• 
  
  Placeholder Columns: Placeholder Column is used to place the Variables.

Qns: Whats is Difference between and Lexical and Bind Parameter.

Ans:

Bind Parameter

Lexical Parameter

With the help of Bind Parameter You Can Pass Values.

With the help of Lexical Parameter You can pass string. You can pass lexical parameter in Select, From , Where Clause

Denoted by Colon

Denoted by Ampersand(&) sign

Qns: How will you Print Conditionally Layout in reports.

Ans: With the help of Format trigger.

For example code like:

IF :P_TYPE

Qns: How will you call Concurrent Program through Reports.

Qns: Whats are User Exit in Reports?

Qns: In Which Folder You will put the Report on the Server?

Qns : How will you Register the Reports?

Qns : What do you Mean by Token in Reports.

Qns : What are Various Default Type in Concurrent Programs?

Qns : What do you incompatibility in Concurrent Programs?

Qns: How will you Capture ORG_ID Dynamically in Concurrent Programs.

Qns : Suppose I have two Parameter Start Date and End Date and I want My Start Date should be lower then End Date. How will you Validate.

Qns : What are Various Execution Method in Concurrent Programs in executables.

Qns : What is Difference between Application Name Defined in Concurrent Program Executables and Concurrent Programs.

Qns : Whats is Mandatory Parameter to Register PL/SQL Procedure in Apps?

Qns: How will you Call Reports with in Reports?

Qns: What do you Mean by Incompatibility in Reports?

Qns : What is significance of Mandatory Parameter in P_CONC_REQUEST_ID?

Qns: How will you put Message in Log File?

Qns : How will you put Message of Reports in LOG Files.

/****************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************/

Oracle Report Builder is an enterprise-reporting tool that retrieves data dynamically from database, formats the report, display the final report and can also help with printing quality reports.

All the reports created using Report Builder in Oracle are stored in Report Builder Tables in ‘*.rdf’ and ‘*.rep’ format. Reports stored in ‘*.rdf’ format have full report definition of binary file. It also includes some comments and source code. You can modify these reports by use of Report Builder. If you transfer these reports on a secondary storage media in binary format then act as executable portable files. However, ‘*.rep’ format files are binary run-only files that do not contain any comments or source code. These files cannot be modified using Report Builder also.

Various tools used in Oracle Report Builder are

• 
  
  Object Navigator,
• 
  
  Data Model Editor,
• 
  
  Parameter Form Editor,
• 
  
  Property Palette and
• 
  
  Layout Model Editor.

With the help of Object Navigator, you can view the hierarchical view of all the objects that are displayed in the report. Data Model Editor is used to define data model of a report that contains various data definition objects. Parameter Form Editor is used to accept user inputs. Property Palette can be used to view or change settings of Oracle report object. Layout Model Editor contains various types of layout objects that help you to choose the type of layout you want for your report.

Best feature of Report Builder in Oracle is its wizard to create reports. Just follow these simple steps to create a report:

1. Start Report Builder.

2. Open Report Wizard in report builder. A new window would appear.

3. Choose the style of report from list of options, namely, Tabular, Mailing Label, Group Above, Group Left, Form Letter, Form and Matrix.

4. Choose the type of query you want to execute, that is, ‘SQL Query’ or ‘Express Query’

5. Enter a SELECT statement for data retrieval from the database for report data.

6. A list of fields would appear from which you can select some or all of the fields to be displayed in the report.

7. Then a new page summarizing the selected fields would appear. If you want to add or remove the selected fields, then click on the Back button or else click on the Next button.

8. You can alter the name of the field labels that would appear in the final report and click on Next button.

9. If you have a template for your company or organization then you can insert it at this phase. Click on the Next button once you have selected the template.

10. Click on the Finish button and the report would be generated.

You may also include from list of available triggers such as formula, format, action, validation, report, before parameter form, after parameter form, before report, after report and between pages trigger. These triggers fire whenever their corresponding events occur and are very helpful in achieving dynamic effect for generating reports.

/*********************************************************************************************************/

Oracle Reports 6i

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 reports object.

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 by oracle 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 

span>

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!!

/*****************************************************************************/

 

I switched the page size to 11x8.5, but the printer still prints in portrait

Even though you set the page size in the report properties, there is a another variable in the system parameters section under the data model in the object navigator called orientation. This sets the printer orientation. Oracle starts by setting it to "default" which means that no matter how you set the page size, the user's default printer setup will be used. You can also set it to either "Landscape" or "Portrait" to force the printer orientation no matter what the user has set as default. These sorts of picky, minor details are the ones which are invariably forgotten when you are designing your report and are the reason I created our two report templates, reptmp_p and reptmp_l (portrait and landscape). For anyone who wants a consistent look in their reports I strongly recommend building a similar pair to save yourself an ulcer, unless you actually like starting from scratch every time!?!

[edit] I've moved field into a repeating frame, but still get a "frequency below it's group" error

Moving fields around does not change what enclosing object is considered it's parent group. Oracle carefully remembers what repeating frame a field was originally placed in and assigns that as it's parent. If you then reference a column further down the line of the query structure it will return that error. If you are not exactly sure which repeating frame a field belongs to, try dragging it out of all of them. Whichever frame will not allow it to escape is it's parent. To change a field's parent, first click on the lock button on the speedbutton bar. It should now look like an unlocked padlock. Now all of the fields on the layout can be repositioned regardless of their original parent items. When you are satisfied with the repositioning click the lock button again to lock the layout. Oracle will parse the layout and assumes that any item fully enclosed in a repeating frame is a child object of that frame. This can be confirmed again by trying to drag an object out of it's parent. (Cntrl - Z or edit..undo will put it back where it came from)

Sometimes, for unknown and mysterious reasons, this method does not work. The alternative in this case is to highlight the field (or fields), cut it (cntrl-x), and then paste it into the desired frame. The paste does not initially set it into the right frame, but if you drag and drop it there before clicking on any other objects, and then click on something else, Oracle will usually figure what your intent was and assign the object(s) as a child of that frame. This is my preferred method of changing a field's parent as it works much more consistently then the unlock/lock method. One note though, if you are reassigning a group of fields, make sure the frame you are going to move them into is large enough to accept the whole group at once before you do the cut/paste. If you do the paste and then try to grow the frame to fit, you will have to cut and paste again. Once you de-select an object that has just been pasted, Oracle will assign it as a child of whatever it is in at the time.

If this technique also fails, you are probably going to have to delete and then recreate the objects within the desired frame. If the object has triggers attached, save yourself some typing by creating the new object in the right frame, copying over the trigger code, and then deleting the old object.

[edit] Why does part of a row sometimes get shifted to the next page, but not all of it?

This is due to the way the scan works when Oracle is parsing the layout. If the tops of all the fields in a row are aligned and the fields are all of the same height and font, they should all stay together. I suspect, however, that Reports bases it's decision on the printed size rather than the field size you define to determine which objects are too large and must be shifted to the next page. This means that even if you set two fields top-aligned with the same height and font but one of them is bolded, the bolded field could get shifted to the next page due to it's bigger footprint. The solution is to put the whole row into a regular frame which is page protected.

[edit] What does the "Print Condition" do?

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.

As an example, assume we have created a field inside a repeating frame with Print Condition Object set to 'anchoring object', and Print Condition Type set to 'All But First'. On every instance of that repeating frame which is printed entirely within a single page, our object will not print. However, if an instance of that frame spans more than one page then our object will print on the second and every subsequent page that this instance of the repeating frame spans.

For most objects you will not have to play with this print condition setting as the default setting is pretty good at determining what pages to print on, even though it only chooses between 'first' and 'last'. Only such things as heading objects you want reprinted on multiple pages are normally candidates for fooling around with this setting.

[edit] Create dynamic 'where' which the user can input on the parameter form for my select statement

While setting a simple parameter for use in defining the select statement, such as a date, bill_period_id etc. is simple, there are times when you may wish to allow a user to add any "where" statement they wish. However, if you create a varchar user variable and try to reference it as an SQL condition ( e.g. Select * from account where :usercondition) you will get an error. The secret is that the variable must be initialized to a valid SQL condition before the Data Model will accept it. This is done in the "Initial Value" spot on the variables' properties form. The usual default is "1 = 1" which simply means all rows meeting whatever other conditions are included in the select statement will pass this condition if the user does not change it in the parameter form.

[edit] How do I change a user parameter at runtime from a layout object trigger?

Quite simply, you can't. Once the BeforeReport trigger has fired, Reports locks down the user parameters until the report is finished. Oh, I know you can put a statement into a layout trigger at design time and the compiler will accept it, but the moment you run the report you will get a nasty error and the report will die. Why they couldn't catch those problems at compile time I have no idea, except that it probably uses the same PL/SQL compiler as Forms which uses that same syntax for the perfectly acceptable function of changing field values.

That being said, there is valid technique to mimic having a user variable which can be changed over the course of the report execution. What you have to do is create a PL/SQL package that contains a variable as well as the functions to read and write to that variable. Since variables inside a package are both local to that package and persistent over the duration of the run, you use this to save and change your variable value. I know that this seems like overkill, but it is the most efficient way of handling an issue that is very rarely encountered. As you can probably guess, this technique is a last resort to finding an SQL work around if one exists.

[edit] How do I set the initial values of parameters for the parameter form at runtime?

This is what the BeforeForm trigger is primarily used for. Even if you have used a select statement to create a lookup list for the parameter, this statement is fully parsed before the parameter form is opened. Simply setting the parameter to a given value in the BeforeForm trigger will select that option as the default value displayed to the user. For example, assume you have a parameter called p_input_date which is intended to hold an invoice date. The following example will select the most recent invoice date as the default, and note that it properly handles exceptions to ensure that the report does not arbitrarily die if this default setting fails. Note also that like all report triggers, it must return a true or false value.

function BeforePForm return boolean is

begin

select max(bill_period_end_date + 1)

into :p_input_date

from billing_period

where bill_period_end_date <= (select trunc(sysdate)

from dual);

return (TRUE);

exception

when others then

 :p_input_date := null;

return true;

end;

[edit] Why can't I highlight fields and change all their format masks or print conditions 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.

[edit] 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.