Print Page | Close Window

table joins

Printed From: Crystal Reports Book
Category: Crystal Reports .NET 2003
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=16176
Printed Date: 16 May 2024 at 10:33pm


Topic: table joins
Posted By: Yarra
Subject: table joins
Date Posted: 13 Apr 2012 at 1:52pm
How can I achieve the desired output in crystal.

Table 1
ID      Name
1     John
2     Mary

Table 2
ID      Phone
1     44444
1     55555
2     88888
2       99999


Current Output (when joined table 1 with table 2 on ID)
ID   Name    phonenumber
1    John       44444
1    John       55555
2    Mary       88888
2    Mary       99999


Desired Output
ID   Name Phonenumber
1    John    44444, 55555
2    Mary    88888, 99999


Replies:
Posted By: hilfy
Date Posted: 15 Apr 2012 at 2:44pm

Try this:

1.  Join from table 1 to table 2 on ID.
2.  Group on table1.ID
3.  Create a formula to concatenate the phone numbers.  It will look something like this: 
StringVar Phones;
if OnFirstRecord or {table1.ID} <> previous({table1.ID}) then Phones := '';
If Phones = '' then
  Phones := {table2.phonenumber}
else
  Phones := ', ' + {table2.phonenumber};
Phones
4.  Place your fields in the Group Footer for the ID group, using this formula for the phone number column.
 
-Dell
 


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Print Page | Close Window