[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Concatenating Rows from one field into one column

Posted on 2005-05-02
7
Medium Priority
?
463 Views
Last Modified: 2008-02-26
I have a report in which all the fields are being duplicated except for one.  Instead of suppressing duplicates and showing up to 10 records because of this one field, I would like concatenate that field so that for each row, all the different values for that field would go into one row.

Simple example:
Data brings back the following of employees with their children:
Employee      Child
Sue      Jane
Sue      Dick
Sue      Les
Matthew      Tyler
Matthew      Jordan

I want to see the following (using only Crystal):
Sue      Jane, Dick, Les
Matthew      Tyler, Jordan
0
Comment
Question by:MatthewSky
  • 4
  • 2
6 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 13910786
I assume you have separate records for each pairing.

Try this
Add a group on Employee
In the group header add a formula
Name - InitVars
Formula
global stringvar strChildren := "";

In the detail section
Name ConcatChildren
Formula
global stringvar strChildren;
strChildren := strChildren & ", " & {Table.ChildField};

In the Group footer
Name DispChildren
Formula
global stringvar strChildren;
strChildren

The detail section will be blank
The group footer will have your detail line.

mlmcc
0
 

Author Comment

by:MatthewSky
ID: 13911336
We are getting an error in the fact that the string length grows to be bigger than 254.  We then changed it to strChildren := left(strChildren & ", " & {Table.ChildField}, 154) just to see what would happen and the data was showing up in the detail section and the group footer was blank.  The details section was never resetting when it was a new group.

Based on that I created 3 new formula fields

@LastRecord:
OnLastRecord

@Final (this signifies the final record in each group):
if {@LastRecord} = True  then
"x"
else if next({ITA_PROJ_RES_TEAM_SP.Project #}) <> {ITA_PROJ_RES_TEAM_SP.Project #}  then
"x"
else " "

@ConcatChildren (modified from yours above):
global stringvar strChildren;

if {@RecNo} = 1 then
strChildren := {ITA_PROJ_RES_TEAM_SP.TEAMNAME}
else if previous({ITA_PROJ_RES_TEAM_SP.Project #}) = {ITA_PROJ_RES_TEAM_SP.Project #} then
strChildren := strChildren & ", " & {ITA_PROJ_RES_TEAM_SP.TEAMNAME}
else
strChildren := {ITA_PROJ_RES_TEAM_SP.TEAMNAME};

We are still going to have a problem with the 254 if we have too much data.  Any ideas?  We are using Crystal 8.5.
-Matthew
0
 

Author Comment

by:MatthewSky
ID: 13911349
I forgot the punchline:

For the details section, suppress if @Final <> "x"
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 101

Accepted Solution

by:
mlmcc earned 1500 total points
ID: 13912483
Two ideas

1.  Upgrade to CR9 or later.  It can handle strings greater than 254 characters.

2.  Change the format of the report to something like

Parent - Sue
Children
     Jane      Dick    Les

or

Parent - Sue
Children
     Jane      
     Dick    
     Les

To do that the parent name goes in the group header and the children's names go in the details.  The first example uses multiple columns across then down.

>> The details section was never resetting when it was a new group.
Put this formula in the report header
Name - InitVars
Formula
global stringvar strChildren := "";


In the group header use
Name - ResetVars
Formula
global stringvar strChildren;
strChildren := "";

mlmcc



0
 
LVL 101

Expert Comment

by:mlmcc
ID: 14103156
My comment {13912483} may have helped

mlmcc
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 14117837
Glad i could help

mlmcc
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Screencast - Getting to Know the Pipeline
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question