Overview of Oracle Reports

Introduction: 

            So you've got your queries set up and you are ready to design your layout. The problem is, none of the Oracle default layouts meet your needs, or, when you try to use one you get an error message saying that the layout you chose won't fit on the page. Then, when you finally finish moving the fields around into the places you want them, it either gives you a "Frequency below it's group" error, prints in an unexpected order / format / page orientation, or does something really strange like reprinting the same page endlessly. Trying to determine the cause and solution from the documentation now begins, invariably taking three or four hours to unearth the correct two second cure. It is somewhere about this point that your monitor is in danger of receiving a quick lesson in Newtonian physics. Hopefully, this document will make learning advanced topics in Oracle Reports 2.6 a much less painful experience, and perhaps save a few monitors from an untimely demise.

            This document is intended for the relative novice who has a basic understanding of the mechanics of Oracle's Report writer, but will also include tips and tricks which may aid the more experienced user. The target audience should at least know how to build a data model (including breaks although instructions for their creation is included), and have a basic understanding of how objects can be manipulated in the layout editor. This is also intended to be a continuous work in progress so that as you find a new wrinkle please add it to this document in an appropriate spot in order that the rest of us may benefit.

The document is structured into two parts. First is my own informal description of how Reports 2.5 deciphers your layout and data model as a clear understanding of this and the relationships between the two parts is critical. From there, I will cover some basic design and efficiency issues common to most reports, then move on to more advanced topics. These include a description of some of the more useful built in procedures and packages that Oracle was good enough to include in Reports but fails to clearly highlight in the main portion of their documentation. The second part of this document is a FAQ section to answer the most common "How do I …." questions I have either heard from others or asked myself. While I'm sure I will not have remembered them all, in the ones I do cover I will try to answer them by explaining how to do it, and (most importantly) why it works. Hopefully this section will give as many insights into Report's internal machinations as the first, or at least help illustrate many of the issues discussed in the first section.

The Reports 2.6 Environment:

            The main areas of the Reports environment are the Object Navigator, the Parameter Form, the Data Model and the Layout Editor. While for the most part the Object Navigator holds only the information derived from the other three areas, it also holds the information used to determine the global variables such as the page setup information and user defined variables. These variables may be used to set selection parameters, print order, or display strings used in the data model and layout.

            The format of the Object Navigator environment is very similar to that found in Oracle Forms, with a couple of exceptions. Where in forms, code snippets are kept with the object that it belongs to, in reports all triggers are kept in the "Program Units" section. The layout area only shows a "p" in the bullet at the left of  an object to indicate that a code snippet exists for that object. For this reason, it is best to stick with the generated names for object triggers as they are derived from the name of their parent object to make it easier to find them in the Navigator, or develop a similar source-based naming convention.

 

The Data Model

            The Data Model is where the queries needed to retrieve your data are created and linked. Each SQL statement is held in block, and then the join conditions between the blocks are created by selecting the link tool and then clicking first on the parent field and then the child field for the linking relationship. Each statement may be either a simple or complex query, and they may use the values held in parameters by preceding the parameter name with a full colon, consistent with the way field values and global parameters are accessed in Oracle Forms.

Because of the tight relationship between the structure of the data model and the  design of the layout, getting from here to the desired final product can be much more pleasant experience by careful planning at this stage. Each distinct grouping of data in your layout requires a distinct block be created in the data model. For the novice user, I would suggest going as far as drawing a rough mockup example of the report to determine how data is to be grouped, and where each piece of information is to be displayed. Then consider that the data is fetched from the top of the model down through your blocks. A piece of information must be included in a query above or equal to the level at which you want it displayed. This will allow you to quickly evaluate the structure and order of the blocks you will need as well as what information is needed for each block. When the same piece of information is required in more than one location on the report, remember that a layout object can always access variables from a higher level block, but not a lower level one. The rule of thumb is to put information in the highest level block of your data model as is possible while meeting the needs of the layout.

