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!