Your basic interactive report starts with a simple SQL statement such as that in Listing 13-1.

select PRODUCT_ID,
       PRODUCT_NAME,
       PRODUCT_DESC,
       CATEGORY_ID,
       AVAILABLE_YN,
       PRICE,
       CREATED_BY,
       CREATED_ON,
       UPDATED_BY,
       UPDATED_ON
  from AB_PRODUCTS

Using SQL, you can decode your foreign key values, keeping in the IDs to allow for drill downs (discussed later). Listing 13-2 shows a nested subquery being used to retrieve foreign key values.

select PRODUCT_ID,
       PRODUCT_NAME,
       PRODUCT_DESC,
       CATEGORY_ID,
       (select CATEGORY_NAME

from AB_CATEGORIES C
        where C.CATEGORY_ID = P.CATEGORY_ID) CATEGORY,
       AVAILABLE_YN,
       PRICE,
       CREATED_BY,
       CREATED_ON,
       UPDATED_BY,
       UPDATED_ON
  from AB_PRODUCTS P

The nested subquery in Listing 13-2 is an example of an inline select. Inline selects are great for columns that would typically not be displayed because the select will not be executed if the column is not displayed. They are also very good for lookup tables with smaller data sets, as opposed to joining the two tables together in the main select.

Aside from "decoding" your values, you can also improve the display of your data. In the SQL below I change the audit columns CREATED_BY and UPDATED_BY to lowercase. Unless you change the case during insert/update, usernames are typically uppercase; in reports, uppercase not only takes up more physical space than lowercase but also tends to display more prominently to the user, garnering more attention than it needs to. I have also used a case statement to change the Y/N value for AVAILABLE_YN to a more readable and pleasing Yes or No. Lastly, I have included the CREATED_ON and UPDATED_ON columns twice. One I will leave as a date, and the other I will change using a format mask.

select PRODUCT_ID,
       PRODUCT_NAME,
       PRODUCT_DESC,
       CATEGORY_ID,
       (select CATEGORY_NAME
         from AB_CATEGORIES C
        where C.CATEGORY_ID = P.CATEGORY_ID) CATEGORY,
       case when AVAILABLE_YN = 'Y'
            then 'Yes'
            else 'No'
            end AVAILABLE_YN,
       PRICE,
       lower(CREATED_BY) CREATED_BY,
       CREATED_ON CREATED,
       CREATED_ON,
       lower(UPDATED_BY) UPDATED_BY,
       UPDATED_ON UPDATED,
       UPDATED_ON
  from AB_PRODUCTS P

With the initial SQL set, I will create the report, as shown in Figure 13-1.

Figure 13-1. Initial report

The report in the figure is not the greatest looking report. The columns containing my system-generated primary keys should not display to end users, and the column headings can use some updates.

Report and Column Attributes

There are several first clean-up steps that I usually do. These include

• Editing the column headings

• Hiding the ID columns

• Formatting the number columns

• Formatting the extra date columns

Note

There is typically no need to format date columns in a report unless you want something other than the standard format that you have set at the application level. You do always set the Application Date Format and Application Timestamp Format under Edit Application Properties > Globalization (see Figure 13-2), don't you?

Figure 13-2. Edit Application, Globalization settings

From within Report Attributes, I will

• Set PRODUCT_ID and CATEGORY_ID to 'Display Text As' hidden

• Update the following column headings:

• PRODUCT_NAME to Name

• PRODUCT_DESC to Description

• AVAILABLE_YN to Available?

Within Column Attributes, I will also apply a Number/Date Format to PRICE of FML999G999G999G999G990D00, nicely displayed in the list of values as $5,234.10, and I will apply the SINCE format to my additional date columns. This will then display the time in relation to the current time (e.g., 4 hours ago). Last, for now, I will center the value for Available? (Column Alignment = center). I like to have this sort of Boolean data centered, but you can follow whatever standards make sense to you and, most importantly, to your users. The revisions are shown in Figure 13-3.

Figure 13-3. Report after limited clean-up

Looking a bit better, but there is more work to be done.

Column Groups

Although they do not show on the running report, if a report has a decent number of columns (say, ten or more), I define Column Groups, as shown in Figure 13-4. This is especially helpful if your report contains data from multiple tables and when the group names help to describe the columns.

Figure 13-4. Editing Report Attributes, adding Column Groups

Column Groups are used from the Actions > Select Columns menu selection to describe the columns. Figure 13-5 shows the dialog from which you define a group. Figure 13-6 shows how groups appear in a single-row view.

Figure 13-5. Selecting columns for display, using Column Groups

Figure 13-6. Single row view, using Column Groups

There are several ways to include links within a report: report link, column link, and a link created within your query. To add a report link, you click Report Attributes and navigate to the Link region. From there, you can either link to the single-row view, to a page within your application, or to a URL. Most reports include an edit link that goes to a form based upon the main data, so a report link would be the logical place to define that. One nice thing about report links is that they are always displayed within the report; the column cannot be hidden using Select Columns.

Column links are a great way to link between reports. You edit the Column Attributes and scroll to the Link region. Just as with the Report Link, you can link to a page or URL and pass column values from your report as well as values within items in your application. I will add a derived column to my report and use it to link to another report in the application—often referred to as a drill-down. Listing 13-4 shows the SQL generating the derived column.

select PRODUCT_ID,
       PRODUCT_NAME,
       PRODUCT_DESC,
       CATEGORY_ID,
       (select CATEGORY_NAME
         from AB_CATEGORIES C
        where C.CATEGORY_ID = P.CATEGORY_ID) CATEGORY,
       case when AVAILABLE_YN = 'Y'
            then 'Yes'
            else 'No'
            end AVAILABLE_YN,
       PRICE,
       (select COUNT(*)
         from AB_PRODUCT_REVIEWS R
        where P.PRODUCT_ID = R.PRODUCT_ID) NBR_REVIEWS,
       lower(CREATED_BY) CREATED_BY,
       CREATED_ON CREATED,
       CREATED_ON,
       lower(UPDATED_BY) UPDATED_BY,
       UPDATED_ON UPDATED,
       UPDATED_ON
  from AB_PRODUCTS P

Be aware that you can edit the SQL for your report, but all columns added, regardless of their position within your select, will display at the bottom on your Report Attributes. Because you order your columns within your running report, the order on Report Attributes is really not important but if you are crazy like me, feel free to use the up and down arrows on the far right to shift your columns around to match your revised query. Also, be cautious about your column aliases when editing. Application Express matches columns by alias, not by position, so if you edit CREATED_BY and change it to lower(CREATED_BY) WHO_CREATED, the old column and any definition you customized will be removed and a new column will be added.

Once the column is defined, I define the column link as shown in Figure 13-7, passing the PRODUCT_ID to the report you are calling. To pass a filter into an interactive report, you prefix the column alias you are passing to with a condition. You need to remember to use the column alias, not the column name from the table, because you might have modified the column in some way and used an alias. For this use, I want IR_ which means equals (equals is the default).

If you want to pass a term and set the filter for contains, you would use the prefix of IRC_. The documentation lists all the valid operators and I will include them below for reference:

When calling a page and passing in a filter, you need to consider that the user may have already customized the report they are landing on (e.g., they may have filters applied, columns hidden, or be displaying the third page of results, etc.). If you want them to land on the report and ensure that the data you want displayed is displayed, you use one or more of the following options in the Clear Cache segment of the URL:

If the primary default report contains a filter or other report setting that you want to clear, use both RIR and CIR, separated by a comma. If there are no special settings in the default or if you want to maintain the settings (e.g., keep in place displaying just the last seven days of data along with the new filter you are passing) just use RIR. In my example, I am displaying the count of reviews and then linking to the reviews page. I will use both RIR and CIR to ensure that the users get the same number of records in the report as was displayed in the count.

If there are items on the page you are calling that are used within the query, it may also be appropriate to clear the session state for that page. That would be accomplished by passing the page number in Clear Cache, along with RIR,CIR (depicted in Figure 13-7).

Figure 13-7. Defining a column link

Because I already ran the report, the new column, Reviews, will not show by default. To display it, I need to click Actions > Select Columns and shift it to the right. At the same time, I will shift a bunch of the auditing columns to the left to make the report more streamlined, as shown in Figure 13-8.

Figure 13-8. Report with column link and fewer columns displayed

Within any report you are calling, the id that is passed will likely be a hidden column. I always edit the report attributes for that report and change the Column heading to Selected xyz. When the report is called, I now see this filter, shown in Figure 13-9.

Figure 13-9. Report called with filter set

If you need to navigate to different pages based upon the data or the role of the user, you can create the link URL within your query and then include it as a column reference (e.g., #LINK#). The snippet below would produce a link taking users to page 2 for OPEN items and to page 3 for CLOSED items (clearing the cache of the page and setting the application level item PREV_PAGE to page 1 so that a Cancel button and branches can return the user to the proper page.

select ID,
       case when STATUS = 'OPEN'
            then 'f?p=&APP_ID.:2:&APP_SESSION.:::2:PREV_PAGE,P2_ID:1,'||ID
            when STATUS = 'CLOSED'
            then 'f?p=&APP_ID.:3:&APP_SESSION.:::3:PREV_PAGE,P3_ID:1,'||ID
            end LINK,
       ITEM_NAME
  from ...

Using this method, you can vary the URL but a link will always display.

Often, it is useful to include links for only certain records. That is handled by coding a full <a href... into your SQL, as shown in Listing 13-5.

select PRODUCT_ID,
       PRODUCT_NAME,
       PRODUCT_DESC,
       CATEGORY_ID,
       (select CATEGORY_NAME
         from AB_CATEGORIES C
        where C.CATEGORY_ID = P.CATEGORY_ID) CATEGORY,
       case when AVAILABLE_YN = 'Y'
            then 'Yes'
            else 'No'
            end AVAILABLE_YN,
       PRICE,
       case when AVAILABLE_YN = 'N'
            then '<a href="f?p=&APP_ID.:7:&APP_SESSION.:MAKE_AVAILABLE::7:P7_PRODUCT_ID:' ||
                 P.PRODUCT_ID ||'">Make Available</a>' end ACTION,
       (select COUNT(*)
          from AB_PRODUCT_REVIEWS R

where P.PRODUCT_ID = R.PRODUCT_ID) NBR_REVIEWS,
       lower(CREATED_BY) CREATED_BY,
       CREATED_ON CREATED,
       CREATED_ON,
       lower(UPDATED_BY) UPDATED_BY,
       UPDATED_ON UPDATED,
       UPDATED_ON
  from AB_PRODUCTS P

After adding this to my query, I need to visit the Report Attributes page and change the display type of the column to Standard Report Column because each column is set as 'Display as Text (escape special characters)' by default. Without the change, I would get the actual html displayed on the report (useful when debugging) rather than a link. When I run the report, I again need to use Actions > Select Columns to make the newly added Action column displayed, as shown in Figure 13-10.

Figure 13-10. Report with dynamic link

Notice that this is the first time there has been null data within the report. If you do not like the default display of "-", you can access Report Attributes and, within the pagination region, remove the Show Null Values as value or add in something that works for you. Remember that this is used for all null values within the entire report, so if you need a different value for different data (null for the Action but "n/a" for something else), you would handle that within the SQL itself by using an nvl:

nvl(CATEGORY,'n/a') CATEGORY

So far, I added a column, but for this action to work, the page would need a P7_PRODUCT_ID item and a before header process to perform the update and then clear out the value that was passed.

update AB_PRODUCTS
   set AVAILABLE_YN = 'Y'
 where PRODUCT_ID = :P7_PRODUCT_ID;

:P7_PRODUCT_ID := null;

The condition on this process would be

:P7_PRODUCT_ID is not null and :REQUEST = 'MAKE_AVAILABLE'

Filtering Using Items

There are times when you want to expose a filter as an item rather than just as a report column. This works well for a real data driver—where you almost always want to see just a certain slice of the data. With interactive reports, you can use a combination of item filters and columns filters. For this I like to use the button bar region template. You then define your item or items and either include a submit button or can make the item self-submitting (like a select list). Within your report, you just modify your where condition to reference this item, handling null values if appropriate (if your item can be null):

and (:P10_APP_ID = APP_ID or :P10_APP_ID is null)

Whenever you have an item that can modify your query, make sure that you include a process that will reset the pagination for the report. If not, the user will get that annoying "Invalid set of rows requested, the source data of the report has been modified. Reset Pagination" message, and that is not really nice. For self-submitting items, you will need a process. To include this, Create Process > Reset Pagination > Condition Type of 'Request = Expression 1' and Expression 1 should be the name of the item. For buttons, you can either create a process (using When Button Pressed) or include a conditional branch that does the reset, as shown in Figure 13-11.

Figure 13-11. Branch calling page with a reset of pagination

Column List of Values

Many applications these days have items that are tagged, that is, a column containing multiple descriptors, separated by commas. Using the built-in column list of values will not help your users find "all my items with the tag of APEX". To accomplish this, I add a column list of values that selects all the unique values from my tags table (or even better, all the values from a table that stores just the unique values).

select TAG
  from AB_TAG_SUM
 order by TAG

I also set the Column Filter Type to Use Defined List of Values to Filter Word Contains. This way it will not just find "APEX" but "APEX, Database", "Security, APEX, SSO", etc. Notice that column list of values only has one value, not a display and return like an item's list of values. You can use a standard list of values but only the first column, or display value, will be used. Without a custom list of values, Application Express will display the first 999 unique values from the column within the query. An example of the list of values display for Tags is shown in Figure 13-12 and the resulting report, after selecting APEX, is shown in Figure 13-13.

Figure 13-12. Report showing column filtering with column list of values

Figure 13-13. Report after value selected

You can also use column list of values to improve performance. If you have a report with a large data set, rather than having the reporting engine calculate the unique values to display, use a list of values. In Figure 13-14, I have associated a named list of values with the Category column and will use an Exact Match.

Figure 13-14. Column list of values using Named List of Values

I have also created a list of values for the Available? column. This will improve performance because I already know that the only possible values are Yes and No. Be aware that there is a drawback to using column lists of values—they do not auto-reduce based upon the filtered dataset. When you have thousands of records (or hundreds of thousands) with just Yes or No, it is worth using. You will notice in Figure 13-15 that if you do not have a shared static list of values to reference, you need to select the values from dual (isn't SQL cool?).

Figure 13-15. Column list of values using Defined List of Values

Once you have all the columns you want in your report, I run it and decide which columns should show by default and what the default sort order should be. To make that the default for all users, you use the Actions Menu and click Save Report > As Default Report Settings > Default Report Type of 'Primary'.

If there are other views that would be useful for your users, you can define those as well and save those as alternates. After you do this, a selection list will display in the search bar allowing users to quickly toggle between the saved reports. If I have a report that deals with dates, I tend to create an alternate that includes a filter by date. By using the row filter, you can create a report that shows the data from the last day or 7 days, which is something that the typical end user would never figure out on their own. By including an alternate report with that filter, users can easily modify it to change the date range for what they need. To do this, you click Actions > Filter > Filter Type of 'Row'. You provide a name (Last 7 days) and use the alias associated with each column to create your "where" clause, trunc(H) >= trunc(sysdate)−7, where H is the Created By column, as shown in Figure 13-16. When working with dates, I tend to include a "trunc" around each to ensure that I am getting the data set that I expect.

Figure 13-16. Row filter limiting report to records created within the last 7 days

With this view current, I use Actions > Save Report > As Default Report Settings > Default Report Type of 'Alternate' and provide a name as Figure 13-17 shows.

Figure 13-17. Saving an alternate default report

Once saved, the report selector now appears within the Search Bar of my interactive report and will display for all users. Figure 13-18 shows what this looks like.

Figure 13-18. Search bar showing saved report

Each interactive report can have one chart display associated with it. You can create this chart for your users and save it as an alternate public report, just as I discussed with the report that showed the data from the last 7 days. Charts are defined from the runtime environment and available if the Chart attribute is enabled (which it is by default). I will create a bar chart showing the number of Reviews each day. This begins by creating a report that contains all of the reviews along with the date on which they were created. Listing 13-6 shows the SQL for that report. I will also include the product and category so that a user could change the report to show the number of reviews by product or category if that is what they need.

select REVIEW_ID,
       (select CATEGORY_NAME
          from AB_CATEGORIES
         where CATEGORY_ID in (
               select CATEGORY_ID
                 from AB_PRODUCTS
                where PRODUCT_ID = R.PRODUCT_ID)) CATEGORY,
       PRODUCT_ID,
       (select PRODUCT_NAME
         from AB_PRODUCTS P
        where R.PRODUCT_ID = P.PRODUCT_ID) PRODUCT,
       substr(REVIEW_CONTENT,1,50) ||
          case when length(REVIEW_COMMENT) > 50
               then ' ...'
               end REVIEW_CONTENT,
       lower(CREATED_BY) CREATED_BY,
       trunc(CREATED_ON) CREATED_ON
  from AB_PRODUCT_REVIEWS R

With the report running, I use Actions > Format > Chart and specify the options shown in Figure 13-19.

Figure 13-19. Chart attributes

Now the page displays the chart and a user can toggle between the report and chart using the buttons in the Search Bar, as Figure 13-20 shows.

Figure 13-20. Resulting chart

Once the report is complete, I can save it as a public report and name it so that all users can access it. Figure 13-21 shows the new chart in the toolbar.

Figure 13-21. Search bar showing saved chart

In the example of linking from the Products report to the Reviews report, I wanted users to land on the primary default Reviews report (thus the RIR). It is also possible to provide a link to an alternate default report or a public report. This is accomplished by passing IR_REPORT_[report alias] in the request segment of the URL.

Every publically accessible report is given a unique, numeric alias. This can be viewed and updated by clicking on the Saved Report link to the right of the region name in the page definition classic view, as Figure 13-22 indicates.

Figure 13-22. Location of Edit Saved Reports in Classic View

Or you can right-click the region name and select Edit Saved Reports in the tree view, as Figure 13-23 indicates.

Figure 13-23. Location of Edit Saved Reports in tree view

The Saved Reports page not only displays the report alias but also displays a link example for each report. This is what you would code into a link within an application to both call the proper page and invoke the desired report. In Figure 13-24, I have updated the Report Alias for the chart created in the Charting section to have the alias of CHART_BY_DAY.

Figure 13-24. Editing Saved Reports

To invoke this chart from another page, I would use:

f?p=33368:10:&APP_SESSION.:IR_REPORT_CHART_BY_DAY

Often, you include an interactive report because of the pleasing display or the desire for column heading formatting but you really do not want or need to expose the search bar to the end users. You can control both report- and column-level settings under Report Attributes.

Report-Level Settings

Figure 13-25 shows the report-level settings that you can choose from for the reports that you create.

Figure 13-25. All the search bar options

The search bar is displayed above a report. If you include the search bar, you should customize the functions that you want to display. Figure 13-25 shows the following settings:

• Search Field: Displays a text field in the search bar used to enter search criteria.

• Finder Drop Down: Enables users to search on a selected column or against all columns.

• Reports Select List: Displays reports select list if the report has alternative default, shared report, or personal reports.

• Rows Per Page Selector: Displays a select list used to select the number of rows to display per page. I never use this one any more because I feel that it clutters the search bar.

• Actions Menu: Enables the user to customize the report based upon the functions you select to include.

The following functions display on the Actions pull-down menu. You need to select the ones you want to display.

• Select Columns: Used to hide, unhide, and order the report columns.

• Filter: Used to add filters to the report.

• Rows Per Page: Used to display the rows per page selector. This is the selector I prefer over the one in the search bar.

• Sort: Used to sort columns. As opposed to using column heading sorting, this allows you to select multiple columns and identify if you want nulls first or last (the default is nulls last for Ascending and first for Descending).

• Control Break: Used to build control breaks on report columns. This allows you to pull columns out of the report and use them to group the rest of the data. This is useful in conjunction with aggregate to do the sum of revenue by region.

• Highlight: Used to define conditions that will highlight certain cells or rows. You decide whether to highlight rows/cells that match your criteria with a background color and/or a special text color. This is useful to identify values outside of a given norm such as base salary above a certain threshold.

• Compute: Used to add computed columns. I usually put my computed columns within my select but this allows users to add additional ones. You can do math (SALARY+COMMISSION) and even include SQL statements such as DECODE and CASE (probably not something that the average end user will understand unless you are writing an application for a bunch of SQL programmers).

• Aggregate: Used to include aggregate calculations on report columns. These include Sum, Average, Count, etc.

• Chart: Used to create a Flash chart based on the data in the report. An example of this was discussed earlier in this chapter.

• Group By: Used to create a Group By view based on the data in the report. This produces a summary report based upon the parameters chosen. For my example Products report, I could group by Category and then calculate the Average Price.

• Flashback: Used to flashback the report data to some time in the past. This is only useful if you are running your database in flashback mode and are retaining the data (from a timeframe perspective) that a user would be interested in.

• Save Report: Used to save the report settings for future use. This is not displayed for unauthenticated pages.

• Save Public Report: Used to save Public Report for all users to view. You must select Save Report to enable this option. With this unchecked, users can still save reports, but just for their own use. If you leave this enabled, any user that meets the authorization in Save Public Report Authorization can save a report as a public report. Leaving this enabled with No Authorization Required is probably not a great idea for applications with many users as the sheer quantity can be overwhelming, thus diminishing the usefulness. This is not displayed for unauthenticated pages.

• Reset: Used to reset the report to the default settings.

• Help: Used to display instructions for using the Interactive report. It will only include a discussion of the options that are enabled for that report.

• Download: Used to download the report in different formats. The available formats are specified within the Download Region.

• Subscription *: Used to send a report to a list of comma-separated email addresses (as of version 4.0.2; only one email address prior to that version). You provide a subject for the email along with the frequency (Daily, Weekly, Monthly). You can also include start and end dates. The report that is sent contains the data set at the current point in time as well as the current report settings. This is not displayed for unauthenticated pages.

Also within the report-level settings is the Save Public Report Authorization (mentioned above within Save Public Report). Additional settings that control the look of the search bar include Button Template, Finder Image, Actions Menu Image, Search Button Label, and Maximum Rows Per Page.

Before you turn off the search bar, make sure that you have saved the default report settings for the report (because once the actions menu is off, you cannot do it—well, you can: you just turn it back on, run the report, save the settings and then turn it back off— a process I am very familiar with). If you are removing the search bar, but keeping on column heading features like filtering and sorting, you will want to create a Reset button to allow your users to get back to the default settings, as shown in Figure 13-26. I sometimes include a Reset button even when I have kept the search bar and actions menu. I place it to the Right of Interactive Report Search Bar. This way, users that are not even familiar with the Actions Menu can clear any filters that might be present from drilling into a report. The button needs to simply call the page passing RIR as the request (and the page number if you also have items referenced in your 'where' clause).

Figure 13-26. Reset button attributes

Column-Level Settings

There aren't as many column-level setting as there are report-level settings. Figure 13-27 shows the few settings that do apply at the column level.

Figure 13-27. All the column-level options

For columns that contain large amounts of non-repeating text data, you should remove the filter capability from the heading. For the Products report that I have been using, this would be wise for the product name because, as a unique key, there will never be a repeating value so the column list of values would just be a repeat of the report.

There are also columns that you may want to ensure the user cannot hide by accident. For those, you uncheck the Hide within the Column Definition. Again, referencing the Products report, I would not want users hiding the product name or they would lose all context for the data. I would also never want someone to attempt to group by the product name—it just does not make sense. (If you leave it on, someone will try it.) Basically, look at each of your columns, consider your data model, and decide which options make sense and then turn off the rest. This will help to protect your users from themselves (e.g., "But why can't I build a chart on product description?").

Within Application Express, there are many utilities that provide cross-region, page, and application reporting and update. Several of these utilities are particularly useful when working with Interactive Reports.

The first handy utility worth mentioning is the Edit Interactive Report Column Headings page (from the Page Definition, edit all regions > Utilities tab > Grid Edit Interactive Report Column Headings). From here, you see all your IR columns and can easily update your headings (see Figure 13-28).

Figure 13-28. Edit Interactive Report Column Headings

Also available from the Region Utilities is the 'Update Interactive Report Settings option, which gives you the dialog shown in Figure 13-29. This dialog allows you to review all your report-level settings and make adjustments to most of them. This is great for applications where you want to ensure that no one can download data—just uncheck the download option for all reports.

Figure 13-29. Update Interactive Report Settings

There are also three useful utilities under Administration: Saved Reports, Subscriptions, and Page Views by Interactive Report. The first two are accessible from Administration > Manage Interactive Report Settings (under Tasks). Saved Reports shows all the Primary, Alternate, and Private reports that are saved for all applications in the workspace (see Figure 13-30). The report itself is an interactive report so you can filter on the application you are interested in reviewing. Using this report, you can view the report alias for all the public reports within a workspace. You can also use this page to remove alternate and private reports. This is handy if you forget to remove Save Public and a user saves a proliferation of Public Reports.

Figure 13-30. Saved Reports

The Subscriptions report (Figure 13-31) allows a developer to remove subscriptions for invalid email addresses or for people who have left the company or who have forgotten how to remove their own subscriptions ("Why do I keep getting this report?").

Figure 13-31. Subscriptions

The Page Views by Interactive Report is under Administration > Monitor Activity (under Page Views). This displays when each saved report was viewed, allowing you to decide which saved reports might be able to be deleted. This report pulls from the Activity Log and on heavily used instances (like apex.oraclecom) can be slow. In the report in Figure 13-32, I have hidden a few columns and filtered on a specific page so that I could see if an alternate view of a specific report that I saved were being used.

Figure 13-32. Page Views by Interactive Report

The alternate default report was last used 22 hours ago, and not by me, so I will be keeping it around.

APEX_UTIL functions

APEX_UTIL is a public package that provides access to lots of great utilities. Several of these are specifically for interactive reports.

IR_CLEAR and IR_RESET

IR_CLEAR is the programmatic way to clear the report settings for a report, essentially a CIR for the page and saved report that you specify. IR_RESET is the programmatic way to reset a report, essentially an RIR for the page and saved report that you specify. As private reports have no alias, they cannot be used with these procedures. The syntax for each is

APEX_UTIL.IR_CLEAR (
    p_page_id       IN NUMBER,
    p_report_alias  IN VARCHAR2 DEFAULT NULL );

APEX_UTIL.IR_RESET (
    p_page_id       IN NUMBER,
    p_report_alias  IN VARCHAR2 DEFAULT NULL );

To clear the primary report, you can either pass the report alias of the primary report or leave the p_report_alias null. Recall that you can find and update the report aliases for saved interactive reports using either Saved Reports from the page definition or the Saved Reports available within Administration. The report alias can also be queried from the APEX_APPLICATION_PAGE_IR_RPT view. If you have not used the APEX Views yet, that might be the best tip of all. They are accessible from within the Application Builder under Utilities and you can also query them from within a schema that is associated with Application Express.

Both these procedures are used within the example for IR_FILTER.

IR_FILTER

IR_FILTER allows you to programmatically create a filter on an interactive report. This can be handy when your filter value could contain a colon (mitigating the need to escape it) and also when you need to set so many filters that the URL gets unwieldy. The syntax is

APEX_UTIL.IR_FILTER (
    p_page_id        IN NUMBER,
    p_report_column  IN VARCHAR2,
    p_operator_abbr  IN VARCHAR2 DEFAULT NULL,
    p_filter_value   IN VARCHAR2,
    p_report_alias   IN VARCHAR2 DEFAULT NULL );

To create multiple filters, you just call IR_FILTER repeatedly. The region in Figure 13-33 contains three items that a user can specify to preset filters.

Figure 13-33. Items to pass via IR_FILTER

To pass these values to a report on page 7, I will first reset the report that I want displayed; its alias is PRIMARY. I will then clear any filters or other customizations that might have been put in place by the user when they last visited the report. Finally, I will conditionally set several filters.

begin
   apex_util.ir_reset (
      p_page_id      => 7,
      p_report_alias => 'PRIMARY');

   apex_util.ir_clear (
    p_page_id       => 7,
    p_report_alias  => 'PRIMARY' );

   if :P12_CATEGORY_ID is not null then
      apex_util.ir_filter (
         p_page_id        => 7,
         p_report_column  => 'CATEGORY_ID',
         p_operator_abbr  => 'EQ',
         p_filter_value   => :P12_CATEGORY_ID,
         p_report_alias   => 'PRIMARY' );
   end if;

   if :P12_NAME_CONTAINS is not null then
      apex_util.ir_filter (
         p_page_id        => 7,
         p_report_column  => 'PRODUCT_NAME',
         p_operator_abbr  => 'C',

p_filter_value   => :P12_NAME_CONTAINS,
         p_report_alias   => 'PRIMARY' );
   end if;

   if :P12_PRICE is not null then
      apex_util.ir_filter (
         p_page_id        => 7,
         p_report_column  => 'PRICE',
         p_operator_abbr  => 'LTE',
         p_filter_value   => :P12_PRICE,
         p_report_alias   => 'PRIMARY' );
   end if;
end;

This process would be called by a button on my page. Lastly, I will need a branch to take my users to page 7, focusing them on the PRIMARY report that I want displayed, as shown in Figure 13-34. Remember, you set the request equal to the report alias prefaced by IR_REPORT.

Figure 13-34. Branching to the PRIMARY report on page 7

When the user clicks the submit button, Figure 13-35 shows the resulting report.

Figure 13-35. Report with filter programmatically set

IR_DELETE_REPORT

IR_DELETE_REPORT is the programmatic way to delete saved interactive reports. It can be used to delete all but the primary default report. If you want to build the ability to delete saved reports from within your application, allowing administrators to delete reports who might not have access to the development environment, you would use this. You would create a page that displays all the saved reports, querying from the APEX_APPLICATION_PAGE_IR_RPT view and then call this procedure.

Please note that it must be executed from within the same running application where the reports reside—if it is called from another application or from within SQL Commands, it will not work. The syntax is very simple:

APEX_UTIL.IR_DELETE_REPORT (
    p_report_id  IN NUMBER );

I used the following query to produce a report of all the public, non-primary reports in my application.

select apex_item.checkbox(1,report_id) to_delete,
       page_id,
       (select page_name
          from apex_application_pages
         where application_id = :APP_ID
           and page_id = ir.page_id) page_name,
       (select r.region_name
          from apex_application_page_regions r,
               apex_application_page_ir i
         where i.region_id = r.region_id
           and i.interactive_report_id = ir.interactive_report_id) region_name,
       report_name,
       report_alias
  from apex_application_page_ir_rpt ir
where application_id = :APP_ID
   and report_type = 'ALTERNATIVE_DEFAULT'

Once created, I edited the TO_DELETE column to change it to a Standard Report Column. Figure 13-36 shows what the resulting page looks like.

Figure 13-36. All public, non-primary interactive reports within an application

To delete these, I just use the following in a procedure that is invoked with the delete button:

for i in 1..apex_application.g_f01.count loop
   apex_util.ir_delete_report (
      p_report_id => to_number(apex_application.g_f01(i)));
end loop;

IR_DELETE_SUBSCRIPTION

IR_DELETE_SUBSCRIPTION is fairly self explanatory. It is an alternate to using the Subscriptions report described previously.

APEX_UTIL.IR_DELETE_SUBSCRIPTION (
    p_subscription_id  IN NUMBER );

You can access all the subscriptions for an application using the APEX_APPLICATION_PAGE_IR_SUB view and then create a loop to remove just the ones that match your criteria. The following example deletes the subscriptions to all interactive reports within the workspace that are being sent to [email protected]. Note that in this view, you will need to query the NOTIFY_ID to pass into P_SUBSCRIPTION_ID.

for c1 in (
   select NOTIFY_ID
     from APEX_APPLICATION_PAGE_IR_SUB
    where upper(EMAIL_ADDRESS) = '[email protected]' )
loop
   apex_util.ir_delete_subscription (p_subscription_id => c1.notify_id);
end loop;

You might be wondering why you select "notify" to pass to "subscription". Well, during development, Subscriptions were called Notifications. One day before release, we decided to change the name to better reflect the feature. We thought we updated the wording everywhere— I guess we missed the APEX view!

User Interface Defaults

Another handy and underutilized utility for reports is User Interface Defaults. Oracle Application Express 4.0 introduced the Attribute Dictionary so you can now define standards based on just column name rather than for each column within each table. This allows you to set a standard heading for CREATED_BY. If you use UI Defaults during creation, that value will be picked up by the wizard but if not, or if you define it after creation, you can use the Attribute Dictionary page available from the Page Definition under Utilities (see Figure 13-37).

Figure 13-37. Attribute Dictionary from within the Application Builder

For reports, this checks your column aliases against the Attribute Dictionary and then reports attribute-by-attribute. You can then identify the attributes that you wish to "pull" into your report. You can also "push" values into the Attribute Dictionary to use as your standards and then use those to compare against other reports (see Figure 13-38). If consistency is your thing, this feature is for you!

Figure 13-38. Update Attribute Dictionary using Report Columns

Activity Log

In addition to using the activity log to see how often pages within your application are being accessed and how performant they are (how great is the Weighted Page Performance report?), starting with Application Express 4.0, you can now see the terms searched for in the search bar of your interactive reports. You can access this information with Application Express under Administration > Monitor Activity > Recent Page Views, as shown in Figure 13-39. You will need to expose the Interactive Report Search column and do some filtering.

Figure 13-39. Recent Page Views

The data within the activity log is available within the APEX_ACTIVITY_LOG view and the new column is named IR_SEARCH. You can write an interactive report in your application that will display all searches within each report in your application. The SQL would look something like:

select APPLICATION_ID, PAGE_ID, PAGE_NAME, IR_SEARCH
  from APEX_WORKSPACE_ACTIVITY_LOG
 where APPLICATION_ID = 33368
   and IR_SEARCH IS not null

To find the most searched terms, the SQL would look something like this:

select APPLICATION_ID, PAGE_ID, PAGE_NAME, IR_SEARCH, count(*) cnt
  from APEX_WORKSPACE_ACTIVITY_LOG
 where IR_SEARCH IS not null
 group by APPLICATION_ID, PAGE_ID, PAGE_NAME, IR_SEARCH
 order by APPLICATION_ID, PAGE_ID, cnt desc

Keep in mind that logging activity is rotated between two different log tables. Because of this, logging information is only as current as the oldest available entry in the logs. If you want to persist your activity log data, you must copy the log information into your own application table.

There are times when an application should show a column or edit link to only some users, based upon an access control list (ACL) that the application maintains. If this is data dependent, you can create that link using the dynamic link creation discussed above. If instead the edit link should always be displayed to Administrators and never displayed to regular users then you can just create a report-level or column-level condition. Just like conditions for almost every attribute in an application, you can select from the predetermined list as shown in Figure 13-40 (always the fastest from a performance perspective) or define a custom one using PL/SQL. If your condition is already expressed within an authorization scheme, you don't need to create a condition—you can just associate that scheme to the column or report link.

Figure 13-40. Report column, authorization

To see the SQL that gets executed when a user accesses an interactive report, you can run the page in debug mode. To do this, you must be logged on as a developer and debugging must be enabled for the application (Edit Application, Properties region). Once you have your filter and formatting in place, you turn debugging on by clicking the Debug button in the Developer Toolbar at the bottom on your browser (see Figure 13-41).

Figure 13-41. Developer Toolbar

You can then click View Debug and select the most recent record associated with the application and page you are working on. That will display the debug output, as shown in Figure 13-42.

Figure 13-42. Debug output

With that output, run the query in SQL Developer to analyze the performance. If you need to figure out the bind variable values, you can look at the following APEX views:

If a query is not performant in SQL Developer, it is not going to speed up when you create an interactive report based upon that query. We often have someone complain that Application Express is slow when it is really their SQL that is slow.

As a developer, you must understand that Application Express scans the first 10,000 rows of your data. If you are using interactive reports on large data sets, you should definitely use inline selects and column list of values as well as defining a filter on the default report to limit the number of records returned (like the one discussed about limiting to just those records from the last day or 7 days). You can also consider fronting your report with a form that requires the selection of certain parameters and then use those in your "where" clause to narrow the results (as discussed earlier in this chapter in the IR_FILTER section). This way your users still get the functionality of an interactive report, just against a smaller data set. If none of these options can limit your result set enough, then an interactive report is just not the right solution and you should consider creating a classic report.

I hope this chapter has helped you learn more about interactive reports and see how useful they can be to an application. If you create them properly, considering your data model and the data that will someday be stored within your tables, interactive reports can really enhance your Application Express application.