It is currently 25 February 2021, 7:42 Advanced search

Report Example: Page Break Handling for Subreports

Questions and answers on how to best use Instant Developer

Report Example: Page Break Handling for Subreports

Postby ljwilson » 9 August 2020, 14:53

I recently had a project where the report required at least one subreport. I had always avoided using subreports since they are not very efficient (master query run for each subreport, so if the subreport is in a detail then lots of SQL queries). The requirements:

  1. Report Header on first page only. It should print at the "top" of the page (where the Page Header prints). The first page does not print the page header.
  2. Remaining pages print the page header.
  3. One "group", consisting of a Group Header for the entire group.
  4. The Header always prints, even if the corresponding Detail 1 and Detail 2 are empty.
  5. Two details: Detail 1 is a set of child records, Detail 2 is a second set of child records.
  6. Either or both Detail 1 and Detail 2 can have no records. If no records then do not print the corresponding Detail.
  7. Detail 1 always has a total footer (even if there is one record).
  8. Detail 2 has no footer.
  9. Group should not split unless the entire group cannot fit on a page.
  10. Top of the page (below the page header) should always be the start of a group.
Data-wise the group header is the parent, and one set of children for detail 1, with a second set of children for detail 2.

I can get all the data required in one master query (SQL statement) without using subreports, but could not come up with a way using standard group headers/detail/group footers to get the print order desired.
Since in the report tree you can only rearrange detail sections, the order would come out to be:

