Crystal Report Select Only First Row

Question

Let say I have the following 2 tables:

Table A:

CompanyCode
LocationCode
FormNumber
EmployeeNumber
...

Table B:

CompanyCode
LocationCode
FormNumber
EmployeeNumber
ClaimDate
...

Table A and Table B is joined using all the 4 parameters (CompanyCode,LocationCode,FormNumber,EmployeeNumber). These 4 parameters will be input for the report. One row in table A can have multiple rows in table B.

In the Crystal Report, for each record in table A, I want only the row with the earliest ClaimDate in table B.

Using SQL, this can be accomplished using:

SELECT        *
FROM          TableA a
INNER JOIN    TableB b ON 
              a.CompanyCode = b.CompanyCode AND
              a.LocationCode = b.LocationCode AND
              a.FormNumber = b.FormNumber AND
              a.EmployeeNumber = b.EmployeeNumber
WHERE         a.CompanyCode = @CompanyCode AND
              a.LocationCode = @LocationCode AND
              a.FormNumber = @FormNumber AND
              a.EmployeeNumber = @EmployeeNumber AND
              b.ClaimDate IN
             (SELECT   MIN(b.ClaimDate)
              FROM     TableB b 
              WHERE    b.CompanyCode = @CompanyCode AND
                       b.LocationCode = @LocationCode AND
                       b.FormNumber = @FormNumber AND
                       b.EmployeeNumber = @EmployeeNumber )

But I'm not so sure how I can do this in Crystal Report. I tried using SQL Expression, but it seems SQL expression cannot pass the report input.

Actually the data displayed does not involve the values. Just the calculation in the backend. There are 2 sections in the report, and it will be surpressed/not based on the field values. So in that query, I will get another field, name it Amount, so it will be the Amount of the earliest date. Then this Amount will be converted to other value, using the currency table. If this amount exceeds some specific value, it will surpress one section and display another. Vice versa. So I'm thinking the above as the inital step to solve this problem. — rcs
try something like this... place the data in details section and sort according to the decending order and then write supress as if recordnumber=1 then false else true.. This is just an idea.. not sure whether it will work.. give it a try — Siva

craig craig · Accepted Answer · 2014-06-24T12:13:03

You will need to create 4 parameters (CompanyCode, LocationCode , FormNumber, EmployeeNumber) in your command.

The command's query:

SELECT        *
FROM          TableA a
INNER JOIN    TableB b ON 
              a.CompanyCode = b.CompanyCode AND
              a.LocationCode = b.LocationCode AND
              a.FormNumber = b.FormNumber AND
              a.EmployeeNumber = b.EmployeeNumber
WHERE         a.CompanyCode = {?CompanyCode} AND
              a.LocationCode = {?LocationCode} AND
              a.FormNumber = {?FormNumber} AND
              a.EmployeeNumber = {?EmployeeNumber} AND
              b.ClaimDate IN
             (SELECT   MIN(b.ClaimDate)
              FROM     TableB b 
              WHERE    b.CompanyCode = a.CompanyCode AND
                       b.LocationCode = a.LocationCode AND
                       b.FormNumber = a.FormNumber AND
                       b.EmployeeNumber = a.EmployeeNumber )

Crystal Report Select Only First Row

2 Answers