Group level 1 - how to subtract one from another?

[hallux]

Crystal version 11 Developer
OLEDB to MS SQL Server 2000

I need some assistance figuring how to complete this report. It was originally created in Excel with two sheets.

One showing the rolled up group totals and a second sheet showing all the data/details for each student. I am putting together a crystal report that will hide the details lines and roll up the values to the group 1 level, which will show to the report user. When the details are needed, they will drill down.

Example data

Student details from the SQL query result set:

"Student", "Home Room Teacher", "School Days Attended"
Larry, Falstaff, 100
Moe, Falstaff, 60
Curly, Falstaff, 55
etc.

Expected output

Grouped by Teacher:

"Home Room Teacher", Total Students", "Ave School Days", Expected Ave Total Days", Total Days Difference"
Falstaff, 55, 85, 80, 5


So what the report needs to do is add up all of the students (I used the count function on "Student") for the "Total Students" grouping, then obtain an "Ave School Days" average of the "School Days Attended", then the report actually pulls a historical-based number from another table to obtain the "Expected Ave Total Days". Up to this point I have been successful in using the count function on "Student" field to obtain "Total Students" and for "Ave School Days" using the Summary option with "calculate this summary" as average.

The kicker is that I am not sure how to subtract the "Expected Ave Total Days" value from "Ave School Days" average to obtain the "Total Days Difference" result. I'm also wondering if there is a name for this kind of report, so I can keep searching for ideas.

Thanks for any assistance.

Thanks,
-Hallux

 

[synapsevampire]

The only unknown I can see from all of this is how to pull the expected average total days.

You explained it " then the report actually pulls a historical-based number from another table to obtain the "Expected Ave Total Days", such that a miracle occurs and you have the expected days, we need clarification.

Is this other table linked in some fashion?

One would assume that there's a period involved, and that you link via dates or a period, From your sample data, no dates nor period exists, so either explain the data in full, or if this is all of the data, then obviously you have no way to determine which period this is for and can't pull the other number.

Assuming that you have a link on a period, you should be able to just join it on the period, or if dates, then you might require a subreport with a shared variable to return the data (assuming it's a date field).

Right click the group header and select insert section below, place the subreport in the second group header section, link via the date, add in a formula such as:

whileprintingrecords;
shared numbervar MyValue:= {table.value}

and then adjust the dates that are passed to the subreport in the subreport->Report-Selection Formulas-Record as in:

{subreporttable.date} >= {pm->start Date}
and
{subreporttable.date} <= {pm->end Date}

Note that the record selection will have something like:

{subreporttable.date} = {pm->start Date}
and
{subreporttable.date} = {pm->end Date}

by default.

Anyway, please avoid just stating that another table exists, describe it's relationship and data, and don't show example data and then talk about data fields not in the data, you're not going to get a concrete answer, you're forcing people to ask for basic logical information and guess.

-k