Linking and placement of subreport 2

[catbert]

I am using CR 10 linked to MS Access via ODBC
I have been looking at this for so long I no longer have any idea what I am doing. I have three tables in a database - The first is a list of visited properties. The other two are linked and contain lists of streets (date visited/street name/area) linked to a list of all house numbers/names participating

I need a report to show all those visited, and the percentage now participating (we only visited those not participating but many people were involved already). My first stage report worked fine - linking on area and using a count of address I have numbers visted in each area and total participating. It looks like this:

I have a main report which looks at all visited customers:
GH1 - Area
Details - suppressed list of address details
GF1 - includes a count of number properties visited by Area

A subreport - also in GF1, shows any customer participating
SbRpt GH1 - Area
Details - suppressed address details
GF 1 - includes a count of properties participating by Area

In understand how to create a shared variable to get this figure onto my main report and perform the calculation but I cannot get the figure to display anything but 0 or 1. Obviously I need to link on address so I can match those now participating to those we visited, and only show the subbset I require.

But when I add this link to the subreport I get null if the subreport is in the RH and the 1 last matched record if it is in the group footer. I only get the correct matches if I put the subreport in the detail - then I get the list of all visited properties - with the occasional one showing a subreport entry - but I only want the summary and the group total on the subreport has reset to 1 all the way through.

I hope this is reasonably clear as I am obviously doing something fairly major wrong and I'm sure it should be obvious. I'll kick myself heartily when someone explains but any help gratefully received.

Cheers

CB

 

[lbass]

If you are going to link on address, then you need to place the subreport in the details section. Then insert a second and third detail section (detail_b and _c). Place the subreport in detail_b. In the subreport, create the shared variable and place it on the subreport:

whileprintingrecords;
shared numbervar partic := distinctcount({table.participant});

The result is probably a 1 or 0, since it is per address. Then in the main report, create these formulas (this assumes you want the calculation at the area group level:

//{@reset} to be placed in the area group header;
whileprintingrecords;
numbervar sumpartic;
if not inrepeatedgroupheader then
sumpartic := 0;

//{@reset} to be placed in detail_a (you can suppress the section):
whileprintingrecords;
shared numbervar partic := 0;

//{@accum} to be placed in detail_c (you can suppress this section):
whileprintingrecords;
shared numbervar partic;
numbervar sumpartic := sumpartic + partic;

//{@display} to be placed in the area group footer:
whileprintingrecords;
numbervar sumpartic;

-LB

 

[catbert]

Marvellous, thanks. I knew it was something to do with shared variables but I thought the error was in my subreport - it feels wrong to put a subreport in the details section.

However (sorry!) I can't suppress the section to display what I actually need to show - whenever I suppress the details all the figures reset to 0.

That sort of makes sense to me except you indicated that sections could be suppressed. I followed your post to the letter - any ideas what I may have done?

Have a star for clear concise instructions in the meantime.

 

[lbass]

You can't suppress detail_b, but the others you should be able to.

-LB

 

[catbert]

my apologies, you are right (of course) , unfortunately I have so many records it still leaves a lot of blank space - and I was in the middle where the return is 0 anyway. I have redcuced section b as small as possible and at least now I only have two pages with some blank space, not publishable but gives me what I need - much obliged!

 

[lbass]

Not sure if this will help in this case, but try formatting the detail_a section to "Underlay following sections".

-LB

 

[catbert]

underlay didn't help but it made me look in the right place + I just suppressed if section was blank - I have all fields in the subreport suppressed as I don't need to see them and now it works perfectly, One page with GF summaries only. Have another star for making my day ;o)