| Help required - user parameters( in Report builder) [message #82953] |
Tue, 22 July 2003 01:01  |
Rajeev Katyal
Messages: 55
Registered: April 2002
|
Member |
|
|
i am facing problem in designing one report(basically not able to perfectly write the query)
==================================
consider that i have table named
"invoice_hdr" with following fields
sih_sih_no varchar2(20)
sih_date date
sth_sth_no number
sih_qty number
sbp_sbp_code varchar2(6)
wdl_wdl_code varchar2(6)
pln_pln_code varchar2(2)
Now i have to design a report(Using report builder forms 4.5) showing data in following format
sih_sih_no sih_date sbp_sbp_code sth_sth_no
Have created following two user parameters
1)p_date
2)P_invoice
Have kept initial value of p_date as 'current date' and p_invoice as '%'
Means
..if the user provides some date in p_date , then it should show all invoices of that date.
..same way if the user selects some invoice no {Colum Name : Sih_Sih_no}, then it should show the details of that particular invoice only , regardless of the date
So i have written following query in report builder....
select
sih_sih_no,
sih_date,
sbp_sbp_code,
sth_sth_no
from invoice_hdr
where sih_date=:p_date and
sih_Sih_no=:p_invoice
It will produce correct result ..as if the user selects some , then it will show all the invoices of that date..coz the p_invoice paramter will be '%'
..but it will produce wrong result..if the user selects one invoice only..instead of the date...coz it will check for both where conditions and the first condition(of :p_date) may not match....coz user does not know ..the invoice date of that particular invoice.
..if i try , writting "or" instead of "and" then also the desired result will not be obtained.
So how do i go about it.
..some one suggested .."try calling report from forms....."..but Can't understand how to really go about it.
All suggestions welcome.
Regards,
Rajeev Katyal
|
|
|
|
| Re: Help required - user parameters( in Report builder) [message #82961 is a reply to message #82953] |
Tue, 22 July 2003 12:24  |
Ali
Messages: 88
Registered: March 2000
|
Member |
|
|
i think this will solve ur problem
create four parameter on report
v_date date
p_date date
v_inv
p_inv
go to after report parameter and code like this
if :v_date is null then
:p_date := ' where 1 = 1 ' ;
else :p_date := ' where invoice_hdr.sih_date = '||''''||:v_date||'''';
end if;
if :v_inv is null then
:p_inv := ' and 1 = 1 ' ;
else :p_inv := ' and invoice_hdr.sih_sih_no = ':v_inv;
end if;
after it go to query and add parameter like this
select
sih_sih_no,
sih_date,
sbp_sbp_code,
sth_sth_no
from invoice_hdr
&p_date
&p_inv
check it out.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
