How to prevent Multiple asset in cr

[vpk123]

i am having 3 tables. like

hpd_helpdesk
shr_association
ast_asset

i created report using the below query, my problem is if asset_id is having mutiple assets then in the report cases are repeating.


select
h.case_id_ ,h.status,h.priority ,h.TICKET_TYPE ,a.ASSET_ID_
From hpd_helpdesk h ,shr_association sc ,ast_asset a
where h.case_id_ = sc.id_1 and sc.id_2 = a.ENTRY_ID and h.case_id_ = '&Ticket'

For example if ticket no 120 having Multiple assets like

case_id =10000, asset_id = {abc,xyz}

then report is coming like

Case Id Status Priority Ticket Type Asset id
======================================================
10000 1 2 3 abc
10000 1 2 3 xyz

my problem is i want to display only one record.

Can anybody help in this how i can create report for the above.

 

[dgillz]

So which of the 2 records do you want to display?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[Turkbear]

Hi,
Insert a Group for Case ID and place the other fields in the details.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Group by the Case ID< then create a formula to concatenate the Asset IDs, as in:

Group header formula:
whileprintingrecords;
stringvar MyAssets:=""

Details formula:
whileprintingrecords;
stringvar MyAssets:=MyAssets+{table.asset}+",";

Group footer formula:
whileprintingrecords;
stringvar MyAssets;
if len(Assets > 0 then
left(Assets,len(Assets)-1)
else
""

Suppress the group header and details sections, and place your fields in the group footer, except the asset field, use the group footer formula to display them.

-k