Wednesday, September 18, 2013

APEX 4.2.3 Released, new packaged app "Data Reporter"

As someone who develops a large number of Oracle APEX apps I frequently have the dilemma of guessing what type of reporting the end users will want. Basically its hard to know what reports people want in the future. To ease this burden the APEX development team and the special projects team here within Oracle have created a new packaged application called "Data Reporter". Here is how it works.

1. Run any Oracle APEX 4.2.3 environment, and if you don't have get a free one here

2. Click application builder, then create application, then packaged applications, then choose data reporter:

3. Install the application and run it. Installing can take a minute or more.

4. From the Data Reporter home page click the New button

5. Choose the type of report you wish to create, my favorite is the Filter Report, so I will choose one of these

6. Give the report a name and category. Color coding the category allows you to locate you report more rapidly on the home page.

7. Now for the important part, define your query. Administrators of the application can set up canned data sources. I simply selected "Top Cities" and it pre-populated the SQL. I can edit the SQL or enter new SQL. The theory is that SQL not that hard especially if you start from an existing query. For security each table accessed must be pre-defined by the application administrator by adding it to the "Whitelist".

8. Since this is a filter report, I need to determine which columns I would like to filter on. For this data set I choose to provide filters for country, sources, and continent. I also adjusted format masks. I only really need to select one filter and I can simply edit my report later.

9. Thats, it I confirm my report and I am good to go.

10. After creating the report immediately runs. I click the filters I wish to have; in the example below you see largest cities in Europe and North America from selected sources.

11. I can edit my report by clicking on edit. Each time the SQL is changed it is revalidated. I can manage access control, view history of when the report was run and changed etc.

12. Clicking the star icon, favorites the report, making it easy to find the reports you are interested in

13. Navigating back to the home page I see my new report, and it can be run by anyone with access to the application. Of course I have the option to restrict access. This allows reports to be timely addressing what is needed today, and can be removed if. You can filter to show your reports, favorites, specific groups etc.

You can follow the same process to build dashboards, interactive reports, calendars and even PDF printable reports. Hopefully this brief introduction will ease the burden on application developers to create lots of reports an effort to anticipate what end users will want in the future. Now end users can, or at least the SQL Savvy ones can get their data self service.


  1. Hi Mike

    Great app ! it really shows a new potential in APEX - Expanding an application by generating pages programatically.

    I noticed the user of a new API (apex_app_builder_api), which seems to be unsupported. Do you know if this kind of "builder" API's will be supported in the future ?

    Best Regards
    Martin Nielsen

  2. Yes hopefully APEX 5.0 will document and support the generation of pages. For many component types, for example classic reports we don't need to generate pages, but for Interactive reports the meta data tracked per user (e.g. saved reports, subscriptions etc) necessitates one application page per interactive report. Are you interested in dynamically building application pages from within an application for components in addition to interactive reports?

  3. Hi Mike,

    this app is truly a beauty! It looks like we have a replacement found for "Oracle Discoverer" type of power users that know how to handle SQL. I know certain customers in Germany would welcome this app. Thx.

  4. Hackers take a close look on the access control of the organizations in order to affect their work.So it is very necessary that organizations must imply a goof access control system.

    Sanola Jerry

    Access Control

  5. Hi Mike.. this app would be great, if there was an easy way to integrate the query builder, so users would not need to know the query structure

  6. @Mike: "Yes hopefully APEX 5.0 will document and support the generation of pages. (...) Are you interested in dynamically building application pages from within an application for components in addition to interactive reports?"

    This would be a great addition to Apex! In fact, I wrote a tool called Apexgen several years ago that basically did this, albeit unsupported of course. See this page for details:

    To be really useful, this feature/API should support at least the following: Create pages, create regions (at a minimum: HTML region, PL/SQL region, and both interactive and classic report region), create page item, create page button, create page process, create page branch.

    The business case for this would be to kick-start the development of an application with a simple PL/SQL script based on metadata (the data model/dictionary), similar to the way "scaffolding" works in other frameworks.

    - Morten