 |
|
Oracle Tips by Burleson |
Add
Query By Example to a Report
In the previous
sections of this chapter, a report was created and several
modifications made. The search page item allowed the report to be
resubmitted to filter the data containing a match to what was typed
in. However, this is not always what is wanted. What if the user
wanted to search for all records where Payment Type = Visa, and the
Donation was greater than ten? This would not be possible with the
current search method. What needs to be done is the creation of
another region on the application page and the addition of page items
to be used to filter the query used to populate the report.
The next exercise is indicative of what I’ve seen
performed quite a lot. The developer creates a report using the
wizard and gives it to user community. Then, they ask for a change to
the report so they can filter the report with different data for each
field in the report. This exercise will guide you in that process.
Navigate to the page definition page for
application page 11.
Create the Filter
Region
1.
On the Page Definition page click on  icon
in the Regions section.
2.
On the Region page choose the HTML option and click Next.
The HTML region type creates a blank
region with no page items allowing the developer to create them
themselves.
3.
On the Display Attributes page:
·
Title: Filter Criteria .
·
Region Template: Reports Region.
·
Display Point: Page Template Body (2. items below region
content).
Another way to
do this is to make this selection by clicking on the  icon
to the right of the Display Point select list. This is a valuable
popup allowing the developer to see graphically the location of each
of the region display pointson the
page as shown in Figure 7.10. The developer would then click on the
Page Template Body (2) link as shown in Figure 7.10.
·
Sequence: This can be left at the current value.
The sequence is
often a matter of personal preference. Since the new region is being
placed in at a different display point and since it is the only region
in that display point, the sequence becomes irrelevant. A later
chapter in this book will cover form layout using region position and
sequence in detail.
·
Click Next.
4.
On the Source page click Create Region.
Now that the Filter Criteria region is
created, it is time to add page items to the region. These page items
will later be used in the WHERE clause of the SQL statement in the
report region to filter the results. Currently the report is filtered
by the P11_REPORT_SEARCH page item, but when this exercise is complete
it will be filtered by the page items in the Filter Criteria region.
The following example walks through
creating one page item and modifying the SQL statement for the
report. After creating that page item, the example will move on to
create the rest of the page items using a feature named Create
multiple items.
First off, move the Go button to the
Filter Criteria region and hide the P11_REPORT_SEARCH page item.
Navigate to the page definition page for
page 11.
5.
Click on the link for the P11_GO button.
6.
Change the Sequence to 100 so it displays after all other page
items in the region.
7.
Change the Region to Filter Criteria.
8.
Apply Changes.
Now, hide the P11_REPORT_SEARCH page item
9.
Click on the P11_REPORT_SEARCH link in the page definition
page.
10.
In the Conditions region, change the Condition Type to Never.
The [never] link can be used to speed up the process.
11.
Apply Changes.
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
