As Published In March/April 2010

DEVELOPER: Browser-Based

Advanced Interactive Reporting

Extend interactive reports with Oracle Application Express.

Oracle Application Express has included interactive reporting capabilities since Release 3.1. With interactive reporting, end users can modify their own data layouts directly in the Web application. Each user can rearrange a report’s data and save multiple layouts for later reference without having an impact on other users.

A previous column, “Building Interactive Reports” ( Oracle Magazine, March/April 2008), explains how to build these reports. This column will take you through some of the more advanced techniques you can employ with Oracle Application Express interactive reports.

We will use the sample application that was built by default when your workspace was created. To begin, confirm that the sample application is installed:

1. Log in to Oracle Application Express. (If you do not have an instance, you can request a workspace at apex.oracle.com.)
2. Navigate to the Application Builder.
3. Search for the sample application.
4. If the sample application is not present
          a. Click Create.
          b. Click Demonstration Application.
          c. If the status of the sample application is Not Installed, click the Install link.
          d. If the status is Installed, click the Re-Install link or click Cancel and search for the sample application. (It is generally the lowest-numbered application).

Setting Parameters for Interactive Reports

If, as a developer of a classic report, you wanted your end users to be able to focus a report by using Search fields, you would need to define a search region, create items, add a button, and modify the underlying SQL to use these search items. With Oracle Application Express interactive reporting, you need to use different techniques, such as setting parameters in the URL to programmatically define report filters.

Removing the search bar. As a developer, you have the ability to include or exclude any of the Oracle Application Express Actions menu items (available via the gear icon) and to include or exclude the search bar altogether. However, if you remove the Reset function, your users will not have the ability to easily reset their reports after they have performed customizations. To provide reset capability to your users in this example, you will add a Reset button before you remove the search bar.

To reset an interactive report in a link, use the string RIR in the clear cache section of a URL. This is equivalent to the end user’s choosing the Reset option from the interactive report Actions menu on the target page. The report returns to the default report settings specified by the developer or saved by the user.

For this example, we will create the Reset button and remove the search bar from the Products page within the sample application:

1. From the Application Builder, click Sample Application.
2. Click Page 3: 3 - Products.
3. Within Buttons, click the Create icon.
4. Select the Products (1) 20 region for the button; click Next.
5. For Position , select Create a button in a region position ; click Next.
6. For Button Name , enter RESET ; click Next ; click Next.
7. For Position , select Top of Region ; click Next ; click Create Button.

Now edit the Reset button to include the RIR string:

8. Within Buttons, click the Reset link.
9. Click the URL Redirect button.
10. For Target is a , select Page in this application ; for Page , enter 3 ; for Clear Cache enter 3, RIR ; click Apply Changes.

Now remove the search bar:

11. Within Regions, click the Interactive Report link.
12. Click the Search Bar button.
13. For Include Search Bar , select No ; click Apply Changes.
14. Click Run.

Now when you run the application, the search bar will be removed and there will be a Reset button. If you or your end users change the interactive report by using the column actions, clicking the Reset button will return the report back to its original layout.

Defining a declarative filter. Within Oracle Application Express interactive reports, an end user can define a column filter by simply clicking a column heading and choosing the appropriate value. Alternatively, a user can define a more complex filter from the Actions menu.

A filter is defined as a string in the following format:

IR<operator>_<target column alias>

where the valid operators are

• EQ = equals (this is the default)

• NEQ = not equals

• LT = less than

• LTE = less than or equal to

• GT = greater than

• GTE = greater than or equal to

• LIKE = SQL like operator

• N = null

• NN = not null

• C = contains

• NC = not contains

To demonstrate how these filters can be used, you will create links from the Orders report on the home page to the customer interactive report:

1. From the Application Builder, click Sample Application.
2. Click Page 1: 1 - Sample Application.
3. Within Regions, click the My Orders link.
4. For Region Source (under Source), change the code line

                               
o.order_id,  

to 

c.customer_id, o.order_id,
                            

5. Click Apply Changes ; click Apply Changes.
6. Within Regions, for My Orders click the Interactive Report link.
7. If the Column Attributes section does not appear, click the Show All button. Click the edit icon for the Customer_Name column.
8. For Link Text , click the [CUSTOMER_NAME] link directly below the field.
9. For Page , enter 2 ; for Clear Cache , enter 2,RIR.
10. For Name / Item 1 , enter IREQ_CUSTOMER_ID . (This filter string uses the EQ filter operator and the CUSTOMER_ID column.)
11. For Value / Item 1 , select #CUSTOMER_ID# ; click Apply Changes ; click Apply Changes.
12. Click Run.

The Orders report on the home page now includes drill-down links to each customer (under Customer Name) and to the customer interactive report.

Another example of declarative filters is available in the hosted APEX Conference Sessions (http://apex.oracle.com/pls/otn/f?p=59652:1:0) application. For example, you may want to show only 2008 sessions for “OUG” conferences. To do this, you would enter the following URL:

http://apex.oracle.com/pls/otn/f?p=59652:1:0::::
IRGTE_YEAR,
IRLT_YEAR,
IRC_CONFERENCE:2008,2009,OUG 

The URL will get records in which the year is greater than or equal to 2008 (IRGTE_YEAR), the year is less than 2009 (IRLT_YEAR), and the conference contains “OUG” (IRC_CONFERENCE). Creating derived columns. Adding SQL derivations to your region source for interactive reports can be a very effective way to show additional information. In the following example, you are going to create a derived column that checks for order totals that exceed the customer’s credit limit, and within the report, you are going to highlight these records:

1. From the Application Builder, click Sample Application.
2. Click Page 1: 1 - Sample Application.
3. Within Regions, click the My Orders link.
4. Modify the region source (under Source) to add the following line after SELECT and before c.customer_id, o.order_id, (case when o.order_total > c.credit_limit then 'Yes’ else 'No’ end) over_limit,
5. Click Apply Changes; click Apply Changes.
6. Click Run.
7. From the Actions menu (the gear icon), select Highlight.
8. For Name , enter Over Limit ; for Background Color, click the [yellow] link; for Highlight Condition Column , select Over Limit ; for Operator , select =; for Expression , select Yes ; click Apply.
9. From the Actions menu, select Save Report.
10. For Save , select As Default Report Settings ; click Apply.

Figure 1 shows the interactive report with the derived column checking for order totals over a customer’s credit limit and the Over Limit columns highlighted.

Figure 1: Interactive report with Over Limit columns highlighted