Skip to Content
0
Former Member
Oct 05, 2009 at 05:50 AM

Report Formatting-record in single row.

7 Views

Hi,

I want to display records as below (in one line) in crystal report by fatching records from below two tables

SAMPLE_ID            ENT_ON                      NON_COMPLIANCE
-------------------- ----------------------- ----------- --------------
140                  2009-08-27 12:36:43.000            F

-When any records got value other then NULL in Non_compliance column, it should display "F" (program is done to display "F".

when I run the report it displays as below:

SAMPLE_ID            ENT_ON                  NON_COMPLIANCE
-------------------- ----------------------- ----------- --------------
140                  2009-08-27 12:36:43.000 F
140                  2009-08-27 12:36:43.000 NULL
140                  2009-08-27 12:36:43.000 NULL
140                  2009-08-27 12:36:43.000 F
140                  2009-08-27 12:36:43.000 F
140                  2009-08-27 12:36:43.000 NULL

Table-A

SAMPLE_ID            ENT_ON
-------------------- -----------------------
140                   009-08-27 12:36:43.000

Table-B

SAMPLE_ID            PA_N        ST_N        COMPLIANCE  NON_COMPLIANCE
-------------------- ----------- ----------- ----------- --------------
140                  1           39          0           14
140                  2           39          NULL        NULL
140                  3           39          NULL        NULL
140                  4           39          1           6
140                  5           39          1           9
140                  6           39          NULL        NULL

However, when the report is execurited it shows records multiple time. Is there any way to display one record in one row where any of the record match to sample_id from table-b?

SELECT "table-a"."SAMPLE_ID", "table-a"."ENT_ON", "table-b"."NON_COMPLIANCE"
 FROM   "DB"."dbo"."table-a" "table-a" 
		INNER JOIN "DB"."dbo"."table-b" "table-b" 
		ON "table-a"."SAMPLE_ID"="table-b"."SAMPLE_ID"
 WHERE  ("table-a"."ENT_ON">={ts '2009-08-01 00:00:00'} AND "table-a"."ENT_ON"<{ts '2009-09-30 00:00:00'})
		 AND "table-a"."SAMPLE_ID"='140'

<MOVED BY MODERATOR TO THE CORRECT FORUM>

Moderator message - But cross post locked

Edited by: Alvaro Tejada Galindo on Oct 7, 2009 2:53 PM

Edited by: Rob Burbank on Oct 7, 2009 3:00 PM