Home » Developer & Programmer » Forms » Help required - user parameters( in Report builder)
Help required - user parameters( in Report builder) [message #82953] Tue, 22 July 2003 01:01 Go to next message
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 Go to previous messageGo to next message
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.
Re: Help required - user parameters( in Report builder) [message #82965 is a reply to message #82953] Tue, 22 July 2003 22:41 Go to previous message
Su
Messages: 154
Registered: April 2002
Senior Member
U can make use of decode.. in the query...
Previous Topic: Tabs In Oracle Forms 6i
Next Topic: Message Box
Goto Forum:
  


Current Time: Thu Mar 28 13:08:17 CDT 2024