Sub-summary report of child records from context of parent

[mrmacmusic]

I would really appreciate some input on this, as I think I've got lost along the way trying to figure this out and whilst I have a working solution it seems somewhat convoluted (and is a bit sluggish to update)... 

I needed to create a report from the context of contacts so that this can display sub-summary data (sorted by contact::area and contact::category) of related survey data for each contact from the single most recent survey record (within a specified report date range) as well as count how many survey events took place in that date range. Because of the context, I concluded – perhaps wrongly – that I couldn't simply put the required summary fields in the surveys table and instead needed to pull the appropriate survey data for each contact into the parent record, then add the summary fields in that table.

To do this I've added a bunch of un-stored calculation fields, namely:

1) wasOpen – since I'm working with all contacts records, this evaluates whether the contact was added after the reportEnd date or closed before the reportStart data and should therefore be included (1) or excluded (0)

2) mostRecentSurvey – calculated using ExecuteSQL( "SELECT ''||surveyDate FROM data JOIN contacts ON fk_contactID = contact_ID WHERE fk_contactID=? AND surveyDate<=? ORDER by surveyDate DESC FETCH FIRST ROW ONLY" ; "¶" ; "" ; contacts::contact_ID ; globals::reportEnd ) and used to create a relationship to a single survey record – contactID = fk_contactID AND mostRecentSurvey = surveys::surveyDate

3) survey_Q1 – answer to first question in related survey multiplied by the value of wasOpen so will only have a value if the contact is being included in the report

4) surveys_carried_out – calculated using ExecuteSQL( "SELECT COUNT surveyDate FROM data JOIN contacts ON fk_contactID = contact_ID WHERE fk_contactID=? AND surveyDate>=? AND surveyDate<=?" ; "¶" ; "" ; contacts::contact_ID ; globals::reportStart ; globals::reportEnd  ) as this needs a figure for each contact whether it is included or excluded from the report.

5) summary field, total of survey_Q1

6) summary field, total of surveys_carried_out

etc (There are obviously other survey questions/dates being pulled in to the parent and summarised in the same way)

So when I set the global reportStart/reportEnd dates, goto the report layout, find all contacts and sort by contacts::area and contacts::category I get the required sub-summarised results, but I just feel I'm missing something and there is perhaps a better, more efficient approach... 

Thoughts?