This content has been marked as final. Show 2 replies
rdarlin2 wrote:10,000ft overview of one way to do this:
I want to list several 'canned' SQL queries on one page as buttons or links; and have the result of selection be an immediate extract/download to excel of the data.
How can I best do this?
I suppose I could create separate hidden regions with their own reports that could somehow be referenced in the link. I would like to avoid doing all that, if I can create a package or process that includes the SQL query and that can be called in the link.
<li>Create a package with private functions that return the SQL for each "canned query", and a public switch function that returns a query function based on an input parameter:
<li>Create 2 pages
create or replace package qry is function selector (p_req_qry in varchar2) return varchar2; end qry; / create or replace package body qry is function emp_qry return varchar2 is begin return 'select * from emp'; end emp_qry; function dept_qry return varchar2 is begin return 'select * from dept'; end dept_qry; function selector (p_req_qry in varchar2) return varchar2 is begin return case p_req_qry when 'EMP' then emp_qry() when 'DEPT' then dept_qry() end; end selector; end qry; /
<li>On page 2 create a standard SQL report region of type SQL Query (PL/SQL function body returning SQL query):
*(o) Use Generic Column Names (parse query at runtime only)*
Maximum number of generic report columns
number of columns in your biggest query
<li>On page 1 create an HTML region (or use an APEX list) with a link for each query, where the REQUEST component of the URL is the parameter used to select a query in the <tt>qry.selector</tt> function:
However this begs the question: Why bother? What's Excel got that APEX hasn't?
<ul> <li><a href="f?p=&APP_ID.:2:&SESSION.:EMP">Emp</a></li> <li><a href="f?p=&APP_ID.:2:&SESSION.:DEPT">Dept</a></li> </ul>
Thanks for the clear explaination and example - this will handle my needs just fine.
In re-reading the Apex documentation regarding Report Query functionality, it is clear there is a requirement to have a print server in order to process the data into an attachment for download... similar to the built in ability to download the dataset from an Interactive Report. I may add the print server functionality as we move forward.
The reason for the excercise is that we have a requirement to submit monthly reports of data as a snapshot in time, these need to be forwarded onward to users without access to the DB/app... so data must be extracted, and excel is a common tool for all users.