Concatenate Multiple Rows in Crystal Reports 2008

RaoVP
RaoVP used Ask the Experts™
on
Hi Gurus,

I am using Crystal Reports 2008, Oracle 10g
I have 2 columns in my report

CONTACT_REPORT_ID   ====    REPORT_NM
------------------------------------------
1                           ABC
1                           XYZ
2                           TOM
2                           BOB
2                           SAM
3                           ANDY
3                           MIKE

But I need to display in the below format

CONTACT_REPORT_ID   ====    REPORT_NM
----------------------------------------
1                           ABC,XYZ
2                           TOM,BOB,SAM
3                           ANDY,MIKE


Please let me know the logic to implement in the above mentioned manner.

Thanks in Advance

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
The easiest way in 10g is use an XMLAGG trick.

Check out:
http://www.experts-exchange.com/Database/Oracle/Q_24914739.html?#a25864822
If you want to do this within CR, you could group on the first column (CONTACT_REPORT_ID ?) and save the values in the second column (REPORT_NM ?) in a string, then output the string in the group footer.  You'd presumably suppress the detail section.

 Create a group on CONTACT_REPORT_ID.

 Create a formula like the following (call it whatever you want) and put it in the report header, and in the group header.  It initially creates the variable in the report header, and resets the contents for each new group in the group header.

Global StringVar names;
names := ""


 Create a formula like the following and put it in the detail section:

Global StringVar names;
if names = "" then
  names := {REPORT_NM field}
else
  names := names + "," + {REPORT_NM field};
""

 Obviously, replace {REPORT_NM field} with your field name.
 The "" at the end is so that the formula doesn't actually produce any output on the report (although that doesn't really matter if the detail section is suppressed).

 Then create a formula like the following and put it in the group footer:

Global StringVar names;
names


 James

Author

Commented:
Hi James,

Thanks for now, Please let me know some more details. I ill try tomorrow and let u know.

But along with these fields I also have 5 different columns, so is it applicable to place all these fields along with the above formula in the group header.

Date, Type, Purpose, Outcome, Author, Entered Date, Last Change Date, User Name, staff credits

FOR EACH AUTHOR THERE ARE MORE THAN ONE STAFF CREDITS 1,2,3....
with reference to above example
CONTACT REPORT ID......> AUTHOR INFORMATION
REPORT_NM ......> STAFF  CREDITS INFORMATION

*** EVERY THING SHOULD APPEAR IN ONE SECTION ***
========================
Date       Type      Purpose            Outcome                  Unit
------------------------------------------------------------------------
5/1/2010       Meeting       Cultivation   Move to Proposal       A

Author: Name (On Behlaf Of if not blank and not the same as Unit)
Staff Credited 1, Staff Credited 2, Staff Credited 3...................................(.this is what needed )

Text
Entered by: User Name on Date
Last Changed by: User Name on Date
=======================
3/10/2010      Phone Call      Cultivation      Cultivation      A
Author:Name
Staff Credited 1, Staff Credited 2, Staff Credited 3

Text

Entered on: Date
Last Changed by: User Name on Date
====================================================


Thanks in Advance
PVRAO
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

You can't use the solution that I described in the group header.  The values will be added to the variable as the records are read, so you won't have the final value until the group footer.  But that's probably not a problem.  Anything else that you had in the group header will probably work just as well in the group footer, so you can probably just move everything from the header to the footer.

 If your other fields are the same for every record in the group, then you can just put those fields in the group footer.  If any of the other fields could have multiple values for the records in a group, then it's a question of how you want to handle those.

 James

Author

Commented:
Here only Staff Credit have multiple values for each author, and I need to display side by side in CSV format.
Since only staff credit have distinct values
Rest of the columns have same data for each group of author.

i,e I am getting 3 set of records for each author, with only distinct field "staff credit"
to avoid multiple set of records and show only one row for each author, I need to concatenate
 "staff credit"

Please let me know whether the above logic works here by place all fields in Group Footer?



Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I assume you already have the SQL to generate the staff list and you just need to add the CSV list part.

Did you look at the link I provided?

I can't help with the Crystal Report itself but can help with the SQL you need.  If you can't get the XMLAGG working please post table defs, sample data and expected results and I'll try to come up with the SQL.
I can't see any reason why you couldn't do this in the group footer.

 James

Author

Commented:
I used the Crystal Report Logic, but by the way I got to know some oracle concept of concatenation.

Thanks Again !!
You're welcome.  Glad I could help.

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial