Advanced Interactive Reports

Simple Upgrades from Classic to Interactive Reports
So you have been playing with Interactive Reports and you know what an impact they will have on the way your users interact with the data - Now to get serious about replacing Classic Reports and going beyond playing with Interactive Reports to implementing them.

We provide a very nice "Migrate to Interactive Report" Wizard (Right hand Tasks panel when editing a Classic Report) which renames the old region appending " - Deleted" to the region name and changing the Condition to "Never". The new Interactive Report will have the same name and region number as the original Classic Report.

If you have old "manual" search regions you should remove them (I suggest you set Condition to "Never" until the new Interactive Report is fully tested) as that functionality is now built in. Make sure you also update the SQL for the Interactive Report removing the corresponding WHERE conditions and move buttons as appropriate {There is a new Button Position "Right of Interactive Report Search Bar" available}.

Here is a quick example from the Bug Tracker Packaged Application:
Old Screen with Classic Report -



New screen using Interactive Report -



Ok so this is hardly earth shattering and hardly does justice to the heading of this post, namely the "Advanced" bit, I added it just to outline how easy it is to upgrade your Classic reports and some of the easy tricks to make migration simpler.

Now you have done all the easy upgrades ... How do you tackle some of the trickier stuff?

Turning off Interactive Report Options
If you want to prevent users from performing any of the default Interactive Report actions then you can simply go into the Application Builder and select the Interactive Report Attributes. This screen is similar to Classic Report Attributes but with some distinct differences. The following sections are all specific to Interactive Report Attributes:

• Search Bar - Manage the Search bar and Actions Menu options
• Download - Specify available download formats (CSV, XLS, PDF, RTF) and details
• Link Column - Specify the "Link" column as either 'Link to Single Row View', 'Link to Custom Target' or 'Exclude Link column'. It comes complete with various items for controlling the look, target, condition and authorization.
• Advanced Attributes - Report Alias and ID needed for accessing from APIs etc, Show saved reports as Tabs (If not use a separate page with a list of reports), and Page Items to submit

Additionally if you want to disable users options when they select a specific Column Heading then edit that Column (from the Report Attributes screen) and modify the options given under 'Allow Users To:'

NOTE: Before switching off the Actions Menu I STRONGLY recommend you running the report and configuring the columns, filters, sorts, etc. exactly as you want them and then "Save Default Report" and then turn off the Action Menu.

Oracle By Example (OBE)
Marcie Young , our curriculum developer, has done a wonderful job of creating several OBEs for this release. I suggest you go to our APEX OTN Site and look at them here: Oracle By Examples. Specifically go through the Utilizing Advanced Interactive Report Region Techniques in Oracle Application Express 3.1 for a good run through Interactive Report advanced features.

Resetting Interactive Reports
End-users can make all sorts of changes to their view of the data and even save off their own reports. Now the end-user wants to start fresh and go back to the Default Layout - Simple he/she just clicks on the action menu and selects Reset.

What if you have hidden the action menu? There are any number of reasons you may want to utilize Interactive Reports but not give the end-user full capabilities.

The answer lies within the documentation: Linking to Interactive Reports. Effectively we have provided the ability to manipulate an Interactive Report from a link.

For our "Reset" example the solution is to create a button with an action of 'Redirect to URL without submitting page' with Clear Cache specified as 'Page Number,RIR'.

RIR is equivalent to the end-user pressing the Reset and will return the Interactive Report back to the default layout.

Using a Search Region
Now lets say we have a report where the users need to enter multiple criteria hit a "Search" button and have the report display the resultant set of data. Using Classic Reports this was as simple as defining additional WHERE conditions within the SQL which optionally inspected the fields for the given criteria.

Can I use the same WHERE Conditions in an Interactive Report? Yes you can and it will work in exactly the same manner as a Classic Report whereby only the subset of the data will be displayed.

An example using the Interactive Report within the New Features App -



The additional WHERE Conditions are:
and (DEMO31_EMPLOYEES.SALARY > :P2_SAL_LOW OR :P2_SAL_LOW IS NULL)
and (DEMO31_EMPLOYEES.SALARY < :P2_SAL_HIGH OR :P2_SAL_HIGH IS NULL)

Let me illustrate a different solution where instead of using the WHERE conditions you use the linking capabilities to create filters based on the criteria entered .

If you remove the WHERE conditions (for the search criteria) from the Interactive Report Source then you can use the linking capabilities to create filters directly on the report.

Same Interactive Report using Linking -



The Branch now includes:
Set these Items: IRGT_SALARY,IRLT_SALARY
With these Items: &P2_SAL_LOW.,&P2_SAL_HIGH.

Now you may well ask "Why bother?" the results are the same in both. Well yes they are sort of in that the data initially displayed is the same. The big difference lies in the fact that when you use the linking option the users can easily disable any of the filters and see additional data.

Alternatively users can now select the filters and modify them to enter different values, etc.

If you do not want the users to be able to even edit the filter displayed, but still be able to enable / disable the filters you will need to specify the link on "HIDDEN" columns.

Same Interactive Report using Linking on HIDDEN columns -



I added two additional columns (HIDDEN) to my source:
DEMO31_EMPLOYEES.SALARY AS SAL_LOW
DEMO31_EMPLOYEES.SALARY AS SAL_HIGH

The Branch was updated to:
Set these Items: IRGT_SAL_LOW,IRLT_SAL_HIGH
With these Items: &P2_SAL_LOW.,&P2_SAL_HIGH.

Now you have restricted filters that can only be enabled / disabled but can't be changed. The downside is you may have to add additional columns, as I did in my case, if you still want to see the column being filtered within the report.

Link One Interactive Report to Another
The OBE uses the same linking capabilities outlined above to link one Interactive Report to another Interactive Report. This is probably the most common use case but I wanted to show a different take on using the linking capabilities to help you identify additional use cases.

If you want to learn how to link two Interactive Report Regions together please read the OBE (given above).

Gotchas
Keep an eye on the 3.1 Known Issues & Limitations as we have found a few issues with all this new code. In fact when doing the sample application as part of this Blog I found a few issues with the linking.