How to run a report from a form

A J Woodward

Example

Try using this code. The first section you use to create a program unit 'Set_Up_Parameter_List', then the second part you use to create a unit 'Run_Report'. This code works for both CS and Web environments, and as you'll see, checks which environment is being used before deciding whether to call 'Run_Product', as you're doing in your present CS environment, or 'Run_Report_Object' which is the command to use on the Web.



FUNCTION SET_UP_PARAMETER_LIST RETURN INTEGER IS

pl_id ParamList; 
pl_name VARCHAR2(20) := 'REPORT_PARAM_LIST'; 
dummy NUMBER;
BEGIN 
--Set up a parameter list to receive parameters to pass to Reports
--Check that this parameter list doesn't already exist, and destroy it if
--it does
pl_id := Get_Parameter_List(pl_name);
IF NOT Id_Null(pl_id) THEN
Destroy_Parameter_List(pl_name);
END IF;

pl_id := Create_Parameter_List(pl_name); 
IF Id_Null(pl_id) THEN 
dummy := Show_Alert('PARAM_LIST_PROBLEM');
RAISE Form_Trigger_Failure; 
END IF; 

--Add parameter(s) to list just created
Add_Parameter(pl_id, 'P_START_DATE', TEXT_PARAMETER, :START_DATE);
Add_Parameter(pl_id, 'P_END_DATE', TEXT_PARAMETER, :END_DATE);
Add_Parameter(pl_id, 'PARAMFORM', TEXT_PARAMETER, 'NO');

--Got here ok, so return zero to show success to calling procedure
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
--Have screwed up setting up parameter list, so return 1 to calling procedure
RETURN 1;

END; 




--This procedure calls the function to set up the parameter list to run the
--appropriate report, then executes the correct function call to actually 
--run the report. The function called depends on whether the form is being run
--in Web or Client-Server mode.
PROCEDURE run_report IS

report_id report_object;
report_job_id varchar2(200);
rep_status varchar2(200);
JOB_NUMBER number;
v_repserver varchar2(200):= 'Report_Server_Name';
v_server varchar2(500):= 'Server_Name';
v_report varchar2(50):='SAMPLE_REPORT';
v_port number := 80;
v_desformat varchar2(30):='pdf';
v_pl_id PARAMLIST;
v_pl_name VARCHAR2(20) := 'REPORT_PARAM_LIST';
al_id ALERT;
v_dummy NUMBER;

BEGIN

IF Set_Up_Parameter_List = 0 THEN 

report_id:=FIND_REPORT_OBJECT(v_report);

IF get_application_property(USER_INTERFACE) = 'WEB' THEN
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_EXECUTION_MODE, RUNTIME); 
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_COMM_MODE, ASYNCHRONOUS); 
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESTYPE, cache); 
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESFORMAT,v_desformat);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_SERVER,v_repserver);
ELSE
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_EXECUTION_MODE, RUNTIME); 
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_COMM_MODE, ASYNCHRONOUS); 
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESTYPE, SCREEN); 
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESFORMAT,v_desformat);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_SERVER,'');
END IF;

v_pl_id := get_parameter_list(v_pl_name);

--Find the report and then run it, passing the new parameter list to it.
IF get_application_property(USER_INTERFACE) = 'WEB' THEN
report_job_id:=RUN_REPORT_OBJECT(report_id, v_pl_id);
--debug_message('Report_job_id is'||report_job_id); 
ELSE
Run_Product(REPORTS, --product name
v_report, --module name
ASYNCHRONOUS, --communication mode
RUNTIME, --execution mode
FILESYSTEM, --location
v_pl_id, --parameter list
null); 
END IF;

-- Check to see if the report is going to be run against the web
IF get_application_property(USER_INTERFACE)='WEB' THEN
rep_status:=REPORT_OBJECT_STATUS(report_job_id);
--debug_message('Rep_status is '||rep_status);
-- Dynamically Set the Job Number
JOB_NUMBER := length(v_repserver) + 2; 
WHILE rep_status in ('RUNNING','OPENING_REPORT','ENQUEUED', null)
LOOP
rep_status:=REPORT_OBJECT_STATUS(report_job_id);
END LOOP;
IF rep_status='FINISHED' THEN
Web.Show_Document('http://' || v_server || ':' || v_port ||'/dev60cgi/rwcgi60.exe/getjobid=' ||substr (report_job_id,JOB_NUMBER)||'?server='||v_repserver,'_blank');
ELSE
message('Report failed with error message '||rep_status);
END IF; -- end of checking report status
END IF; -- end of checking to see if report is running on web.
END IF; -- end of checking parameter list set up ok

EXCEPTION
WHEN OTHERS THEN
al_id := FIND_ALERT('REPORT_PROBLEM');
SET_ALERT_PROPERTY(al_id, TITLE, 'Failed to run report!');
SET_ALERT_PROPERTY(al_id, ALERT_MESSAGE_TEXT,
ERROR_TYPE||'-'||TO_CHAR(ERROR_CODE)||': '||ERROR_TEXT);
v_dummy := SHOW_ALERT(al_id);
-- shows error dialog box
DISPLAY_ERROR;
RAISE FORM_TRIGGER_FAILURE;
END;

www.lv2000.com