Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Group in 10.0

Status
Not open for further replies.

kinvie

Technical User
Aug 4, 2004
21
US
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!!!
 
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
 
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.)

[yinyang] Madawc Williams (East Anglia, UK) [yinyang]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top