OraFAQ Forum: Reports & Discoverer » Problem with Text Box (Text Item) on Reports parameter form in Reports 10g

Home » Developer & Programmer » Reports & Discoverer » Problem with Text Box (Text Item) on Reports parameter form in Reports 10g (Reports Builder version # 10.1.2.0.2 OS - Windows XP SP2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with Text Box (Text Item) on Reports parameter form in Reports 10g [message #291981]

Mon, 07 January 2008 06:09

shahmayur
Messages: 22
Registered: November 2007
Location: London, UK

Junior Member

I am facing a problem with text box (text item) on Reports parameter form in Reports 10g.

I have created a parameter (lets call it P_START) whose data type is date and it is being used in the query of the report. I am displaying this parameter on my parameter form to accept any valid date as an input from user.

Now to make life more easier for the user, I am populating SYSDATE as default value in P_START so that if the user wants to run the report for today's date, he doesn't have to type it. I have assigned SYSDATE to this parameter P_START in report's BEFORE PARAMETER FORM trigger.

When I run this report, the parameter form appears and the system date (SYSDATE) is populated in this field. Now if I change the date in this field
to any valid date, (for e.g. 31-Aug-2007 which I know its matching records exist in tables) and run the report, the repport is not showing any output meaning the query didn't fetch any records.

The good thing is I know the reason for this behavior. This is happening because even though I change the value of this date field, the actual value being passed to the query in report is not the one which I have entered but it is the default value (SYSDATE) which was populated in this field !! And the cause of this I found it in the source code of this Report Parameter form which (in Reports 9i/10g) is in fact a HTML page.

I got the HTML code of this parameter form by clicking on View -> Source menu item in IE browser. It reads like this:

<td colspan=5 rowspan=3><INPUT type=TEXT name="P_START" size=27 maxlength=11 value="07-Jan-2008"></td>

I suspect it is this (value="07-Jan-2008") piece of code which is causing this problem. This is because I am also displaying the value of this parameter in
the same report. And it shows the value as 07-Jan-2008 instead of 31-Aug-2007.

To prove my point, I removed the assignment of SYSDATE to this parameter P_START from the BEFORE PARAMETER FORM trigger. I then ran the report. This time in the parameter form, no value was populated in this text item. The HTML code was reading like this:

<td colspan=5 rowspan=3><INPUT type=TEXT name="P_START" size=27 maxlength=11 ></td>

You will immediately notice that the argument (value="07-Jan-2008") is not present in the above code.

I then entered the same date as before (which was 31-Aug-2007) and ran the report, it fetched the records !!

So my suspicion is correct. But any ideas why this is happening ? Is it a known issue or is it a bug ?

Please can anyone advise me how do I overcome this problem ?

My Reports Builder version is 10.1.2.0.2. It is running on Windows XP SP2.

Many thanks in advance.

Cheers

Mayur

Report message to a moderator

Re: Problem with Text Box (Text Item) on Reports parameter form in Reports 10g [message #291992 is a reply to message #291981]

Mon, 07 January 2008 06:43

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

I have assigned SYSDATE to this parameter P_START in report's BEFORE PARAMETER FORM trigger.

How did you do that?

I have created a sample report using Scott's schema with a simple query:

SELECT ename, hiredate
FROM emp
WHERE hiredate > :p_start

which automatically created a parameter. BEFORE-PARAMETER-FORM trigger was

function BeforePForm return boolean is
begin
  :p_start := sysdate;
  return (TRUE);
end;

and the report works correctly (it doesn't matter whether I type my own date or use a SYSDATE).

My Reports Builder version is 9.0.4.0.33.

Report message to a moderator

Re: Problem with Text Box (Text Item) on Reports parameter form in Reports 10g [message #292002 is a reply to message #291992]

Mon, 07 January 2008 07:18

shahmayur
Messages: 22
Registered: November 2007
Location: London, UK

Junior Member

Hi Littlefoot

Many thanks for your quick response.

I have also assigned the SYSDATE to the parameter in the BEFOREPFORM trigger exactly the same way as you have shown.

I am generating report by REPORT_OBJECT method. I have created REPORT_OBJECT method in the form and I am using RUN_REPORT_OBJECT to generate the report and then WEB.SHOW_DOCUMENT to show the report in the browser.

I am facing this problem using the method described above.

If you want I can post the actual code of my Forms for you here.

Cheers

Mayur

Report message to a moderator

Re: Problem with Text Box (Text Item) on Reports parameter form in Reports 10g [message #292659 is a reply to message #291981]

Wed, 09 January 2008 09:39

shahmayur
Messages: 22
Registered: November 2007
Location: London, UK

Junior Member

Just a quick update. There's good news from my side.

Thanks to the Oracle support, with the help from Metalink team, this problem has been resolved now.

Here's what BEFOREPARAMFORM trigger contained:

BEFORE (Original code)
===========
:P_START := TRUNC(SYSDATE);

AFTER (Modified code)
===========
IF :P_START IS NULL THEN
:P_START := TRUNC(SYSDATE);
END IF;

The justification for adding IF statement in BEFOREPARAMFORM trigger has been explained in the following link:

http://www.oracle.com/webapps/online-help/reports/10.1.2/topics/htmlhelp_rwbuild_hs/rwrefex/plsql/triggers/tr_before_param_form.htm

Cheers

Mayur

PS : One thing that I had forgotten to mention about this problem in my original post was that I was facing this problem ONLY when I call the report from Forms using Run_Report_Object method.

But if I run the report directly from the browser by entering the URL (for e.g. http://wt0001:8889/reports/rwservlet?server=my_repsrv&report=report1.rdf&desformat=pdf&destype=cache&userid=user1/pwd 1@db1&paramform=yes) into the address field, the report was working fine, meaning that the initial values were populated into the parameters on the parameter form and then later by changing the values in these parameters (text item), the report was displaying the results correctly.

So this problem was only occuring when I tried to run the report through Forms by creating the REPORT_OBJECT in the Form and using RUN_REPORT_OBJECT to generate the report and then using WEB.SHOW_DOCUMENT to publish the report in the browser.

Report message to a moderator

Re: Problem with Text Box (Text Item) on Reports parameter form in Reports 10g [message #292692 is a reply to message #292659]

Wed, 09 January 2008 11:57

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I'm glad you managed to solve the problem! Also, thank you for the feedback!

Report message to a moderator

Previous Topic:	Run Oracle report from Command line..
Next Topic:	Format Header

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 30 02:56:41 CST 2024