Home » Developer & Programmer » Forms » Call report from a form - hits Invalid parameter Error
Call report from a form - hits Invalid parameter Error [message #197480] Wed, 11 October 2006 07:23 Go to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Hi guys,

Could you please help me to figure out what is going wrong
with my form which run a report?

I am using Developer suite 10g.

when i run report from report application it run fine,
but when i call that report from a form it gives me error.
I have checked the datatype, checked the parameter value
that I assign from form is available in the report parameter value.

What else do i have to check in order to run this report
from the form.



Error Message from Report Queue Manager:

Invalid value for parameter 'P_YEAR'.



I have also attached the error screenshot.


Your insight will be appreciated.


icon2.gif  Re: Call report from a form - hits Invalid parameter Error [message #197510 is a reply to message #197480] Wed, 11 October 2006 09:15 Go to previous messageGo to next message
chmlaeeque
Messages: 59
Registered: September 2006
Member
check the format mask of the parameter from where you are passing the value and to which parameter you are passing the value ( in report ),
the format mask must be same.
Re: Call report from a form - hits Invalid parameter Error [message #197532 is a reply to message #197510] Wed, 11 October 2006 11:55 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Thanks for your reply, chmlaeeque.

I checked the both place format mask are same!

what could be the next?
icon2.gif  Re: Call report from a form - hits Invalid parameter Error [message #197544 is a reply to message #197532] Wed, 11 October 2006 12:55 Go to previous messageGo to next message
chmlaeeque
Messages: 59
Registered: September 2006
Member
try to add format mask like this be sure that the year format write must be same as in report parameter.

add_parameter(pl_id,'report_parameter',text_parameter,to_char(:text_item,'YYYY'))
Re: Call report from a form - hits Invalid parameter Error [message #197815 is a reply to message #197544] Thu, 12 October 2006 16:18 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Here is my code which run report,
now where do i put the formate mask for p_year?


DECLARE 

-- variables for running individual reports
  report_filename			VARCHAR2(40);
  var_reportpath			VARCHAR2(100);
  report_otherparms 	VARCHAR2(200);
  
-- variables to keep track of successful reports in silent mode
  rep_status					VARCHAR2(40);
  rep_req							NUMBER := 0;
  rep_count						NUMBER := 0;
  dummy								NUMBER;
  
-- variables to load the report names and checkboxes into the parameter list  
  pl_id								PARAMLIST;
  plname							VARCHAR2(20) := 'rpt_list';
  
-- variables to loop through the parameter list and run the reports
  i										NUMBER;
  split_loc						NUMBER;
	param_value					VARCHAR2(40);
	checkbox_name				VARCHAR2(40);
	report_basename			VARCHAR2(40);
	svc_name						VARCHAR2(4);
	run_report					BOOLEAN;
  
  
 	disp_exist            BOOLEAN := get_current_service_count( :global.filename_prefix, 'DISP',
				:reports_control.year_list, :reports_control.quarter_list,
				:reports_control.group_list );
  hosp_exist            BOOLEAN := get_current_service_count( :global.filename_prefix, 'HOSPICE',
				:reports_control.year_list, :reports_control.quarter_list,
				:reports_control.group_list );
  orth_exist            BOOLEAN := get_current_service_count( :global.filename_prefix, 'O/P',
				:reports_control.year_list, :reports_control.quarter_list,
				:reports_control.group_list );

BEGIN

-- Are any reports selected?
IF BOXES_ARE_CHECKED(:System.Cursor_Block,'PRINT_NULL_REPORT') THEN
	
  if not CHECKBOX_CHECKED('print_null_report') then
    disp_exist := TRUE;
    orth_exist := TRUE;
    hosp_exist := TRUE;
  end if;


-- Create Report Parameter Listing

pl_id := GET_PARAMETER_LIST(plname);
if NOT ID_NULL(pl_id) then
	DESTROY_PARAMETER_LIST(pl_id);
end if;

pl_id := CREATE_PARAMETER_LIST(plname);
if ID_NULL(pl_id) then
	GENERIC_ALERT('Error creating report listing');
	RAISE Form_Trigger_Failure;
end if;
ADD_PARAMETER(pl_id,'id1', TEXT_PARAMETER,'cover_page_intro!INTRO');
ADD_PARAMETER(pl_id,'id2', TEXT_PARAMETER,'utilization_profile!ALLUTIL');
ADD_PARAMETER(pl_id,'id3', TEXT_PARAMETER,'utilization_summary!ALL_SUMMARY');
ADD_PARAMETER(pl_id,'id4', TEXT_PARAMETER,'hiv_profile!HIV_PROFILE');
ADD_PARAMETER(pl_id,'id5', TEXT_PARAMETER,'wound_care_profile!WOUND_CARE_PROF');
ADD_PARAMETER(pl_id,'id6', TEXT_PARAMETER,'top_10_diagnosis!TOP10DIAG');
ADD_PARAMETER(pl_id,'id7', TEXT_PARAMETER,'diagnosis_profile!DIAG_PROFILE');
ADD_PARAMETER(pl_id,'id8', TEXT_PARAMETER,'physician_profile!PHYS_PROFILE');
ADD_PARAMETER(pl_id,'id9', TEXT_PARAMETER,'physician_specialty!PHYS_SPECIALTY');
ADD_PARAMETER(pl_id,'id10',TEXT_PARAMETER,'discharge_profile!DISCHARGE_PROFILE');
ADD_PARAMETER(pl_id,'id11',TEXT_PARAMETER,'acute_care_discharge!ACUTE_DSCHRG');
ADD_PARAMETER(pl_id,'id12',TEXT_PARAMETER,'excessive_cost_patients!EXCESSIVE_COST');
ADD_PARAMETER(pl_id,'id13',TEXT_PARAMETER,'provider_profile!PROV_PROFILE');
ADD_PARAMETER(pl_id,'id14',TEXT_PARAMETER,'service_profile!SERVICE_PROFILE');
ADD_PARAMETER(pl_id,'id15',TEXT_PARAMETER,'age_gender_profile!AGE_GENDER_PROF');
ADD_PARAMETER(pl_id,'id16',TEXT_PARAMETER,'network_services!NETWORK_SERVICES');
ADD_PARAMETER(pl_id,'id17',TEXT_PARAMETER,'thh_section_header!THH_COVER');
ADD_PARAMETER(pl_id,'id18',TEXT_PARAMETER,'thh_utilization!THH_UTILIZATION');
ADD_PARAMETER(pl_id,'id19',TEXT_PARAMETER,'thh_service_profile!THH_SVC_PROF');
ADD_PARAMETER(pl_id,'id20',TEXT_PARAMETER,'hit_section_header!HIT_COVER');
ADD_PARAMETER(pl_id,'id21',TEXT_PARAMETER,'hit_utilization!HIT_UTILIZATION');
ADD_PARAMETER(pl_id,'id22',TEXT_PARAMETER,'hit_service_profile!HIT_SVC_PROF');
ADD_PARAMETER(pl_id,'id23',TEXT_PARAMETER,'hme_section_header!HME_COVER');
ADD_PARAMETER(pl_id,'id24',TEXT_PARAMETER,'hme_utilization!HME_UTILIZATION');
ADD_PARAMETER(pl_id,'id25',TEXT_PARAMETER,'hme_service_profile!HME_SVC_PROF');
ADD_PARAMETER(pl_id,'id26',TEXT_PARAMETER,'disp_section_header!DISP_COVER');
ADD_PARAMETER(pl_id,'id27',TEXT_PARAMETER,'disp_utilization!DISP_UTILIZATION');
ADD_PARAMETER(pl_id,'id28',TEXT_PARAMETER,'disp_service_profile!DISP_SVC_PROF');
ADD_PARAMETER(pl_id,'id29',TEXT_PARAMETER,'hosp_section_header!HOSP_COVER');
ADD_PARAMETER(pl_id,'id30',TEXT_PARAMETER,'hosp_utilization!HOSP_UTILIZATION');
ADD_PARAMETER(pl_id,'id31',TEXT_PARAMETER,'hosp_service_profile!HOSP_SVC_PROF');
ADD_PARAMETER(pl_id,'id32',TEXT_PARAMETER,'orth_section_header!ORTH_COVER');
ADD_PARAMETER(pl_id,'id33',TEXT_PARAMETER,'orth_utilization!ORTH_UTILIZATION');
ADD_PARAMETER(pl_id,'id34',TEXT_PARAMETER,'orth_service_profile!ORTH_SVC_PROF');
ADD_PARAMETER(pl_id,'id35',TEXT_PARAMETER,'encounter_detail!ENCOUNTER_DETAIL');


-- set up other parameters to send to generate_report

/*
try to add format mask like this be sure that the year format write must be same as in report parameter.

add_parameter(pl_id,'report_parameter',text_parameter,to_char(:text_item,'YYYY')

*/



  if :reports_control.output_choices = 'FILE' then
    var_reportpath := :global.report_directory || :reports_control.year_list;
    if :global.filename_suffix <> '_A10G' then
    	var_reportpath := var_reportpath || 'q' || :reports_control.quarter_list;
    end if;
    var_reportpath := var_reportpath || '_G' || :reports_control.group_list || '_';
  else
    	var_reportpath := '';
  end if;
 


I did change the formate mask here
but ' := 'p_year='||:reports_control.year_list; '
is alreay get year_list with to_char from
report group list.

report_otherparms := 'p_year='||:reports_control.year_list;
  if :global.filename_suffix <> '_A10G' then
  	report_otherparms := report_otherparms || '	p_quarter='||:reports_control.quarter_list;
  else
  	  report_otherparms := report_otherparms || '	p_quarter= 0';
  
  end if;

  report_otherparms := 'p_year='||:reports_control.year_list;
  if :global.filename_suffix <> '_A10G' then
  	report_otherparms := report_otherparms || '	p_quarter='||:reports_control.quarter_list;
  else
  	  report_otherparms := report_otherparms || '	p_quarter= 0';
  
  end if;
  
  report_otherparms := report_otherparms || ' P_REPORTGROUPID='||:reports_control.group_list;  

     
-- Check if reports are checked and submit to Reports Server 
	
 	for i in 1..35 loop
 		GET_PARAMETER_ATTR(pl_id,'id'||i,dummy,param_value);
 		split_loc := instr(param_value,'!');
 		checkbox_name := substr(param_value,1,split_loc-1);
 		report_basename := substr(param_value,split_loc+1,length(param_value));
    if CHECKBOX_CHECKED(checkbox_name) then
    	run_report := TRUE;
    	svc_name := substr(report_basename,1,4);
    	if svc_name = 'DISP' then
    		if not disp_exist then 
    			run_report := FALSE; 
    		end if;
    	elsif svc_name = 'ORTH' then
    		if not orth_exist then 
    			run_report := FALSE; 
    		end if;
    	elsif svc_name = 'HOSP' then
    		if not hosp_exist then
    			run_report := FALSE;
    		end if;
    	end if;
    	if run_report then
    			report_filename := :global.filename_prefix || report_basename || :global.filename_suffix;
    	GENERIC_ALERT(report_filename || ' ' || report_otherparms);
    		rep_status := GENERATE_REPORT(report_filename,:reports_control.output_choices,var_reportpath,report_otherparms);
    		rep_req := rep_req + 1;
    		if rep_status = 'FINISHED' then
    			rep_count := rep_count + 1;
    		end if;
    	end if;
  	end if;
 	end loop;


-- if we're in silent mode, display a completion message
	 	
 	if :reports_control.output_choices = 'FILE' and CHECKBOX_CHECKED('chk_silent') then
		SET_ALERT_PROPERTY('REPORT_CREATED',alert_message_text,
    	'' || rep_count || ' of ' || rep_req || ' reports created successfully');	
		dummy := SHOW_ALERT('REPORT_CREATED');
  end if;

ELSE
   dummy := SHOW_ALERT('select_report');
END IF;

END; 

[Updated on: Thu, 12 October 2006 20:50] by Moderator

Report message to a moderator

Re: Call report from a form - hits Invalid parameter Error [message #197991 is a reply to message #197544] Fri, 13 October 2006 12:23 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Thanks for your reply.

Now i am facing this :

Warning: The value of restricted LOV parameter P_YEAR
is not among the selectable values.


Even though i have exactly put the same
values at the both - Report parameter and Form parameter

What could be wrong still?
icon2.gif  Re: Call report from a form - hits Invalid parameter Error [message #197993 is a reply to message #197991] Fri, 13 October 2006 12:39 Go to previous messageGo to next message
chmlaeeque
Messages: 59
Registered: September 2006
Member
plz write that statement only where you are assigning value to p_year to report form, remove the remaining code
Re: Call report from a form - hits Invalid parameter Error [message #198001 is a reply to message #197993] Fri, 13 October 2006 13:42 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
First of all many thanks for your reply.


There are record_group created for all parameters
and that reocrd group has a 'p_year' parameter and its
values derived from : select distinct to_char(yr_no), to_char(yr_no) from authorized_utilization_s order by to_char(yr_no)


In the form there is procedure called: run_report
in that procedure i found the following area
for parameter assignment.


report_otherparms := 'p_year = '||:reports_control.year_list ;



Now the value in 'p_year = ' is already to_char from the select statement.

now on the other hand of report
i have a parameter p_year in the user define parameter
with the datatype of character.

Earlier it was giving me error : invalid value of parameter p_year , untill i remove a space from p_year parameter from the form.

And now i have a error of : Warning: The value of restricted LOV parameter Report_type is not among the selectable values.

Even though i have specified the same exact value at the both - report parameter as well as form parameter.


When, for the hack of it, i removed check box from restricted LOV parameter , it did not give any error and report runs
like for hours which usually turn up in two minitues.


So what is going on?


Thanking you again,



Re: Call report from a form - hits Invalid parameter Error [message #198091 is a reply to message #198001] Sat, 14 October 2006 12:31 Go to previous message
chmlaeeque
Messages: 59
Registered: September 2006
Member
no thanx for me plz,
i m probably also new to it but the problem you were facing was same as i had faced but i was in Developer 6i, never used the developer 10g. But the basic is same for all developers.

Since you have solve the problem for P_year for error facing value but try this for better use i think
select distinct to_char(yr_no,'YYYY') from authorized_utilization_s order by to_char(yr_no)

and be sure that the format mask in report parameter is also YYYY and data type and length is same both in report prameter and form parameter, change this in all where you want to select the P_year only there where you are introducing it or selecting it not in order by and else statements.
try hope the LOV problem will be solve automatically i think.
else sorry for more help bcz i didnt did that ever.
SORRY
Previous Topic: cancel query.............
Next Topic: Database Users LOV
Goto Forum:
  


Current Time: Sat Dec 03 22:01:17 CST 2016

Total time taken to generate the page: 0.07691 seconds