How do i display PDF Report Tiltle to be dynamic using oracle reports builder

Hi
I need  report pdf name to change dynamically based on the user criteria.
I have three input parameters for my report like below
P_merch_nbr_in
P_term_id_in
P_date_in

my requirement is
if user enters P_merch_nbr_in ( eg merch_nbr : '9999999999 ' ), i shoud genrate a pdf file with pdf name like  ABC_9999999999.pdf

if user enters P_term_id_in ( Eg term_id 'LK1532 '),i shoud genrate a pdf file with name like   ABC_LK1523.pdf

if user enters P_date_in  ('10/05/10'), i shoud have report pdf name like ABC_10/05/10.pdf

I am using oracle reports builder 10g verison to produce report PDFS

Please help me to achive the above requirement.

Thanks in advance
srinivasoracleAsked:
Who is Participating?
 
MilleniumaireCommented:
If you're using rwclient then this implies that a Reports Server is being used to control the running of reports.  This is the usual situation for Reports 10g.

In this case if you want to set the name of the report output file then your report must dynamically create a report distribution xml file containing the name of the file to be created.

See the following link for info on Distribution XML files if you aren't familiar with them:

http://download.oracle.com/docs/cd/E12839_01/bi.1111/b32121/pbr_dist004.htm

The way I have done this myself is as follows:

Build a Before Parameter Form trigger in your report and include the following code to create the distribution xml file:

FUNCTION BeforePForm return boolean is

  dst_file   text_io.file_type;
  filename varchar2(100);
BEGIN
/*

  Put your logic here to set the name of the file in variable: filename
  This will be written to the distribution xml file.

*/

  dst_file := text_io.fopen('/mypath/myfile.xml','w');
 
  text_io.putf(dst_file, '<destinations>');
  text_io.putf(dst_file, '<file id="a1" name="'||filename||'" format="pdf">');
  text_io.putf(dst_file, '<include src="Report" />');
  text_io.putf(dst_file, '</file>');
  text_io.putf(dst_file, '</destinations>');
 
  text_io.fclose(dst_file);
 
  return(true);
END;

When calling your report you must include the following additional parameters:

    distribute=yes
    destination=/mypath/myfile.xml
   
Note: The destination parameter identifies to Oracle Reports where the distribution xml file will exist.  The code in the Before Parameter Form that creates this file must use the same path and filename.  To this end, in my reports I actually create a user parameter in the form called DESTINATION and then reference this in the code:
     
      dst_file := text_io.fopen(:DESTINATION,'w');

This ensures that the value of the DESTINATION parameter set on the report command line is the same as is used to create the distribution xml file.
     
The above workaround (dynamically creating a distribution xml file) is not required when using Reports 11g Release 1 as Oracle have finally got around to fixing the inability to set the filename using DESNAME (as was originally possible using rwrun in earlier versions of reports).

I hope this helps you.
0
 
srinivasoracleAuthor Commented:
Please respond asap
0
 
k_murli_krishnaCommented:
REPORTS TEMPLATES: GETTING THE MOST OUT OF ORACLE REPORTS
http://www.dulcian.com/papers/ECO/2000/2000_ECO-SEOUC_ReportTemplates.htm
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
srinivasoracleAuthor Commented:
The posting Didn't help me
0
 
MilleniumaireCommented:
Can you confirm you are using rwclient to run the reports and not rwrun, which was generally used with Reports 9i.
0
 
srinivasoracleAuthor Commented:
HI Milleniumaire:
I tried the above solution with my addings shown below,but throws me an error msg like
REP:0738 :only a function is allowed here
Please help me
---MY Report Triger  BEFORE PARAMETER FORM CODE is shown below
FUNCTION BeforePForm return boolean is
  dst_file      text_io.file_type;
  filename     varchar2(100);
  v_distxml    VARCHAR2(20);
BEGIN
    v_distxml:=c:\myfile.xml;      
IF :P_FROM_DATE_IN IS NOT NULL AND :P_TO_DATE_IN IS NOT NULL AND :P_MERCH_NBR_IN IS NOT NULL THEN
 filename  :='ABC'||'_'||:P_MERCH_NBR_IN||'.'||'.PDF';
ELSIF :P_FROM_DATE_IN IS NOT NULL AND :P_TO_DATE_IN IS NOT NULL AND :P_TERM_ID_IN IS NOT NULL THEN
 filename :='ABC'||'_'||:P_TERM_ID_IN||'.'||'.PDF';                
ELSIF :P_FROM_DATE_IN IS NOT NULL AND :P_TO_DATE_IN IS NOT NULL AND :P_SENSE_LVL_IN IS NOT NULL THEN
 filename :='ABC'||'_'||:P_SENSE_LVL_IN||'.'||'.PDF';
  END IF;            
  dst_file := text_io.fopen(v_distxml,'w');  
  text_io.putf(dst_file, '<destinations>');
  text_io.putf(dst_file, '<file id="a1" name="'||filename||'" format="pdf">');
  text_io.putf(dst_file, '<include src="Report" />');
  text_io.putf(dst_file, '</file>');
  text_io.putf(dst_file, '</destinations>');
  text_io.fclose(dst_file);
  return(true);
END;

0
 
srinivasoracleAuthor Commented:
HI Milleniumaire:
I tried the above solution with my addings shown below,but throws me an error msg like
REP:0738 :only a function is allowed here
Please help me
---MY Report Triger  BEFORE PARAMETER FORM CODE is shown below
FUNCTION BeforePForm return boolean is
  dst_file      text_io.file_type;
  filename     varchar2(100);
  v_distxml    VARCHAR2(20);
