On The Mark Technology

So I've got an application that requires some reporting. There are several different reports, and each report can be run with any number of input filters. Some of those filters are required, some are not. So the requirement is to select a report, and based on that selection, display the available filters - some of those filters even require some dynamic selections.

So here's what I did.

I built a reports table, and a report_filters table - a report may have 1 to many filters associated with it. When the user hits the reports page they see a drop down box of the reports available. Also on this page is a couple of hidden regions; "Filters" and "Results".

The drop down box has an "onChange" action that passes the selected report ID to a server procedure via Ajax that provides the HTML required for all the filters available for that report in the "Filters" section of the page. This is where the Native Dynamic Sql (NDS) comes in.

In my table of report filters I have a column (filter_input_procedure) where I put in an anonymus PL/SQL block. This block gets executed in a loop of all filters for for selected report with an EXECUTE IMMEDIATE command. When this fires, the pl/sql block interacts with the database and outputs the required HTML for the input filter.

procedure build_rpt_filters(p_rpt_id in number) is
cursor available_filters is
	   select * 
           from report_filters_table 
           where rpt_id=p_rpt_id 
           order by sort_seq;
begin
for xx in available_filters loop
	execute immediate xx.filter_input_procedure;
end loop;
end build_rpt_filters;

To build the start date input procedure show in "Report With Two Filters", here are the contents of the "filter_input_procedure" field.

declare
begin
htp.p('Provide the Start Date (mm/dd/yy): ');
htp.p('<input type="text" name="p_start_date" size="10" value="">');
end;

The next thing I had to do was accomodate the user entering a value for a filter, but show them some information that allows them to confirm they have entered the right value.

In the "Report That must do a lookup", the input filter is displaed as before - this time with an input box and a "Submit" button. However, the "submit" button has it's own AJAX call that executes another procedure on the server fires to get another batch of HTML, which is displayed in the "Results" section of the page.

EXAMPLE:

One of the last reasons for doing report filters in this way was a support requirement. The application I wrote this for is supported remotely and often without access to the server. So as much configuration as possible is defined in tables and a user interface built for it. Using Dynamic Sql allows me to control some coding aspects on the fly, and using AJAX allows me to craft a user interface that quick and intuitive.