Re: Forms + Reports problems...

From: Peter H. Larsen <plen_at_novo.dk>
Date: 1997/10/16
Message-ID: <34465212.9808C82F_at_novo.dk>


Hi Tina,
[Quoted] Just a short note - I've used 2000+ characters in Reports 2.5 parameters plenty [Quoted] of times. BUT - the default value has to be the MAX length of the field. So I see [Quoted] the problem as having a too little default WHERE and FROM clause. Btw. I have [Quoted] used the same contruct several of times, and I've had from and especially WHERE [Quoted] clauses very very large.

I don't agree about it's being necessary to parse files between programs for [Quoted] parameters. But Reports 2.5 can be somekind of a strange fellow sometimes.

[Quoted] So if Donald would use the longest possible value for the default value of [Quoted] FROMCLAUSE and WHERECLAUSE I would be very surprised if it didn't work. And [Quoted] Donald has to remember to set the size for the parameter to ei. 2000. A guide [Quoted] would be, to make a where-clause that will NOT succeed at any time (but it's [Quoted] long), and a from clause where the ALIAS of the table is prefixed with a lot of spaces.

Regards
Peter

Tina Tran wrote:

> Are you sure that your parameters are not being chopped off? Could it be
> that
> your where clause are too long? Report only allows a string less than 256
> bytes
> to be passed as a parameter...I encountered the same problem. We
> implemented the work around by writing the where clause to a text file
> using text_io package. Pass the file name to the report as a parameter and
> have the report open up the file name to get the where clause.
>
> Donald Raymond Lloyd II <don_at_copland.udel.edu> wrote in article
> <61k2b5$r3i$1_at_copland.udel.edu>...
> >
> >
> > I've got a forms 4.5 application that I'm using to print or preview a
 reports
> > 2.5 document. My first problem with reports is that I want the user to
 be
> > able to click a button and print or preview a report without having to go
 

> > through the paramater entry screen. This works when I'm sending the file
 

> > directly to the printer in batch mode, but for previews, the screen comes
 

> > up even though I pass a parameter setting PRINTJOB to 'NO'. Sometimes
> > when the form comes up, it appears _behind_ the form that called it; then
 

> > the user never sees it and clicks the button a few more times wondering
> > why it's not working...
> >
> > The second problem occurs when passing parameters to a report. In my
> > application I have a main data entry/edit form and a 'Filter' form that's
 

> > used to essentially build the 'where' and 'from' clauses for a query
> > that's used to populate the main form. There's also a report form that
> > gets called & is passed the 'where' and 'from' values. From the report
> > form, the user selects a field to order by from a list box and enters a
> > description of the report to be printed in its header.
> >
> > The report is passed four user-defined parameters, all strings:
 FROMCLAUSE,
> > WHERECLAUSE, ORDERBY, and COMMENT. The query that the report is based on
 

> > looks like this:
> >
> >
> > select DECODE(ACTIVE, 'Y', 'YES', 'N', 'NO') Status
> > , ARCHIVE_DATE
> > , decode( BOND_WAIVER, 'Y', 'B', null) BOND_WAIVER
> > , decode( INV_WAIVER, 'Y', 'I', null) INV_WAIVER
> > , decode( SURETY_WAIVER, 'Y', 'S', null) SURETY_WAIVER
> > , decode( ACCT_WAIVER, 'Y', 'A', null) ACCT_WAIVER
> > , CREATION_DATE
> > , rtrim(ltrim( CREATOR_LNAME || ' ' || CREATOR_FI || ' ' ||
> > CREATOR_MI)) Creator
> > , hc.description HowCreated
> > , LAST_ACT_DATE
> > , ORIG_PRINC
> > , tr.TRUST_ID
> > , TRUST_NUMBER
> > ,cv.amount currentvalue
> > from TRUSTS tr, how_created hc, currentvalues cv
> > where t.trust_id in (select distinct t.trust_id from &fromclause
> > where &whereclause)
> > and tr.how_created = hc.hc_id (+)
> > and tr.trust_id = cv.trust_id (+)
> > order by &orderby;
> >
> >
> > FROMCLAUSE and WHERECLAUSE default to 'trusts t' and 't.trust_id is not
 null'
> > in the case where no specific filter criteria has been specified. If the
 

> > report is run with the defaults, it happily churns out several hundred
 pages
> > of data. However, as soon as I specify a filter, thus changing
 WHERECLAUSE
> > and sometimes FROMCLAUSE, then try to look at a report, Reports Server
> > starts up but nothing prints, the parameters screen doesn't pop up, and
> > nothing shows up in the reports server log. Trying to print in batch
 mode
> > also seems to do nothing.
> >
> > I've used the debugger to check the values of the parameters just before
> > run_product is called - they're valid. I've verified that the sizes of
> > the paramters are declared the same on both sides. The query that's
 built
> > is a valid one; I know this because (a) a similar query has been built to
 

> > successfully populate the main form, (b) if I paste the above query into
> > SQLPlus, substituting the paramters appropriately, and run the query, it
> > returns the expected values, and (c) if I run the report manually from
> > the designer and paste the appropriate values into the paramter entry
> > screen, the report runs without a hitch. It's only when called from
 Forms
> > that the problem occurs...
> >
> > Any ideas?
> >
> > --
> > Don Lloyd don_at_udel.edu
> > Visit Six In A Mustang http://www.geocities.com/MotorCity/Downs/8895
> >
> >
Received on Thu Oct 16 1997 - 00:00:00 CEST

Original text of this message