| How to send parameter from form to report, and use it in the report query? [message #144946] |
Sat, 29 October 2005 05:51  |
adamjsawyer
Messages: 79
Registered: April 2005
Location: Perth, Western Australia ...
|
Member |
|
|
I want to send a parameter from my form to my report when I use RUN_REPORT_OBJECT. The Report must then take that parameter and use it in the query that selects data from the view. How can I do this? I am trying to send the parameter via the REPORT_OTHER property, but the report can't seem to use the parameter in the query. The whole point of this is to stop the user having to choose a particular invoice number - i just want to display the invoice that's shown on the screen when they click the report button. I've searched for ages through the questions on the forum and can't seem to find anything.
thx
Adam
|
|
|
|
| Re: How to send parameter from form to report, and use it in the report query? [message #144948 is a reply to message #144946] |
Sat, 29 October 2005 09:03  |
adamjsawyer
Messages: 79
Registered: April 2005
Location: Perth, Western Australia ...
|
Member |
|
|
For anyone else with a similar problem, here's how to fix it:
Have a report launcher something like this:
/* PROCEDURE LAUNCH_REPORT:
This procedure takes a report object name, a report filename and report parameter,
and launches the report according to some predefined settings.
*/
PROCEDURE LAUNCH_REPORT(
p_report_object_name IN VARCHAR2, -- Name of the report object to run the report
p_report_filename IN VARCHAR2, -- Filename of the report, eg: COL_REPORT.RDF
p_report_parameter IN VARCHAR2) -- Parameter of report, eg: p_job_id = 2043
IS
-- Constants
c_report_server_name CONSTANT VARCHAR2(50) := :GLOBAL.REPSERVER_NAME; -- Name of the report server (from global variable)
c_report_des_format CONSTANT VARCHAR2(20) := 'HTML'; -- Output format of the report
c_report_file_path CONSTANT VARCHAR2(200) := :GLOBAL.PROJECT_PATH; -- Absolute path to the source report file
-- Variables
v_report_id REPORT_OBJECT; -- The report object that runs the report
v_report_job_id VARCHAR2(200); -- Job ID of the report
v_report_status VARCHAR2(200); -- Status of the report object
alert_button NUMBER; -- Generic number for alert button choice
BEGIN
-- Set up the report object
v_report_id := FIND_REPORT_OBJECT(p_report_object_name);
-- Set the report object properties
SET_REPORT_OBJECT_PROPERTY(v_report_id, REPORT_FILENAME, c_report_file_path || p_report_filename);
SET_REPORT_OBJECT_PROPERTY(v_report_id, REPORT_COMM_MODE, SYNCHRONOUS);
SET_REPORT_OBJECT_PROPERTY(v_report_id, REPORT_EXECUTION_MODE, BATCH);
SET_REPORT_OBJECT_PROPERTY(v_report_id, REPORT_DESTYPE, CACHE);
SET_REPORT_OBJECT_PROPERTY(v_report_id, REPORT_DESFORMAT, c_report_des_format);
SET_REPORT_OBJECT_PROPERTY(v_report_id, REPORT_OTHER, p_report_parameter);
SET_REPORT_OBJECT_PROPERTY(v_report_id, REPORT_SERVER, c_report_server_name);
/*
set_alert_property('BAD_REPORT', ALERT_MESSAGE_TEXT, 'FN: ' || c_report_file_path || p_report_filename || CHR(10) ||
'repparam: ' || p_report_parameter || CHR(10) ||
'repserver: ' || c_report_server_name );
alert_button := SHOW_ALERT('BAD_REPORT');
*/
-- Try and run the report
v_report_job_id := RUN_REPORT_OBJECT(v_report_id);
v_report_status := REPORT_OBJECT_STATUS(v_report_job_id);
-- Loop until the report is ready to be displayed
WHILE v_report_status IN ('RUNNING', 'OPENING_REPORT', 'ENQUEUED')
LOOP
v_report_status := REPORT_OBJECT_STATUS(v_report_job_id);
END LOOP;
-- If the report is ready, then display it
IF v_report_status = 'FINISHED' THEN
WEB.SHOW_DOCUMENT('/reports/rwservlet/getjobid' || SUBSTR(v_report_job_id,
INSTR(v_report_job_id,'_',-1)+1)||'?'||'server='||c_report_server_name, '_blank');
-- Otherwise display an error
ELSE
alert_button := SHOW_ALERT('BAD_REPORT');
RETURN;
END IF;
END;
Then create a user parameter in the Report with the same name as the REPORT_OTHER parameter property. Then you can access that parameter in the WHERE clause of the report's SELECT statement. If you run the report from anywhere other than the calling form, you will just be prompted to enter a value for the parameter.
Cheers
Adam
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
