Correct way to make a grouped report...

hsm

I can make a simple cross tab displaying data from one department (The underlying sql selects just that dept).
e g
name.....grades
...A.......1..2..3..4
...B.......2..4..6..8
...C.......5..6..7..8
...D.......2..6..4..3

Now I want to extend the report to a grouped report that shows a separate cross tab for each department. (one for each group, with the selection removed from the sql)
eg

Dept A <

---

group header
name.....grades <

---

data for that group
...A.......1..2..3..4
...B.......2..4..6..8
...C.......5..6..7..8
...D.......2..6..4..3

Dept B <

---

group header
name.....grades <

---

data for that group
...X.......6..7..8..9
...Y.......3..5..7..6
...Z.......1..2..3..4


How should I lay out the bands and cross tab object so that each cross tab only shows data from the corresponding department ?

gpi

Use two datasets: first for unique group header's values (Master), second for group data (Detail). Use cross-tab with detail dataset

hsm

gpi wrote: »

Use two datasets: first for unique group header's values (Master), second for group data (Detail). Use cross-tab with detail dataset

Thank you but I always seem to get a key violation when I open the detail dataset. linked via the 'master Source' and 'MasterField'

My master dataset (called dsUniqueClassSets) contains unique ClassSets (field name TheClassSet), my detail dataset contains records which include a ClassSet field (coming from the same original table, called ThisClassSet)

in the detail dataset I have set the masterSource to be 'dsUniqueClassSets' and the master field to be 'TheClassSet', linked to the detail field 'ThisClassSett'
I get the error as soon as I open the detail dataset - before I even run the report/

The master query sql is

select distinct ClassSet as TheClassSet  from Grades order by ClassSet

and the detail sql as

SELECT 
      Students.LastName || ', ' || Students.FirstName as name,
      Students.Gender as gender,
      Students.KS2APS as 'Ks2 APS',
      Students.KS2Band as 'Ks2 Band',
      Grades.GradeDate as TheGradeDate,
      Grades.ClassSet as ThisClassSet,
      Grades.CurrentGrade as ThisGrade,
      Grades.SubjectCode as Subject,
      
      T.ClassSet as OtherClassSet,
      T.subjectCode as OtherSubjectCode,
      T.CurrentGrade as OtherGrade
FROM 
      Students
      INNER JOIN Grades ON (Students.UPN = Grades.UPN)
      Inner Join (select Grades.upn,  Grades.ClassSet,   Grades.SubjectCode,  Grades.CurrentGrade from grades) as T on ( T.upn = students.upn)
order by  Grades.ClassSet,Students.LastName || ', ' || Students.FirstName

so you can see that the 'TheClassSet' in the master will occur only once and the corresponding 'ThisClassSet' in the detail may occur many times but as they both come from the same table the master should always match one or more detail records.
Any idea why I get the key violation?

gpi

Why I don't see
WHERE Grades.ClassSet = [img]style_emoticons/<#EMO_DIR#>/tongue.gif" style="vertical-align:middle" emoid=":P" border="0" alt="tongue.gif" />1 in your detail sql?[/img]

hsm

gpi wrote: »

Why I don't see
WHERE Grades.ClassSet = [img]style_emoticons/<#EMO_DIR#>/tongue.gif" style="vertical-align:middle" emoid=":P" border="0" alt="tongue.gif" />1 in your detail sql?[/img]

I'm sorry, I don't understand. Why do I need a where clause and what should Grades.ClassSet be equal to?
I'd like to understand this method - especially if it is fast - but I have solved the original problem using your idea of two datasets but with a datasource pointing at the master. Then in the OnDataChange event of the datasource I set the range of the detail dataset. ie

currentClassSet := cdsUniqueClassSets.FieldByName('TheClassSet').AsString;
cdsGradeXtab.SetRange([currentClassSet], [currentClassSet]);

That seems to work quite well as it only accesses the database once, when the datasets are opened, instead of re-querying it each time the master changes, which I suspect is what using the MasterSource and MasterField would do.