Annual reports with a variety of subgroups

[Damocles]

I don't think I really understand the "subsummary" function, so this may be an easy solution, and I just don't have the basic tools. If so, can someone refer me to a resource that explains subsummaries in better detail than the FileMaker User's guide.... I know I could do this by doing multiple searches with different criteria, but that seems archaic compared to what I am sure FileMaker can do.

For every study in our office, we record

Year (We record the exact date of submission, but I've converted it to a 4 digit number for the year)

Type (behavioral or biomedical)(Text)

Review Level (expedited, full board, or exempt)(Text)

Participant (student or faculty)(Text)

I'm interested in presenting an annual summary of the activity in our office by listing the number (and percentages) that will answer the following questions:

How many studies did we handle this year?

Here's where I start getting mixed up....

How many studies(each year) were biomedical? Behavioral?

How many were reviewed by the full board? How many expedited? How many exempt?

And, of course, the part where I have no idea....

How many of the biomedical were expedited?

How many of the biomedical were full board?

How many of the behavioral were expedited?

How many of the behavioral were full board?

Those categories need to be further broken down according to whether the studies are done by students or faculty.

AND

I'd like it to be a one-page form (one page per year) that I can print at any time by picking the appropriate year....

Thanks in advance for good advice and for not laughing.

Paul

[John Caballero]

OK...

Create some number calculations:

cIsBioExp: (Type="biomedical" and Review Level="expedited")

cIsBioFullB: (Type="biomedical" and Review Level="full board")

cIsBehavExp: (Type="behavioral" and Review Level="expedited")

cIsBehavFullB: (Type="behavioral" and Review Level="full board")

cIsStudent: (Participant="student")

cIsFaculty: (Participant="faculty")

Each one of these calculations will return a value of 1 if the conditions are true.

Create the follwong text calculations:

cYear_BioExp (Year & " " & cIsBioExp)

cYear_BioFullB (Year & " " & cIsBioFullB)

cYear_BehavExp (Year & " " & cIsBehavExp)

cYear_BehavFullB (Year & " " & cIsBehavFullB)

Create a number calculation cConstant that returns the value of 1.

Create a SelfJoin relationship (meaning you relate to the file to itself) based on Year (I'd name the relationship something like, "SELF_Year_to_Year")

Create the following additional SelfJoin relationships:

SELF_cYear_BioExp_to_cYear_BioExp

SELF_cYear_BioFullB_to_cYear_BioFullB

SELF_cYear_BehavExp_to_cYear_BehavExp

SELF_cYear_BehavFullB_cYear_BehavFullB

How many studies did we handle this year?

Sum(SELF_Year_to_Year::cConstant)

How many of the biomedical were expedited?

Sum(SELF_Year_to_Year::cIsBioExp)

How many of the biomedical were full board?

Sum(SELF_Year_to_Year::cIsBioFullB)

How many of the behavioral were expedited?

Sum(SELF_Year_to_Year::cIsBehavExp)

How many of the behavioral were full board?

Sum(SELF_Year_to_Year::cIsBehavFullB)

How many of the biomedical were expedited and are student-studies?

Sum(SELF_cYear_BioExp_to_cYear_BioExp::cIsStudent)

How many of the biomedical were expedited and are faculty-studies?

Sum(SELF_cYear_BioExp_to_cYear_BioExp::cIsFaculty)

Get the pattern?

So...you'll create a layout that will have a header and a Subsummary when sorted by Year.

In the Subsummary part, you'll just put the Year field.

In the Body, you'll put the calculcations you just created.

When you want to view the report, find the Year(s) you want to view and then sort the found set by Year.

Caveat - I haven't tested this, so the Big Spuds here may provide corrects/suggestions. Listen to them!!

[John Caballero]

One more thing...the Sums in my previous post are calculcated number fields...name them however you want.

[Damocles]

Thanks! I was looking for some weird field definition like:

Count(Level="Expedited" and Type="Biomedical" and Participant="Student" and Year=GlobalYear)

OR - since summaries are relatively new to my DB - not sure if you could nest subsummaries somehow....

I hadn't considered using a SelfJoin to filter out the year. I'll try it and let you know.

Paul

[Ugo DI LUCA]

John,

You're correct that relationships are the way to go here.

And as far as I can tell, this will just lead him where he wants.

Just a few ideas though for those intensive consumer of "Statistical datas".

Rather than involving "pure" calculated fields and relationships, you may use repeating fields as "storage" fields.

You won't mess up with related calculations that could seriously reduce the speed, specially when cross-calculated.

It won't involve a constant but rather an unstored concanation calc that would be used for several relationships to other indexed standard fields and concanation calcs, each left key being created within a loop.

Each repetition will be set by lookup to its proper position, still involving John's Sum(::Relationship) technique to set the content of the field.

Since Christian Jaeger introduced me to this technique, I'm using it succesfully in my solution where I need to store weekly, monthly, quarterly and yearly statistics.

The BIG advantage of this technique is that, not stricly related to any data structure, the statistics would stay with you even if you're updating your solution and structure.

The Caveat is that it needs some time to set...

But when running, I MUST say it is rather impressive. Thanks again Mr Jaeger !