Web Development

Ask something
Discussion Locked

Crystal Reports 11

0 helpful
1 reply

by afisher

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a property and a photos table. The photos table contains multiple photo records for each property record, each of which have a ranking of 1 - 5. My report doesn't display any fields from the photos table - only property fields - nor does it use the photos table to define criteria. I bring in the pictures from the photos table with a formula in an OLE object based on matching property IDs. The tables are set with a left outer join, property table left. This causes the property records that have multiple photos to show up once for each photo. If I modify the selection criteria to choose records where photo.imagerank = 1, the property records without photos don't show up. I assume it's becuase I've defined a selection criterial based on the photos table, causing any properties without a photo record to be tossed. I need to show all properties meeting my property table criteria while cutting out record duplication caused by there being multiple photos for each property, while displaying the photo from the photos table which is ranked as 1.

1 reply

by spiderdad

Verify that your SQL works outside of Crystal (maybe you already tried this). This might help find the problem.
To cut the duplication, suppress the detail print but save image=1 in the detail section. Then group & report by property at the group footer level only w/ the saved image=1. Below is the technique I used where I saved dates at detail and reported at group footer :
Collect data in detail for footer

?@init? ------------------ formula field in report header section-----------
global stringvar s;
global stringvar p;

?@display5? ------------------ formula field in detail section----------
WhilePrintingRecords;
global stringvar p;

?@display6? ------------------ formula field in detail section-----------
WhilePrintingRecords;
global stringvar s;

?@read5-6data? ------------------ formula field in detail section--------------
whileprintingrecords;
global stringVar s;
global stringvar p;

if {GET_CQ0701_PKG_P_FETCH_CONTRACT_AMEND_ZERO.DATE_TYPE_CODE} = "6" then
s:=s+" "+totext({GET_CQ0701_PKG_P_FETCH_CONTRACT_AMEND_ZERO.TRACKING_DATE})
else
if {GET_CQ0701_PKG_P_FETCH_CONTRACT_AMEND_ZERO.DATE_TYPE_CODE} = "5" then
p:=p+" "+totext({GET_CQ0701_PKG_P_FETCH_CONTRACT_AMEND_ZERO.TRACKING_DATE});

@CNTY BOARD DATE -------------------- Formula Field in Rpt Footer
if Length ({@?@display6?}) > 1
then
Date({@?@display6?})

This discussion is closed to new replies.