Home » Developer & Programmer » Reports & Discoverer » Using a parameter for Maximum rows to fetch (Oracle Report Builder 10.1.2.0.2)
Using a parameter for Maximum rows to fetch [message #486412] Wed, 15 December 2010 07:34 Go to next message
davholla
Messages: 116
Registered: August 2009
Location: London
Senior Member
In Report it is possible to set the maximum rows to fetch to be a number e.g. 1+.
Is it possible to use a number that is selected from another query?

When I try to do this i.e. I put in the field num_lines or :num_lines I just get invalid number
Re: Using a parameter for Maximum rows to fetch [message #486487 is a reply to message #486412] Thu, 16 December 2010 00:51 Go to previous messageGo to next message
Littlefoot
Messages: 19525
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Check SRW.SET_MAXROW built-in (in Reports Builder online documentation). This is a procedure that sets the maximum number of records to be fetched for the specified query.
Re: Using a parameter for Maximum rows to fetch [message #486552 is a reply to message #486487] Thu, 16 December 2010 08:44 Go to previous messageGo to next message
davholla
Messages: 116
Registered: August 2009
Location: London
Senior Member
Thanks a lot for that. Here is the before reports trigger where should I put
SRW.SET_MAXROW('Q_1',1);


Function BeforeReport return boolean is

begin

 

BEGIN

 

  -- srw.do_sql('alter session set sql_trace = true');

  select displayed_field

  into   :C_continued

  from   po_lookup_codes

  where  lookup_type = 'POXPRPOP'

  and    lookup_code = 'CONTINUED';

 

  select displayed_field

  into   :C_address_at_top

  from   po_lookup_codes

  where  lookup_type = 'POXPRPOP'

  and    lookup_code = 'ADDRESS AT TOP';

/* Bug 1484478

  The call to initialize is being made in after parameter value trigger and it

  is not necessary to do it here again

  SRW.USER_EXIT('FND SRWINIT');

*/

 

/* Change the :P_single_po_print parameter value

to 1 if po_from and po_to parameters are same.

Then Query will fetch the correct data for all conditions and uses the

indexes incase of :P_po_num_from=:P_po_num_to and ALPHANUMERIC data

Bug#946470 -- bgopired  */

 

 

  If :P_po_num_from = :P_po_num_to THEN

            :P_single_po_print := 1;

  END IF;

 

 

  srw.user_exit('FND INSTALLATION OUTPUT_TYPE="STATUS"

                 OUTPUT_FIELD=":P_EC_STATUS"

                 APPS="EC"');

 

  srw.message ('010', :P_ec_status);

 

  SRW.USER_EXIT('FND FLEXSQL CODE           ="MSTK"

                             OUTPUT         =":P_FLEX_ITEM"

                             APPL_SHORT_NAME="INV"

                             MODE           ="SELECT"

                             DISPLAY        ="ALL"

                             NUM            ="101"

                             TABLEALIAS     ="MSI"');

 

/* Check for multilingual installation and take appropriate action. */

/* See Reports PL/SQL for more details. */

  if (MLS_INSTALLED) then

     POPULATE_MLS_LEXICALS;

  end if;

 

  RETURN TRUE;

END;  return (TRUE);

end;
Re: Using a parameter for Maximum rows to fetch [message #486554 is a reply to message #486552] Thu, 16 December 2010 09:31 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
Any point before the return I would imagine.

That 2nd return true is pointless btw.
Re: Using a parameter for Maximum rows to fetch [message #486557 is a reply to message #486554] Thu, 16 December 2010 10:25 Go to previous messageGo to next message
davholla
Messages: 116
Registered: August 2009
Location: London
Senior Member
Thanks for that I modified it as at the bottom of the page -

But I get


REP-1316: Before Report Trigger references column 'c_num_lines' at wrong frequency
function BeforeReport return boolean is

begin

 

BEGIN

 

  -- srw.do_sql('alter session set sql_trace = true');

  select displayed_field

  into   :C_continued

  from   po_lookup_codes

  where  lookup_type = 'POXPRPOP'

  and    lookup_code = 'CONTINUED';

 

  select displayed_field

  into   :C_address_at_top

  from   po_lookup_codes

  where  lookup_type = 'POXPRPOP'

  and    lookup_code = 'ADDRESS AT TOP';

/* Bug 1484478

  The call to initialize is being made in after parameter value trigger and it

  is not necessary to do it here again

  SRW.USER_EXIT('FND SRWINIT');

*/

 

/* Change the :P_single_po_print parameter value

to 1 if po_from and po_to parameters are same.

Then Query will fetch the correct data for all conditions and uses the

indexes incase of :P_po_num_from=:P_po_num_to and ALPHANUMERIC data

Bug#946470 -- bgopired  */

 

 

  If :P_po_num_from = :P_po_num_to THEN

            :P_single_po_print := 1;

  END IF;

 

 

  srw.user_exit('FND INSTALLATION OUTPUT_TYPE="STATUS"

                 OUTPUT_FIELD=":P_EC_STATUS"

                 APPS="EC"');

 

  srw.message ('010', :P_ec_status);

 

  SRW.USER_EXIT('FND FLEXSQL CODE           ="MSTK"

                             OUTPUT         =":P_FLEX_ITEM"

                             APPL_SHORT_NAME="INV"

                             MODE           ="SELECT"

                             DISPLAY        ="ALL"

                             NUM            ="101"

                             TABLEALIAS     ="MSI"');

 

/* Check for multilingual installation and take appropriate action. */

/* See Reports PL/SQL for more details. */

  if (MLS_INSTALLED) then

     POPULATE_MLS_LEXICALS;

  end if;

  SRW.SET_MAXROW('Q_headers',:c_num_lines);

  RETURN TRUE;

 

end;

end;
Re: Using a parameter for Maximum rows to fetch [message #486558 is a reply to message #486557] Thu, 16 December 2010 10:42 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
So where does c_num_lines come from?
If it's from one of the report queries then you can't use it as it won't be populated until after this trigger has finished executing
Re: Using a parameter for Maximum rows to fetch [message #486559 is a reply to message #486558] Thu, 16 December 2010 11:02 Go to previous messageGo to next message
davholla
Messages: 116
Registered: August 2009
Location: London
Senior Member
cookiemonster wrote on Thu, 16 December 2010 16:42
So where does c_num_lines come from?
If it's from one of the report queries then you can't use it as it won't be populated until after this trigger has finished executing

It does come from a query. So how should I do this - create a format trigger?
Re: Using a parameter for Maximum rows to fetch [message #486560 is a reply to message #486559] Thu, 16 December 2010 11:10 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm not convinced you can do this.
You can try with format trigger.
Re: Using a parameter for Maximum rows to fetch [message #486615 is a reply to message #486560] Fri, 17 December 2010 06:42 Go to previous messageGo to next message
davholla
Messages: 116
Registered: August 2009
Location: London
Senior Member
cookiemonster wrote on Thu, 16 December 2010 17:10
I'm not convinced you can do this.
You can try with format trigger.

Can I call SRW.SET_MAXROW in a query?
Re: Using a parameter for Maximum rows to fetch [message #486619 is a reply to message #486615] Fri, 17 December 2010 07:49 Go to previous messageGo to next message
Littlefoot
Messages: 19525
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you try? What happened?
Re: Using a parameter for Maximum rows to fetch [message #486624 is a reply to message #486619] Fri, 17 December 2010 08:09 Go to previous message
davholla
Messages: 116
Registered: August 2009
Location: London
Senior Member
Littlefoot wrote on Fri, 17 December 2010 13:49
Did you try? What happened?

With a query I have not tried yet.

When I put it in a formula column I could not work out how to get it to trigger
Previous Topic: Oracle Scheduling of reports
Next Topic: Report server from command line
Goto Forum:
  


Current Time: Fri Aug 29 22:02:01 CDT 2014

Total time taken to generate the page: 0.09643 seconds