Count Total of SubReport Variable in Main Report

[Mav3000]

Hi All,

I have a database with the following hierarchy. All levels link immediately to the one above:

{dtblSites.SiteID}
{dtblOptions.OptionID}
{dtblJobs.JobID}
{dtblAudits.AuditID}
{dtblAudit_SubScore.SubScoreID}

I have a report that has the following groups:

G1: {dtblAudits.D_Audit_Date, "Monthly")

G2: {dtblSites.Reference}

The aim of the report is to show Per Month, Sites and their Audit Scores.

In the Details section I have the list of Sub-Scores, basically:

{dtblAudits.D_Audit_Date}
{dtblAudit_SubScore.N_Sub_Score}

This is not grouped by {dtblAudits.D_Audit_Date}, so {dtblAudits.D_Audit_Date}repeats for each {dtblAudit_SubScore.N_Sub_Score}.

NB: If an audit has a low-scoring section, this secion is recorded as a 'sub score'. Not all Audits have Sub-Scores.

Problem 1: Whenever I add Sub-Score fields into the Details Section, the report immediately omits any Months and Sites where no Sub-Scores exist - even if a Sites has no Sub-Scores. I need to show all Sites - regardless of whether there were any Sub-Scores.

Problem 2: To get around problem 1, I use a sub-report to list Sub-Scores for each Site. This is linked by Site Reference to the Main Report.

I pass a variable to the main report to total the Average Sub-Score value (Average {{dtblAudit_Sub_Scores.N_Sub_Score})and the Sub-Score count (Count {dtblAudit_Sub_Scores.SubScoreID} for each Site.

The second problem I have is that in the main report, although I can show the Sub-Score Average and Sub-Score Count in GH2 (for each Site), I cannot summarise these fields in either GH1 or RH. I could do this if I wasn't using a SubReport, but need the Sub-Report to show all Sites.

I need to summarise the average and count of N_Sub_Score for each Month in RH.

Any help on either problem would be appreciated!

Richard