Large Master/Detail/Detail Reports
Date: Thu, 20 May 1999 14:51:19 -0500
Message-ID: <7i1p4c$dlv$1_at_nntp.gulfsouth.verio.net>
[Quoted] I apologize in advance for the length of this post, but I think this is an important issue, and I have not seen it discussed elsewhere.
I have been working with Reports 2.5 and Reports 3.0 for over two years, and [Quoted] I have come to the conclusion that this product works well with relatively small, simple reports; but it cannot handle a relatively large Master/Detail/Detail report.
For example, I have a report that has one master group and 11 (yes, eleven) detail groups. Some of the detail groups have as many as 50 fields that need to be printed on the report. Depending on the number of instances of each detail group, a typical report may be as few as 2 or as many as 12 physical pages.
[Quoted] The first problem for a report of this size occurs in creating the default layout. Initially, Reports places all the detail groups to the right of, rather than below, the master group. To avoid the REP-1212 error, the Page Width must be increased to an artificial number, such as 50 inches. Then, the developer must manually drag and drop the detail frames and items to their desired location below the master frame, and the Page Width can be restored to 8.5 inches.
[Quoted] After several hours, the layout can be manipulated to the desired configuration, so the design problems can be overcome. But there are runtime problems that cannot be overcome. When the master frame encloses all of the detail frames (the standard arrangement), its height in the layout editor may be larger than the standard physical page height of 11 inches. (In my report, it was 31 inches.) Again to avoid the REP-1212 error, the Logical Vertical Panels property must be increased from its default value of 1. (In my report, it needed to be increased to 4.) But this is not desirable, because at runtime, the number of physical pages printed will always be a multiple of the number of logical pages. For example, if my report required 6 physical pages, it printed 8 physical pages [Quoted] (2 logical pages), with pages 7 and 8 being blank. Oracle Support said this [Quoted] is the normal behavior for a report with Logical Vertical Panels greater than 1. My users require Page 1 of X in the top-right margin of each printed page, and it is unacceptable for a report to have unnecessary blank pages at the end.
[Quoted] As a workaround, I restructured the hierarchy of the master frame and detail [Quoted] frames so that the master frame did not enclose the detail frames. Instead [Quoted] of a parent-child relationship between the master group and detail groups, all groups were siblings. This structure reduced the height of the master frame in the layout editor to 3 inches, thus allowing me to set Logical Vertical Panels back to 1, and eliminating the unnecessary blank pages at the end of the report. However, the price I paid for this structure is that [Quoted] I can no longer use a summary counter in the master group to count the [Quoted] number of occurrences of each detail group and print a "No detail records retrieved" message when there are no detail records retrieved, because of the REP-1314 message. I can probably live with this limitation, but there [Quoted] is another problem that I cannot live with.
Because of the large number of fields in the detail groups, I cannot use a "tabular" format, with the label above the field. All the fields will not fit on one line on 8.5 inch wide paper. Instead, I must use a "form" format, with the label on the same line and to the left of the field. (This [Quoted] is not a limitation of Reports -- it is a function of the large number of [Quoted] fields in the detail groups.) The result is that for some detail groups, one instance may require as many as 6 inches vertically to print. Of course, if an instance begins to print near the bottom of a physical page, it may not fit entirely on that page and will roll over to the next physical [Quoted] page. The Vertical Elasticity property ostensibly handles this situation. [Quoted] Setting this property to Variable will print as much of the object as possible on the first logical page, and if necessary, complete the object on [Quoted] the following logical pages.
[Quoted] The problem is that when this "rollover" occurs, there is a blank space at the top of the next physical page. This blank space is equal to the amount of the repeating frame that printed on the previous physical page. For example, in my report an instance requires 6 inches. If it prints 4 inches at the bottom of the first page, then there is a 4 inch blank space at the top of the next physical page before the remaining 2 inches are printed. Obviously, this blank space in the middle of the report is unacceptable. If [Quoted] the Vertical Elasticity property is set to Fixed, then the object will not [Quoted] attempt to print unless the entire instance will fit on the page; it will begin printing on the next page. This results in a 4 inch blank space at the bottom of the first page. Either way, there is an unacceptable 4 inch blank space in the middle of the report. I suspect that there is no way to avoid this, because the Vertical Elasticity property operates on logical pages, not physical pages.
[Quoted] These various design and runtime problems have led me to conclude that Reports is not equipped to handle large master/detail/detail reports. But I [Quoted] know I can't be the first person to try to produce a report like this. Has [Quoted] anyone found a way to overcome these problems? I don't have any experience with other report writers, so I don't know if other products have these same [Quoted] problems or not. I welcome any comments or suggestions.
Todd Owers
toddo_at_gcr1.com
Received on Thu May 20 1999 - 21:51:19 CEST