Re: Large Master/Detail/Detail Reports

You might want to take a look at Infomaker (Sybase). It handles nested reports (the equivalent of your master/detail) very well.

--
Terry Dykstra (TeamSybase)
Canadian Forest Oil Ltd.
Todd Owers wrote in message <7i1p4c$dlv$1_at_nntp.gulfsouth.verio.net>...


>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


>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.

>

>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.

>

>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


>(2 logical pages), with pages 7 and 8 being blank.  Oracle Support said


this


>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.

>

>As a workaround, I restructured the hierarchy of the master frame and

detail


>frames so that the master frame did not enclose the detail frames.  Instead


>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


>I can no longer use a  summary counter in the master group to count the


>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

>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


>is not a limitation of Reports -- it is a function of the large number of


>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


>page.  The Vertical Elasticity property ostensibly handles this situation.


>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


>the following logical pages.


>

>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


>the Vertical Elasticity property is set to Fixed, then the object will not


>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.

>

>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


>know I can't be the first person to try to produce a report like this.  Has


>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


>problems or not.  I welcome any comments or suggestions.


>

>Todd Owers

>toddo_at_gcr1.com

>

>