Report Design: Select only records with Maximum ID

Select only records with Maximum ID

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=10927
Printed Date: 07 May 2024 at 1:58pm

Topic: Select only records with Maximum ID

Posted By: Kwirky
Subject: Select only records with Maximum ID
Date Posted: 23 Aug 2010 at 7:42pm

Hello,

I have a report which has 2 tables; app_quality_control and MainJobDetails.

I am trying to show a listing of all job number, whether they have had a quality control issue raised against them or not.

If a job has has quality control issues raised against it, it will increase the number of entries in the report depending on how many times the data has been updated. I have been able to show the Maximum ({app_quality_control.quality_control_id}) as the first set of records under each grouping of job number.

I want to be able to select somehow, only the records which have the maximum value, for those records with quality control issues. I still want to show all the records which have no QAs lodged.

So in the example below, I want to show all the records for job number 526878. For the next job, 527264, I want to only show the records which have the maximum ID, in this case 103514.

JobNo	Quantity	Issue?	quality_control_id	Result
526878	1,484	No QA
526878	0	No QA
526878	0	No QA
526878	34	No QA
526878	40	No QA
526878	10	No QA
526878	630	No QA
526878	0	No QA
526878	1	No QA
526878	0	No QA
526878	15	No QA
526878	753	No QA
526878	1	No QA


527264	61	QA raised	103514	Reprint
527264	41	QA raised	103514	Reprint
527264	0	QA raised	103514	Reprint
527264	1,227	QA raised	103514	Reprint
527264	0	QA raised	103514	Reprint
527264	0	QA raised	103514	Reprint
527264	0	QA raised	103514	Reprint
527264	0	QA raised	103514	Reprint
527264	15	QA raised	103514	Reprint
527264	1,206	QA raised	103514	Reprint
527264	1	QA raised	103514	Reprint
527264	1	QA raised	103429	0
527264	15	QA raised	103429	0
527264	1,206	QA raised	103429	0
527264	0	QA raised	103429	0
527264	0	QA raised	103429	0
527264	0	QA raised	103429	0
527264	0	QA raised	103429	0
527264	0	QA raised	103429	0
527264	1,227	QA raised	103429	0
527264	61	QA raised	103429	0
527264	41	QA raised	103429	0

Replies:

Posted By: DBlank
Date Posted: 24 Aug 2010 at 3:45am

group on job no

suppress the GH and GF

Go into the section expert

select suppress formul under the detial section and add your suppress condition there

NOT (table.qualitycontrolid=maximum(table.qualitycontrolid,table.jobno)

or isnull(table.qualitycontrolid))

Posted By: Kwirky
Date Posted: 24 Aug 2010 at 12:12pm

Hi,

I have tried as you suggested and it works perfectly.

Thank you very much :)