Shared Variable reporting more data than I expect

[Hillary]

I have been tasked to create a Mailing Label for our school that only pulls the youngest student from each family (based on each student's address) and sort the results by homeroom. I had already created the Youngest in the Family Mailing Label before I found out about the homeroom sort.

I created a subreport which shows only the youngest student at each address. The groupings are...
G1 - by School
G2 - by @Address
G3 - by Grade

In GF2 I placed a Shared Variable Student
Shared StringVar Student;
Student:={@Student Name}

In the Main Report I have the groupings...
G1 - by School
G2 - by Homeroom
G3 - by @Student Name (Last Name, First Name)

I placed the subreport in GH3a and the Shared Variable at GH3b. The problem is that the subreport shows all students on the main report, not just the Youngest students. When I run the subreport by itself, it only shows the youngest students.

Since the subreport is reporting all students and not just the youngest, the Shared Variable is doing the same.

Any ideas on how to keep the subreport/main report/shared variable just reporting the youngest students???


CR9
SQL Server
ODBC

Thanks,


Hillary

 

[lbass]

How did you link the subreport to the main report? How did you calculate the youngest per address to include in the shared variable?

-LB

 

[Hillary]

The subreport is linked to the main report via School ID and Student ID.

In the subreport, my groupings are...
G1 - by School
G2 - by @Address
G3 - by Grade

@Student (which is the Shared Variable) and Group #2 Name (which is the address formula) are both in GF2.

The Youngest per Address is done by putting the student's name in GF2. Since the Grade (Group 3) is in descending order, the yougest is the only one that shows.

There is no "formula" per say for the Youngest.

Thanks for your help!

Hillary

Hillary

 

[lbass]

I think you should handle this differently. Add a command (database->database expert->your datasource->add command) and enter something like:

Select min(table.`grade`) as youngest, table.`school`
From `table`table
Group by table.`school`,table.`address`

Link the command to the main table by linking {command.youngest} to {table.grade}, and also link {command.school} to {table.school} and {command.address} to {table.address}. If you have the option (not sure of your version), choose link options->enforce both.

This should return only one person per address. You can then group by school and homeroom and only have one person show up per address.

-LB