Group in 10.0

[kinvie]

I am looking for some help with grouping in Crystal 10.0. I have a report that list people in multiple records based on the tests they have had done. I only want to see the first test of the month (I found this by choosing the minimum date and placed this in the group)and then group on the value of this test result. I am running into the person showing up in multiple groups. Here is what my output looks like

8.0-8.9
123 Person 1 6/21/2005 8.6
456 Person 2 6/19/2005 8.5

9.0-9.9
789 person 3 06/06/2005 9.1
123 Person 1 6/01/2005 9.5

I really only person 1 to show up in the 9.0-9.9 group since that test result was earlier in the month. Is there a way to only have this person show up once on the report and the on time based on the earliest date of the month?
Thanks so much!!!

 

[lbass]

Try using "Add Command" as your datasource, where you write the SQL Query, so that you can create a subquery, as follows:

Select
table.`ID`, table.`Name`, table.`score`,
(select min(A.`date`) from table A where
A.`ID` = table.`ID` and
{fn Month(A.`date`)} = {fn Month(table.`date`)})
From `table` table

If you limit the report to one month, this would return one date per ID. If you report on more than one month, the ID first date per month would appear for each ID.

-LB

 

[Madawc]

If that doesn't work, group by day and sequence by time within day. Put the details in the group header and suppress the detail lines. This gives you the first member of the group. (Or last, for the group footer.)

Madawc Williams (East Anglia, UK)