How to run report for web.

[misterimran]

Hi,

we have developed our forms and reports in Developer 6i and we want to run them through web and we are using 9i Application server for this purpose.
Our forms are running but reports are not. In client/Server environment we were using RUN_PRODUCT builtin to run our reports. Can anyone send me the code how to run these reports on web please?
I have installed database and application server on two separate machines.
please help with code and explantion, its urgent.

Imran Baig

 

[ajwoodward]

Hi

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.

Best wishes
A J Woodward
UK

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('[URL unfurl="true"]http://'[/URL] || 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;

 

[misterimran]

Thanks a lot friend, but how to call this procedure in my button on the form that runs the report??

I am doing it like this

RUN_REPORT();

and when i click the button no result where is the mistake?

regards, Imran