Home » Developer & Programmer » Forms » Pass parameters from form to report (Oracle forms & reports 10g)
Pass parameters from form to report [message #411562] Sun, 05 July 2009 07:05 Go to next message
Sarah Aman
Messages: 80
Registered: July 2009
Member

i am learning oracle let me tell my problem in oracle
i have made one simple form and one print button to display report in PDF format but when i am pressing print button it gives me wholee record but i want to print the same record which one i query for.i am poor in english i am sorry please any help



Sarah Aman

Eksaachadost@yahoo.com

[MERGED (again) by LF]

[Updated on: Tue, 07 July 2009 07:28] by Moderator

Report message to a moderator

Re: help me [message #411574 is a reply to message #411562] Sun, 05 July 2009 09:12 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I guess that you should pass some more information from form to report - a parameter which would uniquely identify this record. In a report, you'd then use this parameter in the WHERE clause - it would then restrict number (as well as contents) of the report output.
Re: help me [message #411595 is a reply to message #411562] Sun, 05 July 2009 21:16 Go to previous messageGo to next message
pentrg
Messages: 2
Registered: December 2008
Location: Jl.Jend.Sudirman Kav.1 Pa...
Junior Member

Example:

----------------------------------------------------------------
Select *
from table_name
where table_field_key_name=p_parameter
----------------------------------------------------------------


Information :
table_name -> The name of the table you want to query
table_field_key_name -> the key you want to uniquely identify this record (as Littlefoot said before)
p_parameter -> User Entry, you provide the value you want to uniquely identify this record from the form/report
Display record in report [message #411616 is a reply to message #411562] Mon, 06 July 2009 01:43 Go to previous messageGo to next message
Sarah Aman
Messages: 80
Registered: July 2009
Member

can anyone help me?
how can we display a particular record in report?
1) we should use select query?
OR
2) we should use triggers?


can anyone guide me please.
i hope so i will get prompt answer.

thanks in advance

Sarah Aman


Re: how can we display particular record in report [message #411617 is a reply to message #411616] Mon, 06 July 2009 01:48 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Dear User,

You should need to use "QUERY" or please give us more details about your requirement.

Re: how can we display particular record in report [message #411622 is a reply to message #411616] Mon, 06 July 2009 01:55 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Dear User,

Don't post the same question again and again.


NOTE: I merged the all your duplicate post.

Regards,
Taj

[Updated on: Mon, 06 July 2009 01:56]

Report message to a moderator

pass parameters form to report [message #411999 is a reply to message #411562] Tue, 07 July 2009 07:17 Go to previous messageGo to next message
Sarah Aman
Messages: 80
Registered: July 2009
Member

hi all,

how can we pass parameters form to report?
i am new in oracle!

i made one simple form with report and i made one print button
with trigger.(when-button-pressed) when i press the print button so its giving me whole data,i do not want whole information of the database i want to print particular record..
some one told me use query but i do not know where i place that query.

can anyone guide me how can we display the particular data in PDF format?



here is my print button code:

 DECLARE 
repid REPORT_OBJECT;
v_rep VARCHAR2(100);
rep_status VARCHAR2(20);
BEGIN
repid := FIND_REPORT_OBJECT('REPORT9');
v_rep := RUN_REPORT_OBJECT(repid);
rep_status := REPORT_OBJECT_STATUS(v_rep); 
WHILE rep_status in ('RUNNING','OPENING_REPORT','ENQUEUED') 
LOOP 
rep_status := report_object_status(v_rep); 
END LOOP; 
IF rep_status = 'FINISHED' THEN 
/*Display report in the browser*/ 
WEB.SHOW_DOCUMENT('http://sara:8889/reports/rwservlet/getjobid'|| 
substr(v_rep,instr(v_rep,'_',-1)+1)||'?'||'server=repserver','_blank'); 
ELSE 
message('Error when running report'); 
END IF;
PAUSE;
END; 


[Updated on: Tue, 07 July 2009 07:29]

Report message to a moderator

Re: pass parameters form to report [message #412064 is a reply to message #411999] Tue, 07 July 2009 10:47 Go to previous messageGo to next message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

hi,
First of all create a report with the following query
select * from emp
where deptno = :p_deptno

Here p_deptno is the parameter in you report. When you run this report, a parameter form will be displayed asking you to enter the deptno in the field. Depending on the deptno, your data in the report will be displayed.

Now create a form and create one field on the canvas to enter deptno. Create a button and write the code to run the report.

We pass the parameter from form to report by concatinating the name of the parameter in WEB.SHOW_DOCUMENT string.

Like

WEB.SHOW_DOCUMENT('http://sara:8889/reports/rwservlet/getjobid'||
substr(v_rep,instr(v_rep,'_',-1)+1)||'?'||'&paramform=no'||'&p_deptno='||:control.deptno||'server=repserver','_blank');

:control.deptno is the field on your form where you'll enter the value for deptno.
paramform=no will suppress the parameter form before displaying the report.

I hope it'll help.

Saadat Ahmad
Re: pass parameters form to report [message #412143 is a reply to message #412064] Tue, 07 July 2009 23:56 Go to previous messageGo to next message
Sarah Aman
Messages: 80
Registered: July 2009
Member

first of all

thanks for reply



its giving me error:


REP-52251: Cannot get output of job ID 81 you requested on Wed Jul 08 07:55:47 AST 2009.<P>REP-56033: Job 81 does not exist


can u guide me in proper way.
thanks in advance
Re: pass parameters form to report [message #412184 is a reply to message #412064] Wed, 08 July 2009 02:53 Go to previous messageGo to next message
Sarah Aman
Messages: 80
Registered: July 2009
Member

Dear sir


now my form is passing the parameter but by specify
value
by this: vParamValue number:=10;

i want to print those values which i execute from database
and when i press the print button, it print for me the same values which one i executed.

my new code is:

DECLARE
repid REPORT_OBJECT;
v_rep VARCHAR2(100);
rep_status VARCHAR2(20);
plid ParamList;
vParamValue number(10);
BEGIN

plid := Get_parameter_List('tmpdata');

IF NOT Id_Null(plid) THEN
Destroy_parameter_List( plid );
END IF;
plid := Create_parameter_List('tmpdata');
Add_parameter(plid,'P_deptno',TEXT_parameter,vParamValue);
Add_parameter(plid, 'PARAMFORM', TEXT_parameter, 'NO');


repid := FIND_REPORT_OBJECT('REPORT9');
v_rep := RUN_REPORT_OBJECT(repid,plid);
rep_status := REPORT_OBJECT_STATUS(v_rep);

WHILE rep_status in ('RUNNING','OPENING_REPORT','ENQUEUED')
LOOP
rep_status := report_object_status(v_rep);
END LOOP;
IF rep_status = 'FINISHED' THEN
/*Display report in the browser*/
WEB.SHOW_DOCUMENT('http://sara:8889/reports/rwservlet/getjobid'||
substr(v_rep,instr(v_rep,'_',-1)+1)||'?'||'server=repserver&P_deptno='||:deptno||'&paramform=no','_blank');
ELSE
message('Error when running report');
END IF;
PAUSE;
END;


sarah Aman
Re: pass parameters form to report [message #412234 is a reply to message #412184] Wed, 08 July 2009 05:51 Go to previous messageGo to next message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

your problem was to pass the parameters from form to report. It is resolved. Now I didn't understand your next problem.

i want to print those values which i execute from database
and when i press the print button, it print for me the same values which one i executed.

I don't have any idea from this sentence. Please explain it properly.
Re: pass parameters form to report [message #412254 is a reply to message #412234] Wed, 08 July 2009 06:40 Go to previous messageGo to next message
Sarah Aman
Messages: 80
Registered: July 2009
Member

dear sir


when i remove value from vparamvalue number :='10';(vparamvalue number;)
then it gives me blank report.
u asked for details its follow.


example:
we have so many data in database

1- deptno 10 2-loc new york
2- deptno 20 3-loc london
3- deptno 30 4-loc pakistan

etc etc

1- if i want to print only deptno 10 ,loc new york not with other data.
2- and now if i want to print only deptno 30,loc pakistan not with other data.


and in the report i want to display only those values which i executed in the form

i hope u got me. :


so what we will do for it?

sarah aman

[Updated on: Wed, 08 July 2009 07:01]

Report message to a moderator

Re: pass parameters form to report [message #412266 is a reply to message #412254] Wed, 08 July 2009 07:31 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Obviously, it is not enough to pass parameter value from form to report - you'll have to use this parameter in report's query (its WHERE clause).
Re: pass parameters form to report [message #412270 is a reply to message #412254] Wed, 08 July 2009 07:39 Go to previous messageGo to next message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

hi,
As Littlefoot suggested. You'll have to pass two parameters from form to report. One is for deptno and the second one is for Location.

Now in your report your query will be something like this
select something
from your_table
where deptno = :p_deptno
and loc = :p_loc

This will create two parameters in your report and when you pass the values of these two parameters from form to report, you'll get the desired output.
Re: pass parameters form to report [message #412284 is a reply to message #412270] Wed, 08 July 2009 08:50 Go to previous messageGo to next message
Sarah Aman
Messages: 80
Registered: July 2009
Member

dear sir

i followed you are instructions.But i got blank report nothing there
should i use anything else in the report?




Sarah Aman

here is my email id: eksaachadost@yahoo.com
Re: pass parameters form to report [message #412302 is a reply to message #412284] Wed, 08 July 2009 10:12 Go to previous messageGo to next message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

Nope.....you didn't follow the instructions. It was told to you before that you have to pass the value to report but it seems you are not passing any value to report. I'm attaching one file for you to download. Now download it and run the form. Give 20 in the deptno field and write DALLAS in the Location Field and press the button to run the report and check the output.

Now I hope that you'll be able to change some settings according to you in the code.

Here's the code:
DECLARE
  vrepid                report_object;
  vrep                  VARCHAR2 (100);
  v_format		varchar2(12);		--	PDF or SPREADSHEET
  v_show_document       VARCHAR2 (2000) := '/reports/rwservlet?';
  v_connect             VARCHAR2 (200)   := 'userid=abc/abc@test.world';
  v_report_server       VARCHAR2 (30)   := 'rep60server';
	v_report_name						varchar2(100);
BEGIN
	v_report_name := 'EMP_REP';
	v_format := 'PDF';
   IF GET_APPLICATION_PROPERTY (user_interface) = 'WEB'
   THEN
      v_show_document := v_show_document
         || v_connect
         -- Report server
         || '&server='
         || v_report_server
         -- Report name
         || '&report='||v_report_name
         -- Reports parameters
         || '&destype=CACHE'
         || '&desformat='||v_format
	       || '&paramform=no'
         || '&p_deptno='||:deptno
         || '&p_loc='||:loc
         ;
      web.show_document (v_show_document);
   ELSE
      vrepid := FIND_REPORT_OBJECT ('EMP_REP.rdf');
      vrep := RUN_REPORT_OBJECT (vrepid);
      web.show_document
                (   'it-saadat:8889/reports/rwservlet/getjobid'
                 || SUBSTR (vrep, INSTR (vrep, '_', -1) + 1)
                 || '?'
                 || 'server=rep60server',
                 '_blank'
                );
   END IF;
END;

change the value of v_connect variable, v_report_server variable. Replace it-saadat with sara and I think it's done.

Note : My forms version is Forms [32 Bit] Version 10.1.2.0.2 (Production)

regards,
Saadat Ahmad
  • Attachment: EMP_REP.rar
    (Size: 20.03KB, Downloaded 268 times)

[Updated on: Wed, 08 July 2009 10:16]

Report message to a moderator

Re: pass parameters form to report [message #412309 is a reply to message #412302] Wed, 08 July 2009 11:04 Go to previous messageGo to next message
Sarah Aman
Messages: 80
Registered: July 2009
Member

thanks alot

i was using 10G v-10.1.2 before but cause of server failure i installed 10G v-9.0

i worked on the server to be started but i did not find any way to start the server

i used this command in cmd

rwserver server=server101 autostart=yes

but it did not work for me
if u know the way that how i can start the server so please share with me.

anyways thanks alot


Re: pass parameters form to report [message #412458 is a reply to message #412309] Thu, 09 July 2009 08:46 Go to previous messageGo to next message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

so now there is a new problem.

rwserver server=server101 start

try this command and what is the message you are getting? Search this forum for how to start the report server.
Re: pass parameters form to report [message #412460 is a reply to message #412458] Thu, 09 July 2009 09:28 Go to previous messageGo to next message
Sarah Aman
Messages: 80
Registered: July 2009
Member

thanks for reply

i tried the same way but i did not start the server its showing me in the small box that you are server is ready but server is not starting in form 10G v-10.1.2.
but v-9.0.4 is working fine.


okay thanks alot for everything

Sarah Aman
Re: pass parameters form to report [message #412657 is a reply to message #412460] Fri, 10 July 2009 06:50 Go to previous messageGo to next message
Sarah Aman
Messages: 80
Registered: July 2009
Member

thanks alot i solved the problem now its working
Re: pass parameters form to report [message #412660 is a reply to message #412657] Fri, 10 July 2009 07:18 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What did you do to solve the problem? Would you mind to share the solution, so that other forum members learn something from you?
Re: pass parameters form to report [message #412678 is a reply to message #412660] Fri, 10 July 2009 09:57 Go to previous messageGo to next message
Sarah Aman
Messages: 80
Registered: July 2009
Member

i just added this
add_parameter(plid,'P_empno',text_parameter,to_char(:emp.empno));

and it passed the parameters form to report.And it works fine.



sarah Aman

[Updated on: Fri, 10 July 2009 10:40]

Report message to a moderator

Re: pass parameters form to report [message #413944 is a reply to message #412678] Sun, 19 July 2009 09:56 Go to previous messageGo to next message
sazzadur
Messages: 9
Registered: January 2009
Location: UK
Junior Member
Mr Sadat provided the following code:


DECLARE
vrepid report_object;
vrep VARCHAR2 (100);
v_format varchar2(12); -- PDF or SPREADSHEET
v_show_document VARCHAR2 (2000) := '/reports/rwservlet?';
v_connect VARCHAR2 (200) := 'userid=abc/abc@test.world';
v_report_server VARCHAR2 (30) := 'rep60server';
v_report_name varchar2(100);
BEGIN
v_report_name := 'EMP_REP';
v_format := 'PDF';
IF GET_APPLICATION_PROPERTY (user_interface) = 'WEB'
THEN
v_show_document := v_show_document
|| v_connect
-- Report server
|| '&server='
|| v_report_server
-- Report name
|| '&report='||v_report_name
-- Reports parameters
|| '&destype=CACHE'
|| '&desformat='||v_format
|| '&paramform=no'
|| '&p_deptno='||:deptno
|| '&p_loc='||:loc
;
web.show_document (v_show_document);
ELSE
vrepid := FIND_REPORT_OBJECT ('EMP_REP.rdf');
vrep := RUN_REPORT_OBJECT (vrepid);
web.show_document
( 'it-saadat:8889/reports/rwservlet/getjobid'
|| SUBSTR (vrep, INSTR (vrep, '_', -1) + 1)
|| '?'
|| 'server=rep60server',
'_blank'
);
END IF;
END;

So, Sarah Aman, where did you use
add_parameter(plid,'P_empno',text_parameter,to_char(:emp.empno)) ??
Re: Pass parameters from form to report [message #413946 is a reply to message #411562] Sun, 19 July 2009 10:25 Go to previous messageGo to next message
sazzadur
Messages: 9
Registered: January 2009
Location: UK
Junior Member
When I run a report in web from report builder, it shows in the browser as follows:
C:\Temp\docroot\MODULE5006005276.htm.
I never find or get 'http://host:8889/reports/rwservlet/'..
If I want to use web.show_document, how can I find reports service? I am confused.
I installed developer 10g, where I can start Form service. But I never find Report server seperately.

Re: Pass parameters from form to report [message #414633 is a reply to message #413946] Thu, 23 July 2009 01:21 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I believe they both use OC4J. Have you run a form yet? Have you run the Reports Builder yet?

David
Previous Topic: External tools for Oracle Forms.
Next Topic: Run Report 10g from Form 10g
Goto Forum:
  


Current Time: Fri Dec 09 05:40:32 CST 2016

Total time taken to generate the page: 0.06374 seconds