Oracle Reports FAQ
$Date: 05-Jul-1998 $
$Revision: 1.00 $
$Author: Michael Broughton $
Topics
Back to Oracle FAQ Index
I switched the page size to 11x8.5, but the printer still prints in portrait.
Even though you set the page size in the report properties, there is a
another variable in the system parameters section under the data model in
the object navigator called orientation. This sets the printer
orientation. Oracle starts by setting it to "default" which means that no
matter how you set the page size, the user's default printer setup will be
used. You can also set it to either "Landscape" or "Portrait" to force the
printer orientation no matter what the user has set as default. These
sorts of picky, minor details are the ones which are invariably forgotten
when you are designing your report and are the reason I created our two
report templates, reptmp_p and reptmp_l (portrait and landscape). For
anyone who wants a consistent look in their reports I strongly recommend
building a similar pair to save yourself an ulcer, unless you actually
like starting from scratch every time!?!
Back to top of file
I moved this field into that repeating frame, but I'm still getting a "frequency below it's group" error.
Moving fields around does not change what enclosing object is considered
it's parent group. Oracle carefully remembers what repeating frame a
field was originally placed in and assigns that as it's parent. If you
then reference a column further down the line of the query structure it
will return that error. If you are not exactly sure which repeating frame
a field belongs to, try dragging it out of all of them. Whichever frame
will not allow it to escape is it's parent. To change a field's parent,
first click on the lock button on the speedbutton bar. It should now look
like an unlocked padlock. Now all of the fields on the layout can be
repositioned regardless of their original parent items. When you are
satisfied with the repositioning click the lock button again to lock the
layout. Oracle will parse the layout and assumes that any item fully
enclosed in a repeating frame is a child object of that frame. This can
be confirmed again by trying to drag an object out of it's parent. (Cntrl
- Z or edit..undo will put it back where it came from)
Sometimes, for unknown and mysterious reasons, this method does not work.
The alternative in this case is to highlight the field (or fields), cut it
(cntrl-x), and then paste it into the desired frame. The paste does not
initially set it into the right frame, but if you drag and drop it there
before clicking on any other objects, and then click on something else,
Oracle will usually figure what your intent was and assign the object(s)
as a child of that frame. This is my preferred method of changing a
field's parent as it works much more consistently then the unlock/lock
method. One note though, if you are reassigning a group of fields, make
sure the frame you are going to move them into is large enough to accept
the whole group at once before you do the cut/paste. If you do the paste
and then try to grow the frame to fit, you will have to cut and paste
again. Once you de-select an object that has just been pasted, Oracle will
assign it as a child of whatever it is in at the time.
If this technique also fails, you are probably going to have to delete and
then recreate the objects within the desired frame. If the object has
triggers attached, save yourself some typing by creating the new object in
the right frame, copying over the trigger code, and then deleting the old
object
Back to top of file
I must put a repeating frame around these fields. How do I do this easily?
Well congratulations, you have just discovered one of the main reasons why
good planning goes a long way. Oracle looks at the layout as a sort of
layered inheritance model such that anything created on top of and
completely inside another object is by definition a child of that object.
Creation order is therefor critical to the layout process. This means that
placing a repeating frame on top of a field but larger than that field
fails the ownership criteria. At best, if the new frame is fully enclosed
within the same higher level frame as the field then the two will be
considered sibling children of the higher level frame.
From this point you have two options. First, you can place the new
repeating frame in the correct place and then use the techniques shown
above in the "I moved this field but am still getting a frequency error"
to reassign the fields into the new frame. There is also a second choice
(which can also be used as a solution to the above). Go ahead and draw the
new frame around the fields you want to have placed in it. Now if you try
to click on one of the fields you will not be able to as they are fully
covered by the new frame. Now go to the "Arrange" menu. You will find the
options Send to back, bring to front, move forwards, move backwards. These
are used to alter an object position in the Reports layer ordering. You
use the "send backwards" option to move the frame backwards until all of
the fields have popped to the front and are now enclosed in it. Oracle
reassigns the new repeating frame as each object's parent as they pop to
the front.
Note that you can only move an object back and forth amongst it's
siblings. You cannot set it back below it's parent, nor in front of it's
children. This means that once an object has popped to the front and had a
reassignment of parent, you cannot move it back using these tools.
Back to top of file
Why does part of a row sometimes get shifted to the next page, but not all of it?
This is due to the way the scan works when Oracle is parsing the layout.
If the tops of all the fields in a row are aligned and the fields are all
of the same height and font, they should all stay together. I suspect,
however, that Reports bases it's decision on the printed size rather than
the field size you define to determine which objects are too large and
must be shifted to the next page. This means that even if you set two
fields top-aligned with the same height and font but one of them is
bolded, the bolded field could get shifted to the next page due to it's
bigger footprint. The solution is to put the whole row into a regular
frame which is page protected.
Back to top of file
What exactly does the "Print Condition" do?
The print condition type First, All, All but first, Last, All but last
refer to the frequency with which you want to appear based upon the
setting of the print condition object. A print condition object of
Enclosing Object is whichever object encloses the current object (could be
the parent or a frame within the parent), while Anchoring Object is the
parent object (unless you have explicitly anchored the object in which
case it is the object to which it is anchored). The key here is that this
is about the pages on which the Print Condition Object appears, not the
current object. Oracle views First as the first page on which any part of
the Print Condition Object is printed, likewise Last is the last page on
which any part of the Print Condition Object is printed. For objects
inside a repeating frame, this condition is re-evaluated for each instance
of the frame.
As an example, assume we have created a field inside a repeating frame
with Print Condition Object set to 'anchoring object', and Print Condition
Type set to 'All But First'. On every instance of that repeating frame
which is printed entirely within a single page, our object will not print.
However, if an instance of that frame spans more than one page then our
object will print on the second and every subsequent page that this
instance of the repeating frame spans.
For most objects you will not have to play with this print condition
setting as the default setting is pretty good at determining what pages to
print on, even though it only chooses between 'first' and 'last'. Only
such things as heading objects you want reprinted on multiple pages are
normally candidates for fooling around with this setting.
Back to top of file
How do I create a truly dynamic 'where' condition which the user can input on the parameter form for my select statement
While setting a simple parameter for use in defining the select statement,
such as a date, bill_period_id etc. is simple, there are times when you
may wish to allow a user to add any "where" statement they wish. However,
if you create a varchar user variable and try to reference it as an SQL
condition ( e.g. Select * from account where :usercondition) you will get
an error. The secret is that the variable must be initialized to a valid
SQL condition before the Data Model will accept it. This is done in the
"Initial Value" spot on the variable's properties form. The usual default
is "1 = 1" which simply means all rows meeting whatever other conditions
are included in the select statement will pass this condition if the user
does not change it in the parameter form.
Back to top of file
How do I change a user parameter at runtime from a layout object trigger?
Quite simply, you can't. Once the BeforeReport trigger has fired, Reports
locks down the user parameters until the report is finished. Oh, I know
you can put a statement into a layout trigger at design time and the
compiler will accept it, but the moment you run the report you will get a
nasty error and the report will die. Why they couldn't catch those
problems at compile time I have no idea, except that it probably uses the
same PL/SQL compiler as Forms which uses that same syntax for the
perfectly acceptable function of changing field values.
That being said, there is valid technique to mimic having a user variable
which can be changed over the course of the report execution. What you
have to do is create a PL/SQL package that contains a variable as well as
the functions to read and write to that variable. Since variables inside a
package are both local to that package and persistent over the duration of
the run, you use this to save and change your variable value. I know that
this seems like overkill, but it is the most efficient way of handling an
issue that is very rarely encountered. As you can probably guess, this
technique is a last resort to finding an SQL work around if one exists.
Back to top of file
How do I set the initial values of parameters for the parameter form at runtime?
This is what the BeforeForm trigger is primarily used for. Even if you
have used a select statement to create a lookup list for the parameter,
this statement is fully parsed before the parameter form is opened. Simply
setting the parameter to a given value in the BeforeForm trigger will
select that option as the default value displayed to the user. For
example, assume you have a parameter called p_input_date which is intended
to hold an invoice date. The following example will select the most recent
invoice date as the default, and note that it properly handles exceptions
to ensure that the report does not arbitrarily die if this default setting
fails. Note also that like all report triggers, it must return a true or
false value.
function BeforePForm return boolean is
begin
select max(bill_period_end_date + 1)
into :p_input_date
from billing_period
where bill_period_end_date <= (select trunc(sysdate)
from dual);
return (TRUE);
exception
when others then
:p_input_date := null;
return true;
end;
Back to top of file
Why can't I highlight a bunch of fields and change all their format masks or print conditions at once?
You can. If you highlight a bunch of objects and then right click and
select "properties..", Oracle gives you a stacked set of the individual
properties forms for each of the selected objects. While this may be
useful for some things, it requires changing values individually for each
object. However, instead you can select the group of fields and then
select "Common properties" from the "Tools" menu which will allow you to
set the format mask , print conditions etc. for the whole set of objects
at once.
Back to top of file
How do I change the printed value of a field at runtime?
Triggers are intended to simply provide a true or false return value to
determine whether an object should be printed. It is generally not allowed
to change any values held in the cursor, make changes to the database, or
change the value of it's objects value. That being said, there is a highly
unpublicized method of doing just that using the SRW.Set_Field_Char
procedure. The syntax is SRW.Set_Field_char(0,) and the output
of the object that the current trigger is attached to will be replaced by
. There are also SRW.set_fileld_num, and SRW.set_field_date for
numeric or date fields.
While these options do work, they should only be used if a suitable NVL or
DECODE statement in the original query is not possible as they are much,
much slower to run. Also, note that this change of value only applies to
the formatted output. It does not change the value held in the cursor and
so can not be used for evaluating summary totals.
Back to top of file