Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

Too many sub-sub-summaries for one report?


This topic is 6859 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted (edited)

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 by Guest
Posted (edited)

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 by Guest
Posted

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:

layout.jpg

becomes this:

results.jpg

(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

Posted

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?

Posted

Here you go:

WRRelationships1.jpg

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

Posted

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.

This topic is 6859 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.