How to use sub-summary properly?

[barryfh]

Hello!

I want to generate 2 reports. The format is shown in the pdf within the attached archive file.

Following the explanation in FM User's Guide, I created the fp7 within the attached archive file. The layout "aggregated monthly report," in Browse mode, scarcely looks like what I'm trying to achieve! In Preview mode, it is totally blank.

I searched these forums and found a zip file named "Report_Date." It works perfectly.

In comparing "Report_Date" with my file, I must be missing the obvious as to how come it works perfectly while mine flops.

Additionally, I am totally puzzled by the following behavior of "Report_Date." 1. In Browse mode, the layout "MonthYearAmt_Report" shows "2007" and 1 January record. 2. In Preview mode, the same layout shows "2005" and 1 March record, "2006" and 1 record for each of 5 months, plus "2007" and the 1 January record.

How does that work??? Could someone please explain to me how FM does that?

And what do I need to do differently in the fp7 file to achieve the report formats in the pdf?

Many thanks in advance and cheers!

BFH.zip

[mr_vodka]

For a subsummary to work, you need to sort it by a certain field . This is usually done by running a script that goes to the layout, sorts the records, and then goes to preview mode. Try using the wizard in FM. You will need to create two seperate reports. The first one is pretty straight forward. The second detailed report requires some work.This is because you are using checkboxes and summary reports are usually based on one value items, so you would either need to seperate out your 'targeted audience' field, or need a calc for each one using a PatternCount ().

Also in your PDF what value are you expecting for the second report under Total Participants for each targeted audience category. IOW, if record 1 has both Admin and Student checked with 5 participants and Record 2 has Admin and Student Checked with 3 participants, when you run the report, what should they see when they are both in the same month? This doesnt seem to make that much sense because we cant exactly use 7 participants for both because then when you add all the categories up it will be 14 and that is wrong since there wasnt 14 participants.

[barryfh]

Hello John,

Many thanks for your prompt and helpful reply.

I will need to study your reply and get back tomorrow.

Meanwhile, if you have time, could you please explain to me how FM's reports and subsummary features work per my question above about "Report_Date?"

Thanks and cheers!

[mr_vodka]

Again, your sample file did not have a sort script, and also your subsummary parts were incorrect.

Take a look at the modified file.

Also take a look at these old thread regarding checkboxes and summary reports.

Thread 1

Thread 2

summary_stats_2.zip

[barryfh]

Hello John,

Thank you again very much for your time and helpful replies.

For a subsummary to work, [color:red]you need to sort it by a certain field. This is usually done by running a script that goes to the layout, sorts the records, [color:red]and then goes [color:red]to preview mode.

Could you please explain how come sorting and preview mode are needed for a subsummary to work? Is this intuitively obvious and I'm missing it? I've read pages 48 and 68 in FMP 8 User's Guide without spotting anything about this!

Otherwise, I am just doing this mechanically without understanding the logic behind it.

The second detailed report requires some work. This is because you are using checkboxes and summary reports are usually based on one value items, so you would either need to seperate out your 'targeted audience' field, or need a calc for each one using a PatternCount ().

Excellent point; many thanks!

I played with PatternCount () the other day and got a completely different result than what I was looking for.

I therefore separated out the “targeted audience” field. Very helpful suggestion!

A revision is attached.

This revision has one lingering problem.

Can you please tell me how I can arrange for the name of each month ("January"; "February") to be on the "admins" line in the disaggregated report instead of on the "Total" line?

I have tried everything I could think of, but am stuck.

Many thanks!

Also in your PDF what value are you expecting for the second report under Total Participants for each targeted audience category?

I expect the value that's in the monthly total participants field in the fp7 file I uploaded. For January, that value would be identical to the one for January in the first report under Total Participants.

IOW, if record 1 has both Admin and Student checked with 5 participants and Record 2 has Admin and Student Checked with 3 participants, when you run the report, what should they see when they are both in the same month?

Excellent point! Thank you for pointing out this flaw!

This doesn't seem to make that much sense because we can't exactly use 7 participants for both because then when you add all the categories up it will be 14 and that is wrong since there weren't 14 participants.

I believe the the monthly total events would have a problem in the above scenario rather than the monthly total participants.

Unless I'm missing something, the attached revision should take care of this issue.

Thank you again very much for your time and help.

Cheers!

Summary_stats_3.fp7.zip