BEGIN
    v_distxml:=c:\myfile.xml;      
IF :P_FROM_DATE_IN IS NOT NULL AND :P_TO_DATE_IN IS NOT NULL AND :P_MERCH_NBR_IN IS NOT NULL THEN
 filename  :='ABC'||'_'||:P_MERCH_NBR_IN||'.'||'.PDF';
ELSIF :P_FROM_DATE_IN IS NOT NULL AND :P_TO_DATE_IN IS NOT NULL AND :P_TERM_ID_IN IS NOT NULL THEN
 filename :='ABC'||'_'||:P_TERM_ID_IN||'.'||'.PDF';                
ELSIF :P_FROM_DATE_IN IS NOT NULL AND :P_TO_DATE_IN IS NOT NULL AND :P_SENSE_LVL_IN IS NOT NULL THEN
 filename :='ABC'||'_'||:P_SENSE_LVL_IN||'.'||'.PDF';
  END IF;            
  dst_file := text_io.fopen(v_distxml,'w');  
  text_io.putf(dst_file, '<destinations>');
  text_io.putf(dst_file, '<file id="a1" name="'||filename||'" format="pdf">');
  text_io.putf(dst_file, '<include src="Report" />');
  text_io.putf(dst_file, '</file>');
  text_io.putf(dst_file, '</destinations>');
  text_io.fclose(dst_file);
  return(true);
END;

0
 
srinivasoracleAuthor Commented:
HI Milleniumaire:
I tried the above solution with my addings shown below,but throws me an error msg like
REP:0738 :only a function is allowed here
Please help me
---MY Report Triger  BEFORE PARAMETER FORM CODE is shown below
FUNCTION BeforePForm return boolean is
  dst_file      text_io.file_type;
  filename     varchar2(100);
  v_distxml    VARCHAR2(20);
BEGIN
    v_distxml:=c:\myfile.xml;      
IF :P_FROM_DATE_IN IS NOT NULL AND :P_TO_DATE_IN IS NOT NULL AND :P_MERCH_NBR_IN IS NOT NULL THEN
 filename  :='ABC'||'_'||:P_MERCH_NBR_IN||'.'||'.PDF';
ELSIF :P_FROM_DATE_IN IS NOT NULL AND :P_TO_DATE_IN IS NOT NULL AND :P_TERM_ID_IN IS NOT NULL THEN
 filename :='ABC'||'_'||:P_TERM_ID_IN||'.'||'.PDF';                
ELSIF :P_FROM_DATE_IN IS NOT NULL AND :P_TO_DATE_IN IS NOT NULL AND :P_SENSE_LVL_IN IS NOT NULL THEN
 filename :='ABC'||'_'||:P_SENSE_LVL_IN||'.'||'.PDF';
  END IF;            
  dst_file := text_io.fopen(v_distxml,'w');  
  text_io.putf(dst_file, '<destinations>');
  text_io.putf(dst_file, '<file id="a1" name="'||filename||'" format="pdf">');
  text_io.putf(dst_file, '<include src="Report" />');
  text_io.putf(dst_file, '</file>');
  text_io.putf(dst_file, '</destinations>');
  text_io.fclose(dst_file);
  return(true);
END;

0
 
MilleniumaireCommented:
When assigning a string value to a variable it must be included in quotes.  I'm guessing that this is what the error message refers to:

v_distxml:=c:\myfile.xml;

should be:

v_distxml:='c:\myfile.xml';
0
 
MilleniumaireCommented:
PS: Sorry for not getting back to you sooner, but it looks like we are in different time zones.  I'm in England so have only just started work, which is where I tend to monitor Experts Exchange from.

The time here is 9:00am on Wednesday morning.
0
 
srinivasoracleAuthor Commented:
Milleniumaire,
i implemented the below suggestion and i was able to fix the error
REP:0738 :only a function is allowed here. i was having problem in setting the below two parameters
in report builder 10.1.2.0.2
    distribute=yes
    destination=/mypath/myfile.xml
and also i am running report in pdf format using report builder file menu file-->generate to file--PDF
please help in this.
your suggestion
--------------------
When assigning a string value to a variable it must be included in quotes.  I'm guessing that this is what the error message refers to:
v_distxml:=c:\myfile.xml;
should be:
v_distxml:='c:\myfile.xml';

0
 
srinivasoracleAuthor Commented:
Milleniumaire,
i implemented the below suggestion and i was able to fix the error
REP:0738 :only a function is allowed here. i was having problem in setting the below two parameters
in report builder 10.1.2.0.2
    distribute=yes
    destination=/mypath/myfile.xml
and also i am running report in pdf format using report builder file menu file-->generate to file--PDF
please help in this.
your suggestion
--------------------
When assigning a string value to a variable it must be included in quotes.  I'm guessing that this is what the error message refers to:
v_distxml:=c:\myfile.xml;
should be:
v_distxml:='c:\myfile.xml';

0
 
srinivasoracleAuthor Commented:
Milleniumaire,
i implemented the below suggestion and i was able to fix the error
REP:0738 :only a function is allowed here. i was having problem in setting the below two parameters
in report builder 10.1.2.0.2
    distribute=yes
    destination=/mypath/myfile.xml
and also i am running report in pdf format using report builder file menu file-->generate to file--PDF
please help in this.
your suggestion
--------------------
When assigning a string value to a variable it must be included in quotes.  I'm guessing that this is what the error message refers to:
v_distxml:=c:\myfile.xml;
should be:
v_distxml:='c:\myfile.xml';

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.