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  |
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.
|
|
|
|
|
|
|
|
|
|
| Re: Call report from a form - hits Invalid parameter Error [message #197815 is a reply to message #197544] |
Thu, 12 October 2006 16:18  |
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 #198001 is a reply to message #197993] |
Fri, 13 October 2006 13:42 |
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 |
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
|
|
|
|
Goto Forum:
Current Time: Sat Feb 08 16:52:48 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
