Too many sub-sub-summaries for one report?

[Dane]

OK, been beating my head against the wall about this one for a week or so....

Got a report I need to generate, on annual test scores for students, with sub-totals by teacher, by grade, by school, and a grand total for district-wide. So far, so easy.

The kicker is, my boss wants to see the scores separated out by sex, so essentially I need three sub-totals for each level of sub-summary-- scores for girls, scores for boys, and combined scores. Unfortunately, summary fields don't let you filter within the report (at least that I know of)-- i.e., I can't ask for "On the teacher subsummary level, give me Average Scores Total, Average Scores Girls, Average Scores Boys." The summary field is all-or-nothing based on the filtering criteria script performed before entering the report layout.

I'm beginning to think that this level of granularity can't be obtained just using summary fields in sub-summary parts, that I'll have to "brute force" it and use numeric or calculation fields for each detail and hard code them in.

But before going that route, I thought I'd ask to see if I'm overlooking some basic technique that is totally obvious to the more experienced among us.

Thanks--

Dane

Edited February 25, 2006 by Guest

[Ender]

Hi Dane,

If you don't mind seeing the boys and girls summarized in separate groups, you can add a Sub-Summary by Gender part, and include your summary Average field and Gender field on that. If using leading sub-summary parts, I'd put that as the first sub-summary part. If using trailing sub-summary parts, I'd put it last.

If you need the boys' and girls' scores on the same line, you'd need to add a calculated score for each gender, and summary fields that average those:

Score_Female (calculation, number result) = case(Gender = "F";Score)

Score_Male (calculation, number result) = case(Gender = "M";Score)

AveScore_Female (summary) = Average of Score_Female

AveScore_Male (summary) = Average of Score_Male

You could just put those summary fields on your existing report, replacing your current summary Average field on each part.

Edited February 25, 2006 by Guest

[Dane]

Hi, Ender,

Thanks for the response. The first suggestion is in fact what I've been beating my head against the wall about; when I do a sub-summary part with the field for sex in it, I don't get two reports, but only one, with either F or M in it (based on which was the last record in the match set, I think), and the score is the same as the teacher/class average, not for that sex only.

e.g., this:

becomes this:

(and, yes, the sex is included in the sort, so it should be working from that respect.)

I'm thinking the problems may be because my field for sex is in a related table, not the table the scores are in. (oh, yes, I forgot to mention that little detail, sorry....) This doesn't seem to work. Will go on to try your second suggestion.

Thanks!

-Dane

[Ender]

This type of report should work, even with a related sub-summary part.

Do you actually have different genders in each room?

Maybe there's something unusual with your relationships. Can you show the relevant parts of the table occurence graph?

[Dane]

Here you go:

I've highlighted the primary keys, since they're not always obvious (I inherited this DB, so standard nomenclature is somewhat spotty....)

4 tables, left to right:

TeacherMain is teacher name & ID number

TeacherAnnual is where & what grade that teacher is teaching that year (can't be part of TeacherMain since teachers can & do change schools &/or grades).

StudentAnnual is the both the annual record of enrollment and also the test scores for that academic year.

StudentMain is the student master record. Note that this is where the student's sex is recorded.

Class list/enrollment record is basically the intersection of the TeacherAnnual and StudentAnnual and the current academic year (a global, from a Utilities table). Looking at this from an outsider's perspective, I realize how odd it looks to not use the StudentAnnual primary key.

Hope this is clearer than mud....

Thanks for your time!

-Dane

[Ender]

Basing the report in the TeacherAnnual table is giving you problems because it only shows the Sex of the first student in each class. If you wanted to keep the report in the TeacherAnnual table, you'd need to use aggregate calcs and two separate relationships to StudentAnnual, filtered for each Sex (and a Sex field would need to be added to the StudentAnnual table.)

I'd suggest building the report in the StudentAnnual table instead, as this is where all the scores reside. This would allow the Sub-Summary by Sex part that your report needs.

[Dane]

Ender,

A thousand blessings upon your house. Grazi, multo grazi!

Now my poor forehead can start to heal...

--Dane

[Ender]

Glad to help. :tigger: