General discussion

Locked

Crystal Reports 11

By afisher ·
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.

This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by SpiderDad In reply to Crystal Reports 11

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?})

Back to Web Development Forum
1 total post (Page 1 of 1)  

Related Discussions

Related Forums