Multiple Reports in 1 report

[10Pints]

Hi
Using Access 2010
I have a request to create a report that has 4 multipage reports in one
2 of these are summaries of the other two - but they want the summary reports as a separate listing not interleaved with their associated "detail" reports.

To explain further the 2 top level reports are by system - listing the performance of each system in a contiguous report - one for type 1 performance, the second for type 2 performance.
These 2 top level reports are then broken down by subsystem in the third and fourth report - again these report detail only the subsystem performance - and not to include the aggregated system data as a footer might be used to do.

If I put 2 multipage sub reports on one report - it works in design mode - but if I save the report and run it it complains that the report is bigger than the page (which is true) and then only displays one report without its header and then only the first few records that fit in the page. The second report is not visible and there are no scroll bars.
When I print it to paper or .XPS file it sort of works but there are blank pages after each page of the report.

I was going to supply one query that gave all the data at subsystem level and then let the report pull the relevant fields.

I need to get rid of the blank pages and I would like parameterise the sub reports so I can use 1 sub report "template"
and pass the data type and node type (system or subsystem) from the master report.

I thought this would be easy ... but oops I need some help with this one!



I could drive the 4 reports from the C ++ front end code easy enough - but I guess there is a smarter way of doing it ...

Thanks in advance for any advice kindly proffered

regards

10 Pints

[June7]

Not sure what you mean by 'multi-page' - are you using page break controls?

What section did you put the subreports in?

If you want to provide db for analysis, follow instructions at bottom of my post.

[LaughingBull]

do you have separate queries for all 4 reports?

[10Pints]

Hi June - by multi page I mean each of the sub reports can have more than one page - so it is different from my normal use of a sub report as header in my project.
I put a page break between the reports.
I will make a simple "shuttle" database with the reports in and send to you along with the requirements nd my current design doc
But Question: you refer to instruction at the bottom of your post? - sorry I dont see that - please expound

Hi LauhingBull my plan was to have one query for all sub reports and then get use the Master fields link idea to get the Master report to supply 2 flags to each child report (at design time) 1 for the form performance type (MCIT or PCIT) and one for the node type (system or subsystem) - but I am not sure how yet but I think that is the way I should do this ...

I will upload a shuttle mdb file shortly when I have sussed the upload technique on this site.
Thank you both!

[10Pints]

I have made a cut down version of the database and supplied the client requirements and my design thus far

There are 2 "scenarios" of solution - the first and most elegant is to use the report to do most of the work - using the ideas we are talking of here.
The second is a way I know I can implement using the current reporting c++ toolkit I have in place to drive the 4 separate reports (first populating the query paramerters) and then print the reports in turn

[June7]

Don't need C++ just to print reports in sequence. VBA can do that. Will try to look at your db tomorrow.

[10Pints]

Yes sure ... but the App is is written in C++
Thanks for looking at it

[June7]

The instructions are in my signature area at bottom of post.

I have done reports with subreports that span multiple pages and use page break between the subreports. You are using report Header_main in two places and Progress Report New Subreport in two places. There is no connection between reports - no synchronization because Master/Child Links properties are not utilized. None of this makes sense to me.

I have never done report/subreport/subsubreport arrangement.

Your frontend is programmed in C++?

[LaughingBull]

Watch this video youtube https://www.youtube.com/watch?v=2XGQmVVj1jA

[10Pints]

Hi June thanks for looking at that.
The test db I uploaded did have the bottom 2 subreportw too wide for the page - and that was causing the blank page issue - sorry I missed that obvious one.
That now leaves me with populating the subreport node type and form type fields from the master report
I see Laughing Bull has posted a a comment - so I will now check this out - thanks Lauhing Bull.

The nested subreports is the way i have used to resue common stuff in the headers like company logo and name - it is a dubious one as the editing of controls can be a pain
The idea is when my client sets the system up for a new client of theirs then I only have to update the title and logo in one place - a row in a table
Yes the front end is entirely in C++ as I developed this from 13 years ago and I feel it is only recently that the .NET environment has become sufficiently powerful to make the change from the toolkit I wrote
(much of my system is data driven by the system interrogating the DB schema to populate grid combo boxes, workout dependencies during table imports etc.)

Version 2 is imminent after this release and it will be a service oriented .Net C# offering - I am busting to get at it...
Thanks folks I am going to study the video Laughing Bull has advised

Thanks all for your valued help!

Now it is different story of course

[10Pints]

mmm
I still get the blank sub report issue and it is driving me nuts
I have many other reports that use this idea of a report and a sub report header.
However in this new report I keep getting the sub report ( I use for the header) displaying blank
It is not a Link Master/ Child fields thing as the other reports don’t use that and they work
Please see the latest shuttle.mdb that has the old report - which displays the sub report and the new report which does not.
It must be something easy ... but I cannot find it.

Kind regards

Terry

[June7]

Don't understand data relationships.

Relationships window shows Project links to System (id to PID) and System links to Subsystem (ID to PID) - should that be ID to SID?

SID and PID fields are in all 3 tables - why?

Move the controls of ProgressReportNewHdr into Report header section, not Page Header.

[10Pints]

Ah ha! Solved it thanks!! That works - I have tried for ages to get that to work and had no clue I needed to have the sub report controls in the main section.
Where would I have found that? - My searches on google did not reveal it - thanks June!

The schema structure ...
Ah now if I was using SQL server or DB2 etc. I can have recursive queries, but in access not.
The hierarchy represented is 7 or so levels deep starting with a single project type at the top and this broken down into child systems and that is broken down into child sub systems ... Commpacks MCPacks etc. etc.
You are right the data could be normalised by separating the parent / child part in to a node table and then joining the node
PID is the parent id. SID is the status id - these objects have a life cycle that is in 3 to 5 stages (groups of statuses representing the states at the main stages of the work) and the system needs to track this.

It is part of future development to normalise the parent/child and the progress elements of the hierarchy. But it does not pose a problem at the moment.

Thanks June you solved it for me.

[June7]

Just think about it - main report controls pagination, therefore page header/footer of subform does nothing, doesn't even display.

If by recursive query you mean a table joined to itself - yes, Access can do that. However, probably not sophisticated enough for multiple tiers.

[10Pints]

Ah right - I copied an existing one but moved the controls I guess to the header not realising the impact.
Thanks June - please take full credit for this and how do I attribute that credit to you and close the thread?
Pls advise

Kind regards

Terry