Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

Linking Forms and Reports by isbangee


Linking Forms and Reports oracle

More Info
									Calling Forms and Reports within a Form
Note: This document is intended as a walkthrough for linking forms with forms and forms with reports. More complete information on this should be available in your Oracle book. Overview Allowing a forms or reports to be called from another form is part of making your system userfriendly. It will allow the end user to show a specific report, or open another form by just pressing a button (in most cases). It will be your job to make sure it happens for him/her. You have the option of passing a value (or several values) when calling a report or form. This tutorial will show you how to do so for a report. In exhibit A, an example of a form that calls a report passing two different dates is shown. In this case, the user would fill out two different dates for the desired report.


The end-user will enter the start date and end date. Then he or she will click on GO, and a report will come up with the visits registered between these two dates.

In another case, the enduser could call up the sensitivity report, which does not require any date or other input.

Tutorial Developed at the College of Business, Florida State University.

Page 1

Creating your basic form For this type of form, there is generally no need to create a data block, since you will not be reading from the database. You can start a blank form and go directly to creating a canvas. You will notice later that a data block has automatically been created as you add push-buttons, text items, labels, etc. What should you include in you form? You will need a button for each report or form you call. If you are going to pass values, you will need a text item for each. You can also add all the labels you want. The form in exhibit A has three buttons, two text items, and a couple of labels. Each button calls a different form or report. The procedures have been coded in the WHENBUTTON-PRESSED trigger for each button. Calling another form To call a form, you need to use the CALL_FORM procedure. You can also add the EXIT_FORM procedure to close the calling form, although this is optional. Exhibit B shows an example of the PL/SQL code used to call a form.

Indicate the name of the form being called with its full path. Also, make sure you are calling the compiled version of the form (.fmx) Optionally, use EXIT_FORM to close the calling form

That’s it! You can now test you button, make sure it works. If it does not, the most common problem is in the path and/or file name used.

Tutorial Developed at the College of Business, Florida State University.

Page 2

Calling a report Calling a report is a bit more complex. You have the option of sending a ‘parameter list’, which is simply a list of different values, passed to the report so that it filters unwanted data. Going back to the example shown in exhibit A, you can see that two different reports can be called from this form. The first one involves passing two dates to the report – this is where the parameter list comes in. The second report does not require any input from the user, thus we will not need a parameter list for this one. The procedure to call a report is called RUN_PRODUCT. A typical use of this procedure to call a report would be:

There are several parameters that do not need to be changed. In fact, you will mostly have to worry about the path and filename, and the parameter list (both of which are in bold in the example above). Exhibit C presents an example in which we will be calling a report and not passing any values to it.

The full path and filename of the report.

Note that we are not passing values to this report, so we are not using a parameter list. We will indicate this using two single quotes.

Tutorial Developed at the College of Business, Florida State University.

Page 3

In some cases, you will want to pass one or more values to the report. The example in exhibit A, shows a report that receives two values: a starting date and an ending date. Aside from coding the trigger for button performing the call, you will also need to make some modifications to the report, so that it knows it has to expect values from the calling form. Exhibit D shows the code for passing two values to a report. You can see there are several lines of code required, but this is not hard to understand. Let’s go over it! First, we need to declare a variable that will hold the parameter list’s system id (just an internal number used by the computer to identify it). Once this is done, use the CREATE_PARAMETER_LIST procedure to create and assign it any name we want. Now, all that is left is to put some data in it. This is done through the ADD_PARAMETER procedure. In this example, we will call it twice: one for the starting date and one for the ending date for the report. Then, we will use RUN_PRODUCT to call the report (indicating the name for the parameter list). Last, just to keep things clean, we will erase the parameter list from memory using DESTROY_PARAMETER_LIST.

I am assuming you already created your form (Canvas + Data Block)…
This declaration will hold the create identification for the for an LOV: all that you need parameter list.


The wizard will A record definition. You can think of a Record Group as a View, only that it is stored in the form module, not in the database.

Now, you can create the parameter list and assign it a group andthis case temp_data. name. In the actual LOV

q q q q

Select the Record Groups node Click on Create When prompted, select Use the LOV wizard

Then, you can add parameters (the values you are going to pass to the report).

On the next window, select New record group based on a query, and click on next.
Call the report, indicating the parameter list. To keep things clean, we will remove the parameter list from memory.

Tutorial Developed at the College of Business, Florida State University.

Page 4

You might find the ADD_PARAMETER procedure a bit complex at first. There are some considerations to take… A sample syntax for this procedure is:

Note that parameter_list_id is a name you gave the variable that was to hold parameter list identification, so if you use a different name, be sure you change is here too. The next value is very important. When the report loads and receives the values from the parameter list, each parameter should have a name by which it will be identified. You will see the importance of this further on. For now, make sure you use a meaningful name for each parameter. The next value, TEXT_PARAMETER, is the most common, so we will not go into details. The last parameter is the variable or block item holding the value you want to send to the report (this tutorial passes the value directly from the block item). Ok! We have our form-side coding done… Now what? Now it is time to create or modify a report, so you can start loading Oracle Report Builder. If you are building a report from scratch or you already have a report done, the only thing that will be affected is the SQL code. What we are going to do is just add a WHERE clause to it.

Exhibit E shows the code for a report, before we considered the incoming parameter list. Now look at exhibit F, and note the two new lines.

This is the original code for the report, before considering an incoming parameter list.

Tutorial Developed at the College of Business, Florida State University.

Page 5

At this point, you must remember the names you gave each parameter back in the form. You will use the same names here, preceding them by a colon.


We only added two more lines to the WHERE clause using AND. Also, note that we are comparing values in the database column to what are called Bind Variables.

In this example we These bind variables have the same name are using the as the values coming in the parameter list. greater or equal to and the less than or equal to operators. This will not be the case for every report. Neither will the amount of parameters passed. This is were you will be able to prove your SQL coding skills.

Adva nce d To pic: Whenever you call your report using a parameter list, you will notice a window opening up, allowing you to recheck the values in the parameter list. Adding the following line to your PL/SQL code in your form will prevent this from happening.

Tutorial Developed at the College of Business, Florida State University.

Page 6

To top