Oracle Business Intelligence Discoverer is a powerful tool designed to assist you in querying, reporting and analyzing your data. However, Oracle has stopped adding new features or enhancements at version 11.1.1.7.0, and has entered the “Sustained Support” phase of its life since June of 2017. Oracle recommends that Discoverer customers migrate to BI Foundation Suite, and that EBS customers move to Oracle BI Applications. There is certainly nothing wrong with following these recommendations, as long as you don’t consider additional licensing fees and the acquisition of a new skillset as anything wrong.
Migrating from Discoverer to “something else” is a scary proposition, because there again are the costs involved in acquiring a new product and a new skillset, and the possibility of complications in integrating with Oracle.
What if I told you that you already own a product that, while not an exact replacement of Discoverer, can accomplish many of the tasks that you were using Discoverer to do, and does so with a user-friendly intuitive interface? That product is Oracle Application Express (APEX), and it is included as a no-cost add-on to the Oracle Database.
This blog posts looks at five common tasks that should be familiar with Discoverer users and explains how those same tasks are performed using Oracle APEX Interactive Reports. While the following tasks are accomplished in Discoverer using a variety of interfaces, all of the features we will be exploring can be accessed from an Interactive Report by selecting “Actions | Format” and then the specific feature.
Computation
A standard practice of relational database design is to not store everything. If you can calculate a value and you don’t always need the calculated value, you just save what is necessary to get the value when you need it. Including these calculated values in a report typically requires the query that retrieves the data to include these formulas in order to display the calculated columns.
Discoverer users can add computed columns by selecting Tools | Calculations.
Resulting in the following…
APEX Interactive Reports allows you to add calculated columns to your report by selecting Actions | Format | Compute.
The new column, ‘Year_Sal’, has been highlighted for clarity.
Aggregation
When reporting against numeric data, it is often desirable to include aggregations such as the total or average amounts.
Discoverer allows for the definition of aggregate columns by selecting Tools | Totals.
Line 15 displays the resulting aggregation.
Not surprisingly, APEX Interactive Reports makes including these aggregations a very simple matter indeed. All you need to do is select the column and aggregation type, and the additional information is included in your report.
Control Break
Often when viewing a report, the amount of information can be overwhelming, even if all of it is necessary. In cases such as this it is often useful to subdivide your report into multiple reports based on a one (or more) aspects of the data, thus removing repeated data elements.
Using Discoverer, this is accomplished by selecting Tools | Sort then identifying the field or fields in which the data is to be grouped. Then under Group specify “Group Sort”
I’ve moved Deptno to column 1 for demonstration purposes.
APEX Interactive Reports allows you to easily create a “Control Break”. You simply need to select one or more columns to be used to subdivide your data, and apply the change to view your newly formatted report.
Notice, the data is sorted on Dname.
Pivot
Pivot tables are a very effective way of displaying normalized, relational table data in a more readable format. Historically, the biggest problem with pivot tables is that they were hard to create.
Within the Discoverer work area, you can create pivot tables by dragging a worksheet item heading from a “row” position to a “column” position.
Discoverer then redraws the worksheet with the new configuration.
APEX Interactive Reports has taken the drudgery out of creating pivot tables. You simply select the desired row and column configuration of your pivot table, as well as any functions that you want to apply to which columns, and APEX does the rest.
Export
The features we mentioned above are only a few of those provided by APEX Interactive Reports that allow you to modify the default design of a report. However, without the ability to share the resulting report, the perfection of its presentation is meaningless.
Oracle Discoverer does allow exporting of a single worksheet or workbook as XLS, HTML, TXT, CSV, PRN, DIF, SLK, and WKS.
With Interactive Reports, the Download feature allows you to output your report as a CSV file, an HTML file, an Excel worksheet, a PDF document, or an editable RTF document.
Conclusion
Discoverer users shouldn’t worry that moving to Oracle Application Express (APEX) will result in decreased functionality. Quite the contrary! Oracle APEX is a soft place to land because as this blog demonstrated, Interactive Reports can meet or exceed existing Discoverer functionality in the areas of Computations, Aggregations, Control Breaks, Pivots and Exports.
If you have a complex Discoverer example and question whether APEX is up to the task, please contact me for a free consultation.
