Building a large multi-level report with Table of Contents
I have a client that prints and collates a 275 page report twice a year.
The way they currently do this is print 7 different reports, each report has 5 sections (call these books), and within each book there may be between 1-5 chapters, and within each chapter there may be between 5 and 20 pages. They print each of these 7 reports separately to PDF files, then merge them into a master file, rearranging the pages to get them in the correct order, and then manually number each of the pages, lots of nug work.
They are looking for a solution which would allow them to print the entire report as a single document and since I've never even considered this level of complexity in a single report, I'm looking for recommendations.
I think I might be able to do this by creating a table which contains all of the combinations of Book, Chapter, and Section along with sort order and the report(s) which would be used for each of these levels. I think, I can create groups based on each of these levels and use the format event of each group to enable/disable certain controls in the group headers, and change the sourceobject of the subreport which I would embed in the Detail section of the report and also change the master/child relationship of that subreport based on the Book/Chapter/Section.
They would also like to automatically generate a Table of Contents, which can be printed separately for this data. I'm thinking I could use one of the group or page footer events (maybe format or print) to save the page number associated with the first page of each Book/Chapter/Section and then generate the Contents page(s) from that data.
Anybody have any ideas?
The way they currently do this is print 7 different reports, each report has 5 sections (call these books), and within each book there may be between 1-5 chapters, and within each chapter there may be between 5 and 20 pages. They print each of these 7 reports separately to PDF files, then merge them into a master file, rearranging the pages to get them in the correct order, and then manually number each of the pages, lots of nug work.
They are looking for a solution which would allow them to print the entire report as a single document and since I've never even considered this level of complexity in a single report, I'm looking for recommendations.
I think I might be able to do this by creating a table which contains all of the combinations of Book, Chapter, and Section along with sort order and the report(s) which would be used for each of these levels. I think, I can create groups based on each of these levels and use the format event of each group to enable/disable certain controls in the group headers, and change the sourceobject of the subreport which I would embed in the Detail section of the report and also change the master/child relationship of that subreport based on the Book/Chapter/Section.
They would also like to automatically generate a Table of Contents, which can be printed separately for this data. I'm thinking I could use one of the group or page footer events (maybe format or print) to save the page number associated with the first page of each Book/Chapter/Section and then generate the Contents page(s) from that data.
Anybody have any ideas?
<<Anybody have any ideas?>>
The table of contents is the easy part actually. You just need to follow the technique for doing "Page X of Y" over a group (basically what you were thinking...capturing data in a header/footer event, then printing that out making sure your doing two pass printing). There is an old MSKB article on this.
As for the report, something I just did was to string a couple reports together and make it look like they were a single report. I grabbed the page number at the end of each and passed it on to the next report. So my page numbering was correct, but I was not able to do "Page x of y", just "Page x".
If you can live with that limitation, then I can't see why it can't be done with a series of reports, although ending up with a single PDF might be an issue.
If you must do it all in one report so you can get "X of Y", then you might be able to do it with a lot of sub reports.
There's also the technique of an "un-bound" report just like there are with forms.
You drive the report with a single record (doesn't matter what) and in the OnOpen or whatever, grab the data you need and push it into un-bound controls. You can use MoveLayout, NextRecord, and PrintSection to control the report engine. So you don't necessarily need to do a dynamic report.
Sorry nothing specific other than you can do the table of contents for sure, but hopefully that gives you some different ideas.
If the reports are simple and straightforward without a lot of fields/totals, then I think I would try it first as one master report with a lot of sub reports. Let Access do all the work. If that would not fly, then one report pushing data into controls as I went along. Hard to know though without an example.
Jim.
The table of contents is the easy part actually. You just need to follow the technique for doing "Page X of Y" over a group (basically what you were thinking...capturing data in a header/footer event, then printing that out making sure your doing two pass printing). There is an old MSKB article on this.
As for the report, something I just did was to string a couple reports together and make it look like they were a single report. I grabbed the page number at the end of each and passed it on to the next report. So my page numbering was correct, but I was not able to do "Page x of y", just "Page x".
If you can live with that limitation, then I can't see why it can't be done with a series of reports, although ending up with a single PDF might be an issue.
If you must do it all in one report so you can get "X of Y", then you might be able to do it with a lot of sub reports.
There's also the technique of an "un-bound" report just like there are with forms.
You drive the report with a single record (doesn't matter what) and in the OnOpen or whatever, grab the data you need and push it into un-bound controls. You can use MoveLayout, NextRecord, and PrintSection to control the report engine. So you don't necessarily need to do a dynamic report.
Sorry nothing specific other than you can do the table of contents for sure, but hopefully that gives you some different ideas.
If the reports are simple and straightforward without a lot of fields/totals, then I think I would try it first as one master report with a lot of sub reports. Let Access do all the work. If that would not fly, then one report pushing data into controls as I went along. Hard to know though without an example.
Jim.
Dale,
One other idea, which is a cross between the two last things I mentioned; don't forget about possibly using temp tables.
Use a temp table to drive the report, filling it with the data you need. In doing this, you end up with a simple report (one table, always the same fields, so no sub reports) and Access handles getting the data into the controls (your not going the un-bound route).
You'd get "Page x of y" capability and still be able to do your TOC.
I've done this for multi-level bill of materials explosions in the past and it worked well.
Jim.
One other idea, which is a cross between the two last things I mentioned; don't forget about possibly using temp tables.
Use a temp table to drive the report, filling it with the data you need. In doing this, you end up with a simple report (one table, always the same fields, so no sub reports) and Access handles getting the data into the controls (your not going the un-bound route).
You'd get "Page x of y" capability and still be able to do your TOC.
I've done this for multi-level bill of materials explosions in the past and it worked well.
Jim.
ASKER
@Gustav, might end up going that route but was looking to see if anybody had done this level of detail in Access.
@Jim, thanks for the ideas. Page numbering generally just needs the "book #" appended to the page "3-1", "3-2". Some of the subs also have an x of y format for within that report. You may be right that I could string several sub-reports together and insert page breaks between those.
@Jim, thanks for the ideas. Page numbering generally just needs the "book #" appended to the page "3-1", "3-2". Some of the subs also have an x of y format for within that report. You may be right that I could string several sub-reports together and insert page breaks between those.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have not had a chance to return to this but am looking at embedding subreports into the main report, and possibly nesting subreports in some of those. Will let you know what I ended up doing sometime next week.
ASKER
OK, I've finally started work on the year-end "monster" report mentioned above.
I've tried putting the sub-reports in the Details section and in group footers (ensured that the first page showed up correctly before the sub reports), but what I'm finding is that the page headers of those sub-reports, which display both titles and column headers on the reports when printed individually, do not print when embeded into another report as a sub-report.
I've tried moving the page header and footer stuff into the report header and footers of the sub-reports, and this works, but as you would expect, if the sub-reports are more than one page long, I only get the header info on the first page of the sub-report and the footer info on the last page of the sub-report.
Anybody have any ideas?
I've tried putting the sub-reports in the Details section and in group footers (ensured that the first page showed up correctly before the sub reports), but what I'm finding is that the page headers of those sub-reports, which display both titles and column headers on the reports when printed individually, do not print when embeded into another report as a sub-report.
I've tried moving the page header and footer stuff into the report header and footers of the sub-reports, and this works, but as you would expect, if the sub-reports are more than one page long, I only get the header info on the first page of the sub-report and the footer info on the last page of the sub-report.
Anybody have any ideas?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Still working on this, it got bypassed with a couple of other aspects of the project but will be back on front burner this week.
OK.
Jim
Jim
ASKER
@ Jim/Helen, or anyone else with any great ideas.
I've attached a sample database with the main set of tabs (chapters) and a subreport, which has some of the details associated with each tab. The subreport (rpt_Budget_01) looks great when viewed on its own, but will not even render when embedded as a subreport. Any idea what could be causing this.
Keep in mind, I'm working with a database I just took over from someone else, so the structure may not be ideal and I have some leeway to make changes to it.
EE_Sample_db_SubReports1.accdb
I've attached a sample database with the main set of tabs (chapters) and a subreport, which has some of the details associated with each tab. The subreport (rpt_Budget_01) looks great when viewed on its own, but will not even render when embedded as a subreport. Any idea what could be causing this.
Keep in mind, I'm working with a database I just took over from someone else, so the structure may not be ideal and I have some leeway to make changes to it.
EE_Sample_db_SubReports1.accdb
I will take a look at it when I have some time (probably tomorrow).
I do see the content of the subreport on the main report (there isn't much content, though). When I open the report in print preview, I get a message about an expression that is too complex to evaluate, then the report appears normally (as far as I can tell). Do you have any idea what that expression could be? The queries used for the report and subreport don't have any complex expressions.
The TempVar was null, so I replaced it with a custom db property (I like this method, because properties persist until you change them). The year then appeared in the footer. Here is a screen shot of the report, opened in 64-bit Access 2010 running on Windows 10:
I turned off a few headers and footers that weren't needed, but I don't think that had any effect.
The TempVar was null, so I replaced it with a custom db property (I like this method, because properties persist until you change them). The year then appeared in the footer. Here is a screen shot of the report, opened in 64-bit Access 2010 running on Windows 10:
I turned off a few headers and footers that weren't needed, but I don't think that had any effect.
There is some material (the page footer) that appears in both the main report and the subreport -- is that intentional? I think you may not need so many subreports in any case. Could the logo image be placed directly on the report? And could you make a grouped query that includes the data in the subreport as well as the data in the main report? That might work better.
I did see one problem, though -- the textbox on the subreport uses 1 to evaluate a Boolean field -- I changed it to True, and put it in the query (so much easier to work with and debug than an expression in a control), and then it appeared in the subreport -- but not in the main report (I still got the error message about a too complex expression).
I might be able to redesign this report so that it works, but it would be better to have more data for testing.
I might be able to redesign this report so that it works, but it would be better to have more data for testing.
Dale,
Sorry I've been MIA on this.....but I'm in the middle of a wedding event; oldest son is getting married today.
I see Helen has made progress on it. I'll check back in on this Sunday when I should finally have some time to spend if needed.
Jim.
Sorry I've been MIA on this.....but I'm in the middle of a wedding event; oldest son is getting married today.
I see Helen has made progress on it. I'll check back in on this Sunday when I should finally have some time to spend if needed.
Jim.
That's fine with me -- I have some other work to do that will keep me busy over the next few days.
?
Sounds like you've already made some good progress on this.
I was only saying I would have some time to spend if we got stuck again on this and why I hadnt come back sooner.
Jim
Sounds like you've already made some good progress on this.
I was only saying I would have some time to spend if we got stuck again on this and why I hadnt come back sooner.
Jim
ASKER
Helen/Jim,
Thanks for looking into this. I've been traveling for a couple of days and have not had the time to dig down into Helen's comments. Will try to do so over the next several days.
Helen, I couldn't figure out what the "complex expression" is that shows up when the main report loads either. It may be in the grouping, but agree that it is not in the query used by either report.
Thanks for looking,
Dale
Thanks for looking into this. I've been traveling for a couple of days and have not had the time to dig down into Helen's comments. Will try to do so over the next several days.
Helen, I couldn't figure out what the "complex expression" is that shows up when the main report loads either. It may be in the grouping, but agree that it is not in the query used by either report.
Thanks for looking,
Dale
Dale,
I'm still out of pocket work/ee wise. Had the wedding first and right after that, my mom fell. She's pretty much OK, just banged up quite a bit. I haven't been in the office since last Tuesday as a result. Should be back at work tomorrow, but it might be a couple more days before I could look at this. Lot's of stuff piled up at this point :(
Jim..
I'm still out of pocket work/ee wise. Had the wedding first and right after that, my mom fell. She's pretty much OK, just banged up quite a bit. I haven't been in the office since last Tuesday as a result. Should be back at work tomorrow, but it might be a couple more days before I could look at this. Lot's of stuff piled up at this point :(
Jim..
ASKER
Still looking into this, but dropped down on my priority list.
ASKER
Thanks for the help, guys, but I was unable to accomplish this the way I wanted, with embedded reports.
However, I created a table which contains all of the sections of the report, the reportname that each section pertains to, the WhereCondition needed for opening the report. Then I use the report Open event to reset the page number, store that number back into the aforementioned table and simply loop through the table, generating the report, outputting it to PDF then merging it into the Master PDF file. Took a while to figure it out, but it is working like a charm.
However, I created a table which contains all of the sections of the report, the reportname that each section pertains to, the WhereCondition needed for opening the report. Then I use the report Open event to reset the page number, store that number back into the aforementioned table and simply loop through the table, generating the report, outputting it to PDF then merging it into the Master PDF file. Took a while to figure it out, but it is working like a charm.
Not that I have any experience in this but, you know, it's the feeling of the right tool for the right job, and this may not be Access.
/gustav