Home » Developer & Programmer » Reports & Discoverer » Passing Parameter From Form to Report (Oracle 9i, Developer 6i, Report Builder 6i)
Passing Parameter From Form to Report [message #378556] Tue, 30 December 2008 23:32 Go to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Hi,
I am working on a form where i need to print a Sales Order Report. To do that i am calling a predifined report after presseing the Save button. I have written the following code to run the report along with the parameter passing through form:

declare
	al_button number;	
	pl_id ParamList;

begin
	pl_id := Get_Parameter_List('repPara'); --create a parameter list
	IF NOT Id_Null(pl_id) THEN 
		Destroy_Parameter_List( pl_id ); 
	END IF; 
	
	pl_id := Create_Parameter_List('repPara'); 
	Add_Parameter(pl_id, 'PARAMFORM',TEXT_PARAMETER, 'NO');
	Add_Parameter(pl_id, 'P_DO_NO',TEXT_PARAMETER, :Master_SALES.Do_Number); --passing inv_no as parameter
	Add_Parameter(pl_id, 'P_DO_DATE',TEXT_PARAMETER, :MAster_SALES.Do_DATE); --passing inv_date as parameter
	
	Run_Product(REPORTS, 'E:\Do_dcmbl Forms\REPORTS\DO_Food_Portrait', SYNCHRONOUS, RUNTIME, FILESYSTEM, pl_id, NULL);
end;


But the problem is that the report is showing nothing, it seems that parameter value doesn't match with the existing data. What to do now?
Re: Passing Parameter From Form to Report [message #378571 is a reply to message #378556] Wed, 31 December 2008 00:18 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What happens when you run the report (from Reports Builder), using the same parameters' values as the ones passed from a form (do_number and do_date)?

What happens when you take the report's query out from the report and run it in SQL*Plus, again with the same values?
Re: Passing Parameter From Form to Report [message #378574 is a reply to message #378556] Wed, 31 December 2008 00:30 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Thanks for the reply. Report is running using the same parameter values but not showing any data, such that the parameter values doesn't match with the existing values stored in the DB. For your convinient i am attaching the .rdf file which i am calling from form. Please take a look and try to give a suggestion. Thanks in advance.
Re: Passing Parameter From Form to Report [message #378578 is a reply to message #378556] Wed, 31 December 2008 00:38 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

When I am runnign the report from report builder,its workking fine along with the parameter list. but the problem arise when i an calling the report from the form along with the passing parameters from form.
Re: Passing Parameter From Form to Report [message #378585 is a reply to message #378578] Wed, 31 December 2008 00:50 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Do parameter names and datatypes match (in a form and in a report)? Try to display passed parameter values in a report (put them anywhere; margin, to the top of a report so - if nothing appears, parameter values will; doing so, you'll be able to see what's being passed).
Re: Passing Parameter From Form to Report [message #378627 is a reply to message #378556] Wed, 31 December 2008 02:32 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Hi,
I have passed the parameter values into the report and its working fine. but yet no result is showing, only the report is running, The same thing happen when the parameter values doesn't match with the DB values.
Re: Passing Parameter From Form to Report [message #378630 is a reply to message #378627] Wed, 31 December 2008 02:44 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So it appears that there are no records in the database which satisfy the condition made up by these parameters.

Once again: did you run the same query in SQL*Plus? What was the result?
Re: Passing Parameter From Form to Report [message #378651 is a reply to message #378556] Wed, 31 December 2008 04:01 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Hi,
The problem is in the parameter P_DO_DATE i have assign the date format as DD-MM-YYYY. and from the form i am sending the :MASTER_SALES.DO_DATE as a DD-MM-YYYY, While sending the parameter value to the report it shows 12-31-0008. What to do now, It showing something which i didn't write anywhere.
Re: Passing Parameter From Form to Report [message #378661 is a reply to message #378651] Wed, 31 December 2008 04:31 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I have already asked whether
LF
parameter names and datatypes match (in a form and in a report)?

What datatypes do you use? If you have chosen to work with characters instead of dates, you'll have to explicitly convert strings into dates by using the TO_DATE function.
Re: Passing Parameter From Form to Report [message #378672 is a reply to message #378556] Wed, 31 December 2008 04:48 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Thanks, But I have checked it couple of times and yet couldn't found the errors, i am sure about it that i have the same data type both in report and in the form.
Re: Passing Parameter From Form to Report [message #378675 is a reply to message #378672] Wed, 31 December 2008 04:58 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK; you could, though, really try to pass date value as character (take care about the format in form so that you could easily convert it in a report's WHERE clause by using the TO_DATE function) - just to check whether there's the result or not.

By the way, you still didn't answer the question regarding the result this query returns in SQL*Plus.

Yet another idea: do dates in a table you are selecting from contain time part? For example, 31.12.2008 (dd.mm.yyyy) - is it really truncated to date, or is it 31.12.2008 07:14:33 (dd.mm.yyyy hh24:mi:ss)? If so, it might be the problem. In that case, you'll have to TRUNC database value (or figure another way out).
Re: Passing Parameter From Form to Report [message #378678 is a reply to message #378556] Wed, 31 December 2008 05:12 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Which Queries? The one that i have used in report? I am really having fun working on such a silly mistakes don't know where and yet no results, but i truely appreciate your effort on helping me. Let me show you the queries that i have written for the report.
SQL> SELECT DISTINCT vsf.MRR_NO, vsf.MRR_DATE, vsf.DO_NO, 
  2  vsf.DO_DATE, vsf.DEPO_NAME, vsf.DISTRIBUTOR_NAME, vsf.ADDRESS
  3  FROM V_DO_SALES vsf
  4  WHERE (vsf.DO_NO = :P_DO_NO
  5   AND vsf.DO_DATE = :P_DO_DATE);
Bind variable "P_DO_DATE" not declared.
SQL> /
Bind variable "P_DO_DATE" not declared.
SQL> 
SQL> 
SQL> SELECT DISTINCT vsf.PRODUCT_QUANTITY, vsf.PIECES, 
  2  vsf.PRODUCT_NAME, vsf.SL_NO, vsf.INV_RATE, vsf.SALES_AMOUNT
  3  FROM V_DO_SALES vsf
  4  WHERE (vsf.DO_NO = :P_DO_NO
  5   AND vsf.DO_DATE = :P_DO_DATE);
Bind variable "P_DO_DATE" not declared.
SQL> SELECT DISTINCT NUMTOCHAR_PACK(SUM(VSF.PRODUCT_QUANTITY)) DISPLAY_COL1, NUMTOCHAR_PIECES(SUM(VS
F.PIECES)) DISPLAY_COL2, 
  2  SUM(VSF.PIECES) DISPLAY_COL3
  3  FROM V_DO_SALES VSF
  4  WHERE (VSF.DO_DATE = :P_DO_DATE
  5   AND VSF.DO_NO = :P_DO_NO);
Bind variable "P_DO_NO" not declared.
SQL> 

Guess you are looking for this. Otherwise let me know.
Thanks
Re: Passing Parameter From Form to Report [message #378680 is a reply to message #378678] Wed, 31 December 2008 05:22 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, these are the queries taken from the report. Now substitute colon signs (:) with ampersands (&) and - when prompted - enter values you pass from a form in order to get the result.

Because, "bind variable not declared" isn't exactly the result which might help solving the problem.
Re: Passing Parameter From Form to Report [message #378681 is a reply to message #378556] Wed, 31 December 2008 05:30 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Well, I have got three queries after varifying the query No. 1 I have got the following errors:
SQL> SELECT DISTINCT vsf.MRR_NO, vsf.MRR_DATE, vsf.DO_NO, 
  2  vsf.DO_DATE, vsf.DEPO_NAME, vsf.DISTRIBUTOR_NAME, vsf.ADDRESS
  3  FROM V_DO_SALES vsf
  4  WHERE (vsf.DO_NO = &P_DO_NO
  5   AND vsf.DO_DATE = &P_DO_DATE); 
Enter value for p_do_no: 114
old   4: WHERE (vsf.DO_NO = &P_DO_NO
new   4: WHERE (vsf.DO_NO = 114
Enter value for p_do_date: 31-12-2008
old   5:  AND vsf.DO_DATE = &P_DO_DATE)
new   5:  AND vsf.DO_DATE = 31-12-2008)
 AND vsf.DO_DATE = 31-12-2008)
                 *
ERROR at line 5:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Re: Passing Parameter From Form to Report [message #378683 is a reply to message #378681] Wed, 31 December 2008 05:34 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
p_do_date should be TO_DATE('31.12.2008', 'dd.mm.yyyy')

If you don't know how to make it work using variables, hard code these values!
Re: Passing Parameter From Form to Report [message #378714 is a reply to message #378556] Wed, 31 December 2008 13:09 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Hi,
I just wanted to mentioned that, with your great support i was able to solved the problem. I am showing my full gratitude to you and all the other members of orafaq.com/forum.
Quote:
PROBLEM SOLVED. FULL CREDIT TO LITTLEFOOT.
Re: Passing Parameter From Form to Report [message #401531 is a reply to message #378556] Tue, 05 May 2009 06:44 Go to previous message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
This topic is very usefull for me also Thank you little foot


Regards


Shahzaib Ismail
Previous Topic: rwrun error - ORA-01012: not logged on
Next Topic: discoverer administrator
Goto Forum:
  


Current Time: Sun Dec 11 02:38:57 CST 2016

Total time taken to generate the page: 0.08076 seconds