Grouping

[ggies07]

I have a very odd grouping problem that I can't seem to figure out.....This report I've been using year after year, now they want it sorted by homeroom teacher, while still having the child's name. Every time I switch the first group from parent name to Homeroom teacher name, it puts only a few set of students in on the report and gives them all the same homeroom.....there is something in the formulas that is doing this and I can't seem to find the fix.......they seem easy to me, but i'm going in circles.

Group 1 - parent name
group 2 - student name

Formulas:

siblings name:
WhilePrintingRecords;

StringVar Student1;
StringVar Student2;
StringVar Student3;
StringVar Student4;
StringVar Student5;
NumberVar Sibling;

If NumberVar Sibling = 1 then Student1 := {StsSt.StsSt_Nickname} + " (" + IF {StsStHrT.StsStHrT_Lastname} = "Centeno" then Left ({StsStHrT.StsStHrT_Nickname},1 ) + ". " + {StsStHrT.StsStHrT_Lastname} + ")" else
if {StsStHrT.StsStHrT_Lastname} = "Meese" then "Meese/Gonzales" + ")" else
if {StsStHrT.StsStHrT_Lastname} = "School" then "Middle School" + ")"
else {StsStHrT.StsStHrT_Lastname} + ")" else
If NumberVar Sibling = 2 then Student2 := {StsSt.StsSt_Nickname} + " (" + IF {StsStHrT.StsStHrT_Lastname} = "Centeno" then Left ({StsStHrT.StsStHrT_Nickname},1 ) + ". " + {StsStHrT.StsStHrT_Lastname} + ")" else
if {StsStHrT.StsStHrT_Lastname} = "Meese" then "Meese/Gonzales" + ")" else
if {StsStHrT.StsStHrT_Lastname} = "School" then "Middle School" + ")"
else {StsStHrT.StsStHrT_Lastname} + ")" else
If NumberVar Sibling = 3 then Student3 := {StsSt.StsSt_Nickname} + " (" + IF {StsStHrT.StsStHrT_Lastname} = "Centeno" then Left ({StsStHrT.StsStHrT_Nickname},1 ) + ". " + {StsStHrT.StsStHrT_Lastname} + ")" else
if {StsStHrT.StsStHrT_Lastname} = "Meese" then "Meese/Gonzales" + ")" else
if {StsStHrT.StsStHrT_Lastname} = "School" then "Middle School" + ")"
else {StsStHrT.StsStHrT_Lastname}+ ")" else
If NumberVar Sibling = 4 then Student4 := {StsSt.StsSt_Nickname} + " (" + IF {StsStHrT.StsStHrT_Lastname} = "Centeno" then Left ({StsStHrT.StsStHrT_Nickname},1 ) + ". " + {StsStHrT.StsStHrT_Lastname} + ")" else
if {StsStHrT.StsStHrT_Lastname} = "Meese" then "Meese/Gonzales" + ")" else
if {StsStHrT.StsStHrT_Lastname} = "School" then "Middle School" + ")"
else {StsStHrT.StsStHrT_Lastname} + ")" else
If NumberVar Sibling = 5 then Student5 := {StsSt.StsSt_Nickname} + " (" + IF {StsStHrT.StsStHrT_Lastname} = "Centeno" then Left ({StsStHrT.StsStHrT_Nickname},1 ) + ". " + {StsStHrT.StsStHrT_Lastname} + ")" else
if {StsStHrT.StsStHrT_Lastname} = "Meese" then "Meese/Gonzales" + ")" else
if {StsStHrT.StsStHrT_Lastname} = "School" then "Middle School" + ")"
else {StsStHrT.StsStHrT_Lastname} + ")"

student name:
{StsSt.StsSt_Nickname} + " (" + IF {StsStHrT.StsStHrT_Lastname} = "Centeno" then Left ({StsStHrT.StsStHrT_Nickname},1 ) + ". " + {StsStHrT.StsStHrT_Lastname} + ")" else
if {StsStHrT.StsStHrT_Lastname} = "Meese" then "Meese/Gonzales" + ")" else
if {StsStHrT.StsStHrT_Lastname} = "School" then "Middle School" + ")"
else {StsStHrT.StsStHrT_Lastname} + ")"

studnet 2,3,4:
WhilePrintingRecords;

StringVar Student2;

 

[hilfy]

It looks like in your data you can have multiple students in a "family" with the same parent. However, each of those students will likely have a different homeroom teacher. So, the logic that is working in the parent report probably will not work in the teacher report.

So, I would create a new report from scratch, focusing on the teacher/homeroom/student relationship instead of the parent/student relationship.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[ggies07]

Hey Dell,

That makes sense. Though I've tried starting with a group by homeroom, then adding the student relationship names (parents), but I get stuck where every child of the family prints out under the Parent name. side note - these are name tags and in the past it has the parents name and then their children with each homeroom listed like you talked about.

I'm freaking out a bit because they threw this at me and want it in 3 days. Ah change........

 

[ggies07]

it like the homeroom field I use to group with does not separate from the field that goes by the student's name in the report.....does that have to do with pulling it from the database or just setting it up correctly?

 

[hilfy]

It all has to do with how you link up your data and then display it in the group. I would have to know more about how your tables are configured, but here's a general overview of what I would do. This assumes that each student has its own record in a "student" table and that the student's home room is listed in that record:

Add the Homeroom table
Add the Student table
Link from Homeroom to Student

Get your report configured so that you have the students showing by homeroom.

Now, one at a time, add any additional tables that you need to get data like parent names, homeroom teacher, etc.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[ggies07]

interesting....I'll see what I can do. If it helps, I'm using a .mdb file from our database. Each child has their own record with the homeroom teacher name on it. So I'm pulling an export from the student side with that info: student name, homeroom teacher, parent names.

after reading your response again, I think that's where this is going sour, tell me if this might be it:

We don't have a separate homeroom and homeroom teacher. The student only has the homeroom teacher on it's record and that's what I'm using for both the grouping and the field in the report, which would screw it up no?

 

[ggies07]

I can get the parents to show up in the right homeroom teacher group, it's just adding the student + plus their teacher back under the parent names.......

 

[hilfy]

Is parent name also in the student record, or is there a separate table for parents? If there are multiple tables, please provide information about table names and fields.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[ggies07]

Well, I'm not directly dealing with SQL code tables. Our database has a GUI interface, so the way I see it is the parents names are on the Relationship tab on the student's record.

Now, there is an Individual section in our database and that is where the parents records are located. Maybe I have to start the export from the Individual side?

Does that help?

 

[hilfy]

You have to know something about the tables in order to be able to effectively design reports. Can you post the existing SQL from your report? I might be able to figure out something from there.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com