Interactive Report based on Dynamic SQL

Apex doesn't let you create an interactive report based on dynamic SQL. That is, not out-of-the-box. Apex does allow you to create an interactive report based on a function, so you could create a pipelined function based on dynamic sql, but with a pipelined function your report is predefined by the return type of your function. In this post I will demonstrate a way to create an interactive report based on dynamic SQL using Apex collections. The result is far more flexible than a report on a pipelined function. The setup will comprise of a few steps:

1. create a function that returns a query as string
2. create a before header process that creates a collection based on the query string
3. create an interactive report based on the collection
4. create page items to map collumn headers and a process to set the column headers
5. Create display conditions for the report columns

What the function that returns the query looks like, is entirely up to your requirements. The function can be used in the before header process to create a collection. That process will look like this:

Next you need to create the interactive report. This is very simple since all data for the report is in the collection you create with the before header process.

This will create the interactive report, but this will render the column labels as C001, C002, etc. One way to create meaningful column headers is to create (hidden) page items for each column of your interactive report. You can create a process to fill the page items with the column names from your query and you can reference the column headers in your interactive report column names using the &Pxx_ITEM_NAME. notation. The procedure for within the process can look something like:

The procedure sets the session state for the page items. Note that, if you want to use dynamic sql based interactive reports on more than one page in your application, you can best create the hidden page items on your applications global page. Since you set the session state for the items, you can reference their value throughout your application. The cursor's select statement queries the column names from the “USER_TAB_COLUMNS” table. This works for all tables and user created views in your schema. However, if the result of your dynamic SQL is a complex query with joins over multiple tables, or when you don't include all columns of a table, you'll have to modify the query accordingly.

One last step we need to perform is to add display conditions to the report columns: we only want to show the columns in the interactive report, if they are needed, i.e. contain any data. You can use a simple display condition of type "Exists", in which you can use the following query:

Here you'll need to replace "XX" with the number of the column: "C001", "C002", "C003", etc. By now everthing is in place and your interactive report should be working. You can check an example in my demo application.
Good luck!

Apex 5.0 Early Adopter

This weekend Oracle launched the long awaited Apex 5.0 Early Adopter release. I just signed up for a workspace on apexea.oracle.com to check out some of the new features. I must say that my first impression is very positive. If you haven't already, you can sign up here for a free workspace

The first thing that struck me was that the look and feel of the application builder has changed slightly: they've used a flat design. As a test I've exported my demo application from apex.oracle.com, and imported it in apex 5.0. In general, this worked smooth and my application worked straight away. A few things were left for adjustment: the css and JavaScript files and the images that were stored in the database were not copied along. So I started uploading of my JavaScript file. That's when I noticed the first new features: instead of uploading files to an image, css or static files folder, you can create your own folder structure. Also you can bulk upload zipped files and -something I really missed in previous Apex versions- you can download files.

Since I made my own folder structure I also needed to change some of the file references. I navigated to the first page I wanted to edit and that's when I noticed the complete new layout of the page editor. The bulk of the editor is now made up of a grid layout, which displays your page and all its regions. You can drag and drop buttons and new regions into the grid and have your page setup in no time. Once you click on a page component, all the properties of that component are displayed on the right side of the screen. You can edit them straight away. Code fields have a modal pop up box with syntax highlighting, which is a major step forward from the small text area's that I was used to.
Unfortunately the modal editor doesn't seem to be available for dynamic action code fields.

After restoring my demo application, I decided to create a new app to check some of the announced features. First thing I wanted to check was if I could get more than one interactive report on a page. You can easily create more than one IR on a page, but only one can be active at a time. I'm not sure if there is an out of the box method to switch between active IR's, or that developers will have to create their own solutions.
Another feature I found was the pivot option of Interactive Reports, which lets you create pivot tables in just a few button clicks.

All in all my first steps in Apex 5.0 are very positive. It is really a big step forward from Apex 4.2. So I guess I'll be spending the next few weeks doodling in the new Apex environment and learn all of the new features. I'll update this post with new findings occasionally, so make sure to check back regular.

UPDATE:
I've taken a look at the new theme (Theme 31) and the templates. They look really nice. Again here the template editor has syntax highlighting, which aids readability. Even better: the editors come with code completion! Next to that the edit screen is enhanced and now includes separate fields for notifications and for sub templates. Also you have separate sections for JavaScript and CSS, for which you can include your own file paths rather than the Apex files.