Calling sub report / another report in oracle reports 10g – Hyperlink Oracle Reports

Hi Readers,

i am going to show you a very simple method of calling sub report / another report in oracle reports 10g.

Hyperlink reports in oracle means call another report from a report by clicking at a value.  For example, we have a report like bellow,

BRANCH ID

BRANCH Name

10001 LAHORE CANT
10002 MULTAN

And we want to call another report named BRANCH_DETAIL of a Branch by clicking on BRANCH ID. How can we achieve this?

Select the data field (i-e Branch ID ) and press F11 to go code editor. this will open FormatTrigger code editor.

Add the bellow code before return (true);

SRW.SET_HYPERLINK(‘http://ser/reports/rwservlet?userid=hr rep_ser_oracle&desformat=pdf&destype=cache&desname=d:\branch_detail.PDF&report=D:\SER_Bin\Reports\branch_detail.rdf&branch_Id=’||:branch_Id);

(Change the report server url as you have, report name and parameter if you have)

for example:

function F_BranchFormatTrigger return boolean is
begin
  SRW.SET_HYPERLINK(‘http://ser/reports/rwservlet?userid=hr rep_ser_oracle&desformat=pdf&destype=cache& desname=d:\branch_detail.PDF&report=D:\SER_Bin\Reports\branch_detail.rdf&branch_Id=’||:branch_Id);
  return (TRUE);
end;

Compile and save the reports. If needs convert to any other extension.

Now run your report…

Note : It is recommended, Please use encrypted login information.

Advertisements

Query Builder

Query Builder is an entirely separate Oracle product, however within Reports 10g, Oracle has created a cutdown version for selecting data only, basically it allows the Developer to grab tables and their columns, apply functions to them and when complete return to Reports 10g with the SQL query built. 

Using Query Builder to its fullest extent is a course in itself, however, the following slide outline the main functionality of the product with reference to its usage in Reports 10g. 

Before Query Builder will begin, if the Developer is not already connected it will prompt them to connect. One other point, if there is SQL already written in the Data Source Definition and is in error then Query Builder will repeat that error message when it opens

Query Builder will display a list of all tables/views available for the

Developer to select from the current schema

Display1

 

 

 

 

 

 

 

 

 

 

This dialog will not close automatically and each table included will be entered into the dialog behind it ….

Here the Developer has selected the Products table and has clicked on several columns to select them …

 

 

 

 

 

 

 

 

 

 

 

To create an Order By clause the Developer must click on the nineth button from the right which displays the columns which are available for sorting …

display3

 

 

 

 

 

 

 

To create a condition, click on the left hand panel, then either manually enter the condition or double click on the required column and enter the condition needed, here the Developer is limiting records with a Product Number of 9. display5    

 

 

 

 

 

 

 

 

Clicking on the green tick button will accept and check the condition, if it is correct then a second input line will appear …

display5

 

 

 

 

 

 

 

 

 

To create a column with a function around it, click on the button with the green plus sign above a disk pack, this will display the following dialog to define a new column.

display6

 

 

 

 

 

 

 

 

The Developer must first name the new column, this will then allow access into the ‘Defined as’ area where the new column can be set out, this will also enable the Paste Column and Paste Func buttons from which the Developer can select

display7

 

 

 

 

 

 

 

 

 

display8

 

 

 

 

 

 

 

Here the Developer has created a new column called

‘lowered_description’, this will now appear in the list of columns and subsequently in the Data Source Definition dialog …

 display9

 

 

 

 

 

 

 

display10

 

 

 

 

 

 

 

This example is now ready for the Developer to move onto the next option or apply column aliases/labels

display12

 

 

 

 

 

 

 

Oracle Reports

Various values of ‘Print Panel Order’ property of report.

Misc Oracle Reports Information.

Various Oracle Reports infomation.

Physical and Logical pages in Reports

Other triggers used in Oracle Reports

Various Module Types in Reports.

Can u have more than one layout in report?

What are different types of column in reports?

What is the difference between Frame and Repeating Frame?

Various values of ‘Print Panel Order’ property of report.

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.

Misc Oracle Reports Information.

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. 

Display a message in reports. 

Using SRW.Message 

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.

‘Print Direction’ Property of Repeating frames. 

‘Print Direction’ Property specifies the direction in which successive instances of the repeating frame appear. 

Various values of the ‘Print Direction’ Property of Repeating frames ?

Following are the various values :

  • 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.

‘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. 

‘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. 

‘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. 

‘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.

 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). 

Some of the procedures in the SRW package. 

* SRW.Message, SRW.User_Exit, SRW.Do_Sql, SRW.Run_Report 

Report layout regions.

 There are three report regions in the Layout editor : 

  •  header
  •  body/margin
  •  trailer

Brief Description of the various report layout regions. 

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. 

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.

 

Various Oracle Reports infomation.

Various page layout sections in Oracle Reports .

A report has three sections : 

  • the report header pages, 
  • report body/margin pages, 
  • and report trailer pages.

Various types of parameters. 

There are two types of parameters: 

  •  default (system parameters)
  •  user-created (bind and lexical parameters)

Reference parameters and columns in reports . 

There are two ways to reference a parameter in Oracle reports: 

* As bind references

* As lexical references 

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. 

Types of Matrix report. 

 1. Single query 

 2. Multi query

 3. Nested Query 

 4. Matrix Break

 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. 

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. 

‘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. 

‘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.

 ‘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.

 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.

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.

Physical and Logical pages in Reports

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.

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

 

Other triggers used in Oracle Reports?

Apart from the Five Global Report Triggers, there are three other types of triggers :
* Validation Triggers
* Format Triggers
* Action Triggers

What is Validation Trigger?

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.

What is Format Trigger?
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.

What is Action Trigger?

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.

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. but if you want to add a matrix layout, it will create manually by using cross product option.