A break group is a method of breaking a single SQL block into separate blocks without the need to write separate SQL statements. They are used to create a report in which an identical value in sequential rows is printed only once for its related records. (i.e. A break group divides a query's columns into sets like a "group by" statement.)

To create a break group:

                                                                           

    1. In the Data Model Editor, drag the main group that contains the column at which

you want to break your report down from the query title object about 2 inches.                                                                      

    2. Select the column that you want to use to divide your report (e.g. if you want to

group a list of employee data by department number, select the department

number column) and drag it out of and above the group to create a new group.                                                                 

    3. Double-click on the title bar of the new group object to display its property sheet. 

Replace the name in the GENERAL:Name Field if you do not want to use the

default.  Fill in the rest of the property sheet as necessary and accept it.                                                             

Although all of the built in dialog boxes for creating queries are designed to help the user create blocks based on a query from a single table, remember that this was designed to aid the novice user with little SQL experience. For efficiency reasons, whenever possible you should try to create single block based upon an optimized compound select statement rather than a number of joined select statement blocks. This is because Oracle must open separate cursors and link them together for each individual select statement block you create, but a more complex select statement with a break group is treated as a single cursor with a group by clause. You are more likely to optimize the query than Oracle's built-in optimizer, and fewer blocks in the data model equates to fewer frames needed on your layout which is also easier to work with and more efficient at runtime. Finally, by creating a compound statement, any of the fields selcted can be promoted or demoted through break blocks to ensure that is accessible at the proper time in the layout. In separate queries this promotion is not easy to accomplish except through inefficient summary and formula fields. Your goal should be to have the fewest select statements and also the fewest distinct blocks of fields on your display that will fulfill the levels of groupings that your report layout will need.

In cases where there is no option but to create separate select statements with links between them, it is not necessary to restrict links to match columns with an "equals" condition. If you click on a link and bring up it's properties box, you will find that you can select from ( =, < ,<= ,<> ,>, >=, LIKE, NOT LIKE) as the join condition. In cases where even these options are not sufficient (for example, when you would like an "or" clause between two links rather than the default "and" used to evaluate multiple links), there is another option. First, create a dummy link between the title bars of the two blocks rather than between columns by selecting the link tool and then clicking first on the parent block's title bar and then the child block's title bar. Now, in the select statement of the child block, you can access column values in the parent block as though they were user variables by using the colon to indicate a parent block column name. You can now create as complex a link clause as you desire.

            Additional filtering can be done by right-clicking on the title bar of a block and selecting the "Program Unit Editor" option. This will create a trigger called a "group filter" returning true or false which determines whether a row for this block is included in the report. It can also be used to perform actions on each fetch instance of the block. This is different from the trigger on that block's repeating frame in the layout because returning false in the group filter will exclude that row's data from any summary columns, while the repeating frame trigger will only exclude the data from the display. It also ensures that the trigger fires on every fetch. This cannot be guaranteed in the layout since if that block has a parent whose repeating frame also has a trigger, if the parent repeating frame does not print, the child's trigger will not be fired since it's result is irrelevant to the layout.

            You will also notice that when Reports parses a select statement you have given it, to the left of every field name in the block diagram is a little black triangle. These are representations of the break order for each field and can be considered equivalent to an order by clause. These are initially all to ascending by default unless your query specified a descending "order by" clause for a given field in which case you will see a little upside down black triangle. The thing to remember about these break orders is that Oracle completely ignores them in the lowest level detail block for that select statement, but lives by them once you drag the fields into a break block.

In a break block, all fields moved into it will use these break orderings, even if they override the original select statement's ordering clauses, and at least one field in a break box must have an ordering clause. The  display order of output for the report will be based upon the break orders of the fields in the break box ordered from top to bottom in the data model. For efficiency reasons, if you move a bunch of other fields into the break for soley for display reasons to have them at this level of your data model structure, you should turn off their break orders from within their properties forms. This is done by unchecking the "break order" checkbox near the bottom of the field's Properties box. If you do not do this, Oracle will put in all the extra indexing overtime to sort the results on these fields when you don't care about the order, or worse yet when the order is trivial.

            An example of this would be assuming you have a master-detail type report on some aspect of account information, and want the account_id, account_name, and contact info in a break box with the rest of the report details underneath. Your presentation is to be sorted by account_id only. If you leave the break orders on all the fields in the break box, Oracle will be running your query as though there was a "order by account_id, account_name, contact_id, contact_name …." clause. This is extremely inefficient since it's doing all the indexing work even though there is never more than one account_name or contact_id for each account_id, but it'll spend a good chunk of time spinning it's wheels checking the indexes just to make sure.

The Layout Editor

            The Layout Editor provides two different views of the page. The margin view shows the entire page size and anything on this view is printed on each page of the report unless overridden by a trigger. A thick black box indicates the area in which the details of the report will be printed. The margin view should be used to print all static titles, headings and footer fields which will remain positionally constant on every page. If your headings will change from page to page depending on the current block being printed, then they should be placed within the body view. Note that the heavy box outlining the details section can be moved and sized to create the appropriate margins needed for the body view, allowing you to then use the full limits of the body view without worrying about the margins.

            The body view is the size of the margin box you created in the margin view, and so is usually smaller than your actual page size. Here is where the fetched record details are formatted and displayed.  When building a layout from scratch, you must remember that frames must be nested in order of  the hierarchy you developed in the Data Model, and all higher level frames must exist before a lower level one can be included (i.e. you cannot skip a repeating block just because you do not need to print anything from it).  Basically, you need one repeating frame for each distinct block that you have in your data model. The only exception is a detail block that is only used to provide info that is summarized in a higher level block and is never actually displayed. In this case, the detail block would not have to be included in the layout to let it's parent evaluate the summaries. However, for speed and efficiency reasons, if such a situation can be avoided through such things a "sum(a) … group by b" select statement in the data model instead of the existing statement it should be implemented that way. SQL mathematical functions work much, much (and probably three more "muchs")  faster than the Report's summary columns.

In addition to the explicit anchors you can create  to keep items in position to each other, Oracle creates a set of implicit anchors between objects based upon what it thinks the intent of your layout was. The algorithm used is included in the Oracle Reports Reference Manual. Often, when items are not printing where you expected them to it is because Oracle either misinterpreted or was unable to determine your intent. The basic algorithm consists of looking at objects who have variable sizes and establishing which way they will grow or shrink. Objects within the path that they can grow/shrink are assumed to want to stay the same distance from the end of the object. Objects of fixed size are not evaluated as being parent objects in the implicit anchoring algorithm. This means that if an object is of fixed size and it's trigger tells it not to print, objects following within the path it would have had if were of variable size are not repositioned to take it's place unless they are explicitly anchored to that object.

While Reports will not show you what implicit anchors it has created in the layout editor, you can get it to do so in the Object Navigator. Select the Object Navigator as the active window, and then select "Navigator Options" from the "Navigator" menu. Select the "Layout" page of the options form and check the "Anchoring Information" checkbox and then the "Apply" button. Now, if you look at the "Layout" section of the Object Navigator, any object for which you have defined an explicit anchor or Oracle has defined an implicit one will have an "Anchoring Information" section. The following example shows a field which is anchored as the parent object to B_AA_LINE1, and is the child object of  P_AA_MSG1.

 fig 1. Anchors displayed in Object Navigator

The graphics used for these anchor displays is somewhat counter-intuitive in that the current object is the small ball, and the object named as the other object in the anchoring relationship is the large ball. There is also no way of determining from the Object Navigator if the anchor is an implicit or explicit one. You would have to look at the Layout Editor to see if there is an explicit anchor showing for that object. Finally, when defining explicit anchors, remember that the order of selecting objects is important. First you click on the child, and then select the parent. This is opposite to creating links in the Data Model where you select the parent first.

The Layout Editor is extremely picky about the creation order as it assumes that this is directly related to the ordering of parent-child relationships among the objects. As a new object is created on the layout, it becomes a child of the most recently created frame or repeating frame which completely encloses it. Once this parent-child relationship has been set, changing it is a complex task (although there are several techniques explained in the FAQ section of this document). This means that you should first create the parent frame and then all the child fields which are to be placed in it if you want to avoid "accessing value below it's frequency" errors. This ordering scheme also holds true for any frames you may wish to place in the margin view of the layout, although this should be avoided whenever possible.

Report Execution

The basic order of execution is to:

    1. Fire the before form trigger

    2. Run the parameter form

    3. Fire the after form trigger

    4. Parse the data model

    5. Fire the before report trigger

    6. Execute the report, firing between page trigger after each page but the last

    7. Fire the after report trigger.

While it is the order of execution within part 6 which requires the closest attention, notice that the data model is parsed before the "before report" trigger fires. This means that the BeforeReport trigger fires too late to alter any user variables used in the data model as the cursor has been defined. Any such validation must be done either in the validation triggers attached to the variables themselves or in the AfterPForm trigger to have any effect on the query definition. The BeforeReport trigger can still, however, be used to alter the tables to change what data is available within the tables for selection. All triggers are required to return a Boolean value, and report execution is terminated upon a return value of false in any one of the main report triggers.

It can almost be said that the layout and data model act independently from each other in that each record is fetched by the query created by your data model, and then is evaluated as to how it fits into the layout. The layout is scanned from the top down  with each object being evaluated and printed as it is reached. It is not until an object is printed in it's entirety that the scan continues downwards. In this way, each frame may be viewed almost as a child instance of the report as when a repeating frame becomes the current object being printed, a new scan is started within that frame under the same rules as the main scan. Only when all of the contents of all instances of this repeating frame are printed does the main scan resume for the next item outside of the repeating frame it has just completed. Oracle maintains a hierarchy of nested items currently being formatted to determine what triggers may need to be fired as objects are formatted over multiple pages.

When a page break occurs, the report reprints the margin specification, as well as any objects higher up in the nesting hierarchy of currently printing objects which have appropriately set their print conditions to reprint and then continues it's scan. Some print conditions or variables may require that the report format itself further ahead to determine what must be printed on the current page. The simplest example of this is to set a field to display $TotalPhysicalPages (e.g. So that the header shows a "page x of y" field). This requires the report to run completely to determine the total pages before it can fully format the first page, even when run in preview mode.

Triggers

Besides the five built in main triggers mentioned earlier, triggers are small pieces of code which may be attached to any of the objects placed in the layout editor. They are required to return a true or false value which corresponds to whether this instance of the trigger's object should be printed. For example, a trigger on a repeating frame is reevaluated for each repeat to see if the contents should be printed in that instance.  The basic rule regarding format triggers is 'The fewer the better' as they slow the report formatting. You should try to perform as much data validation in the data model as possible through such things as the decode statement or group filters. When circumstances dictate the use of triggers, try to group items which will print under the same conditions into a frame and then attach the trigger to the frame. This avoids unnecessary repetition of the same code for multiple objects.

Frames and repeating frames will have their triggers fire on each page for which that object prints. This means that a frame that stretches to print over multiple pages will fire it's format trigger on each of those pages. Also, for all objects the trigger fires on the first page on which that object is formatted. One significant result of this is that you should not base the format trigger's result upon anything pertaining to page numbers if you are not sure of where on the page it will print (e.g. detail lines). An object may start to print on the bottom of one page, thus firing the trigger, and then Oracle decides the object is too big and moves it to the next page without re-firing the trigger. In this case, a result based upon the current page number would in fact be being evaluated on the basis of the previous page number. This problem may also cause some problems if you are attempting to coordinate between the object's trigger and the BetweenPages trigger where the BetweenPages fires after a field you would have expected it to have fired before. The only way to guarantee what page an object will print on is if it is an object in the Margin View of the Layout Editor, or by making it a fixed size child of a fixed size frame which has been set to "page break before".

The BetweenPages trigger also has another notable side-effect. In preview mode it will fire in both directions if you work forwards and backwards through the display. This may lead to some strange results if it is manipulating variable values upon which other fields or their printing conditions are based. Of course, this problem will not occur when the report is run to the screen or to a printer.

Another trigger with potentially harmfull side-effects is the BeforePForm trigger. While any errors in the AfterPForm or BeforeReport will simply reactivate the parameter form, an unhandled exception in the BeforePForm trigger will cause the report to exit. This is very frustrating to the user as the report exits before the parameter form pops up, so it looks as though the report never executed at all. In cases where the error is due to a select from a missing table, the report may GPF and also kill the application that called it. Selecting into a user parameter with a format mask or restricted list of possible values is also risky as if the select returns null. The report decides that it can not apply the mask to null and exits. The way around this eventuality is to select into a local variable and then assign this value to the parameter only if it is non-null.

To access the values of any of the user variables from the data model portion of the object navigator, simply precede them with a full colon. E.g. :p_invoice_date, :p_account_name, or whatever else you might have named them. Note that while you may access their values, Reports 2.5 does not allow you to modify them after the BeforeReport trigger has completed.

User variables may also have validation triggers attached to them. These can be created by pressing the "Edit.." button on the parameter's properties form, double clicking the round dot to the left of the parameter in the object navigator, or right clicking on it in the Object Navigator and selecting "Program Unit Editor". The only advantage to performing validation here rather than in the AfterPform trigger is that on failure it will reopen the parameter form and highlight the offending parameter whereas the AfterPForm trigger does not know exactly why it has failed so only reopens the parameter form with no other hints. The disadvantage is that splitting up a bunch of factors to be considered before accepting the users parameters is less maintainable, especially where report failure may be due to the combination of parameters rather than a single setting. As a point of form, I prefer putting all parameter validation into the AfterPForm trigger to keep it all easy to find and easily accessible in it's entirety.

Built-In Packages:

           

There is one main package Oracle has included which provides many useful functions to the developer. This is the SRW package detailed in chapter 13 of the Reports 2.5 reference manual. It includes many functions to allow such things as DDL statements to alter the database which are normally not allowed within PL/SQL. It also includes a full set of exceptions for use by these DDL functions. One of the most useful procedures for debugging is the SRW.Message functions which will pop up a message window with up to three lines of text. Unfortunately, when run in batch mode as all of our reports are when run off the application menu, these messages are not displayed but are instead written to the report log. This means that it cannot be used to provide instant visual feedback or hints to the users.

            There are also Break and Trace procedures to help in debugging, as well as a few other handy tools for such things as changing visual attributes and format masks of fields during runtime, or for handling uncommon exceptions such as when a field overflows or is truncated. I strongly recommend reading the chapter on the SRW package to all new Reports developers.

 

Frequently Asked Questions:

I switched the page size to 11 × 8.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!?!

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

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.

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.

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.

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.

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.

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;

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.

 

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,<newstring>) and the output of the object that the current trigger is attached to will be replaced by <newstring>. 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.

Give me your Feedback.......