APEX

Hi,

I'm trying to replicate an existing web reports application. So far APEX has been able to handle all the requirements, until I ran into the following issue. Users need to select and order their columns before displaying the reports. Here's what I've tried:

Option 1: Using Interactive reports.
Though interactive reports seems like a great idea it does have 2 problems that we need to address:
- We can only select the columns after the report has been displayed. This is an issue since some reports take a while to run. After the user selects the columns, the report is re-queried.
- If we want to turn off all the column functionality we'd have to manually go into each column and remove all the extra functionality that is supported when the columns is clicked. Note that I can go into the back end and write scripts to remove all the extra functionality.


Option 2: Generate the report and columns dynamically.
Some extra configuration tables were created that store the queries SQL, columns, and the user's columns preferences.
- Create a function to return the column headers based on users preferences
- Create a function to return the report sql, which is then wrapped with the proper columns that the users want to see for example:
select *
from emp
becomes:
select ename, emp_no, sal, mgr
from (select * from emp);

So far this handles all our requirements. We've had to put a hack in the functions that generate the column names and sql so that if we're developing the report (i.e. app_id = 4000) then show all the columns in the default order. We've had to do this so that the parser would pick up the column meta data for the report attributes page.
We were able to get the following to work:
Downloads
Links
Sortable columns...

The only thing we couldn't get to work was the dynamic column ordering For example:

Our "default" column order is:
ename, emp_no, sal, mgr (this is from the above example). When the code is parse in the APEX developer it sees the columns in this order

At run time, our user has selected the following columns in the following order:
mgr, emp_no, ename, which results in the dynamic SQL returning:
select mgr, emp_no, ename
from (select * from emp);

One would think that the ordering would be "mgr, emp_no, ename" however it returns the order how APEX stored it. ie: "ename, emp_no, sal, mgr'

I've tried the generic column names, but I lose the link functionality.

Any ideas on how to fix this?

Thank you,

Martin