Group Header 1
Group Header 2
Detail 1
Detail 2
Group Footer 2 (we don't need this, but this is where it would be in the order)
Group Footer 1

But what we want is:
Group Header 1
Detail 1
Group Footer 1
Group Header 2
Detail 2
Group Footer 2 (we don't need this, but this is where it would be in the order)

It is easy to get the printing order desired with at least one subreport, and even easier if you use two subreports. To get around the problem of making a SQL call for every detail set, I used imdb tables for each detail's master query to cache all the data from one SQL call to get all the detail records for each detail, and then set each subreport to use the imdb table filtered by its parent record. If the SQL server was local to the web server, then not much difference in speed noticed; If the SQL server was remote (not on the same Local Area Network/computer) as the web server, then it took over 2 minutes to render a PDF with no imdb tables; with imdb tables it took less than 2 seconds to render a PDF!

So, we make 3 SQL calls before creating the report: first one for the headers (no imdb table for it needed), second SQL call for the first set of detail records loaded into an imdb table, and a third SQL call to load the second set of detail records into a separate imdb table.

The first detail (subreport) had its own Report header/Detail/Report footer. The second subreport only had a Report Header/Detail.

Reading the documentation, it looked like all I had to do was check the "Keep with next" flags for each Detail containing a subreport, the logic being keep all the detail sections that make up a group "together". All looked good at first glance, but paging through various reports would show that one of the details might get separated from the other so both weren't on the same page. Another problem I ran into was the group header should always print, even if there were no detail records, which wouldn't happen if the first detail was empty. So I changed it to have a third detail be the group header, so it would always print.

To recap, what we have so far is 3 details:
Detail 1 Is the Group Header
Detail 2 is the first set of child records (in a subreport)
Detail 3 is the second set of child records (in a subreport)

Checking "Keep with next" for Detail 1 and Detail 2, and not checking for Detail 3 means (to me at least) it should keep all 3 details together. (See Report Example 5). But it would still split details within a group across pages on occasion.

Reading through all the relevant posts on both the English and Italian sides, I found a couple by "theguru" on how he had encountered this very issue and he fixes it by (this is from google translate):
BOOK Report foot cut and page missing
https://forum.instantdeveloper.com/viewtopic.php?f=5&t=87640&p=163457
I have had MANY similar problems.
In general I can tell you this: the automatisms in the design of the books, especially in the case of subreports, are in my experience quite buggy. I believe that the problems have never been solved as users (at least what happened to me) prefer to solve these situations by arranging the various code formats (in the various before after formatting ), rather than waiting for a patch to the framework.
Quickly I could tell you: always disable all flags like "hide if empty" (if you need this behavior, implement it by code by setting the sections to hidden, after the appropriate checks)
Create speculative procedures that "count" the records of the subreports, calculate the size of the box, and (after all the arithmetic of the case) if there was not enough space on the page insert a nice "new page before "
Once you take us the hand is not that complex, and you bring the result home.


And one post by dipa alluding to the known bug with this:
report: serious problem with sections going on multiple pages
https://forum.instantdeveloper.com/viewtopic.php?f=5&t=87859
I asked you about "Keep together" because there is a similar bug (which they provided me with) when used with sub-reports of any level.


lucabaldini also had a very helpful post explaining how the report engine handles printing of subreports:
subreport re - heading to new page
http://forum.instantdeveloper.com/viewtopic.php?f=2&t=67622
I rarely use sub-reports because their query is executed every time for each "instance" in which they are contained ... there are many ways to create a report and I, if I can, try to incorporate the data into a single query and then, through groupings, I show the grouped data.

In other words, instead of having a report and putting a sub-report in the detail, I create a single report with a group where I group the data for the same field that changes with each detail (e.g. primary key).

However, a breakable sub-report is a sub-report that can be broken on multiple pages ... the idea itself of the breakable sub-report is a system forcing. I'll explain.

The system, as you know, formats one detail section at a time ... only if the section can be pasted on the page does the system embed it and move on to the next instance. This is not true of sub-reports (as you can also see from debugging). The system, when it has to, formats the entire sub-report as if the page were infinite. Then adapt the section in which the sub-report is contained and see if it fits on the page. If it does not fit and cannot be broken, the printing stops. If it fits and can be broken, the system breaks the section and incorporates, in the section, the portion of the sub-report (already formatted) that fits. Then, when it moves to the next page, it retrieves the remaining section from the cache and tries to paste it on the page, repeating the algorithm indicated above until the section is completely printed.At this point the system goes to the next page.

While incorporating the various pieces of the sub-report, if they are in the case of a group section and the "print on page change" flag is active, it makes a copy on the fly and pastes it on the page ... It does not re-format it but it simply clones ... unlike what is done for the "main cycle" of the report.

Using theguru's advice, I was able to make the report match the specifications. Report Example 1 in the attached project demonstrates the method. For me I found the "hide if empty" flags worked correctly for the details containing the subreports, it was the "Keep with next" that didn't do as I expected.

I've included the attached project (InDe 20.0 r9) so on the English side we have an example of how to use the before/after formatting events to determine if we need a page break or not. It can also hopefully serve as a reference project for a malfunction report to see if the "keep with next" can be improved when dealing with subreports.

If you load the example project, and print for the first date Report 1 and Report 5, you will notice the first 3 pages are identical. Starting on page 4, Report 5 starts a group at the very bottom of the page, when it should have moved it to the next page. Report 1 (using the before/after formatting events) moves it correctly to the next page.

Another point of interest in the example project is the technique it uses (before/after formatting events) to satisfy the first two requirements:
  1. Report Header on first page only. It should print at the "top" of the page (where the Page Header prints).
  2. Remaining pages have a page header.
It does in the OnFormatting event of the Master Page by changing the size and position (height and top) of the Page Body so it is "on top" of the Page Header for the first page, so the Report Header section (which is linked to the page body) prints where the Page Header does. The Page Header box is hidden for the first page.
After the first page it sets the Page Body top and Height back to the original values (so it is underneath the Page Header), and makes the Page Header visible again.

Note: this project includes an Access DB--the production version this is based on used MS SQL Server.
Report Examples Express.zip
Subreport Page Break Examples - InDe 20.0 r9 Express
(882.05 KiB) Downloaded 44 times

...jack
ljwilson
 
Posts: 559
Joined: 26 November 2013, 14:15

Return to Tips & Tricks

Who is online

Users browsing this forum: No registered users and 6 guests