Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: 'Concatenate mulitple rows' formula Post Reply Post New Topic
Page  of 2 Next >>
Author Message
SunnyA84
Newbie
Newbie
Avatar

Joined: 05 Nov 2010
Online Status: Offline
Posts: 5
Quote SunnyA84 Replybullet Topic: 'Concatenate mulitple rows' formula
    Posted: 05 Nov 2010 at 11:15am
Dear experts,
 
I am creating a report and I need your help writing a crystal formula to concatenate rows into one field.
 
Data example :
 
Emp ID              Remarks (its a free text field in the database)
11                     Good
11                     Excellent
11                    
11                     Very Nice
99                     Nice
99                     wow
22                     Excellent
22                    
22                     Good
 
 
However  I would like to see it as :
 
Emp ID       Remarks
11              Good,Excellent,Very Nice
99              Nice,Wow
22              Excellent,Good
 
 
 
Can someonel please help me with a formula.
 
Thank you!
 
Regards,


Edited by SunnyA84 - 05 Nov 2010 at 11:43am
Sunny
IP IP Logged
FrnhtGLI
Senior Member
Senior Member
Avatar

Joined: 22 May 2009
Online Status: Offline
Posts: 347
Quote FrnhtGLI Replybullet Posted: 08 Nov 2010 at 2:20am
Try using a string variable formula. Something like:
 
whileprintingrecords;
 
global stringvar sRemarks:=sRemarks & (if next({table.EmpID})={table.EmpID}
or previous({table.EmpID})={table.EmpID}
     then {table.Remarks} & ','
          else '';
 
sRemarks;
 
You will have to make two more formulas: One that will restart it on the Emp ID group and one that will display it on the detail line.
 
The restart formula will just be:
Whileprintingrecords;
global stringvar sRemarks:=''
 
and the display one will be:
whileprintingrecords;
global stringvar sRemarks;
 
Place the restart formula in the Emp ID Group Header and the display one on the detail line. Also place the calculate formula in the detail line, but suppress it.
Then suppress the detail lines when {table.EmpID}=next({table.EmpID}).
You'll have to throw something in there for when it reaches the last record also so maybe:
 
(not onlastrecord
and
{table.EmpID}=next({table.EmpID}))
 
 
Not sure if the logic is completely sounds, but it may give you a good starting point.
|< /\ '][' ( )
IP IP Logged
SunnyA84
Newbie
Newbie
Avatar

Joined: 05 Nov 2010
Online Status: Offline
Posts: 5
Quote SunnyA84 Replybullet Posted: 26 Nov 2010 at 9:42am
Thank you so much for ur help ..it worked for me. :)
Sunny
IP IP Logged
tanu
Newbie
Newbie
Avatar

Joined: 19 Jun 2011
Online Status: Offline
Posts: 10
Quote tanu Replybullet Posted: 19 Jun 2011 at 3:19am
Hey sorry for posting so late, but i have exactly same problem.
I am completely new to crystal reports, hence i am not able to understand where to put what.

I am using crystal reports through visual studio 2005. So where do i have to put that stringvar if formula? and what about other two formulas, where do i have to place them??

It would be great if you help me solve this problem. I really need help.

Thankyou.
IP IP Logged
KevV
Senior Member
Senior Member


Joined: 19 May 2011
Online Status: Offline
Posts: 106
Quote KevV Replybullet Posted: 20 Jun 2011 at 7:22am
Using FrnhtGLI's formulas put Formula 2  in the EmpID Group Header. Then place Formula 1 in the deatail section of the report somwhere and suppress it. Then place Formula 3 in the detail section of the report where you want it displayed. Then go into the "Section Expert" and go to the detail section and click on the Suppress (No Drill Down) and enter Formula 4
 
Formula 1
whileprintingrecords;
 
global stringvar sRemarks:=sRemarks & if next({table.EmpID})={table.EmpID}
or previous({table.EmpID})={table.EmpID}
     then {table.Remarks} & ','
          else '';
 
sRemarks;
 
Formula 2 
Whileprintingrecords;
global stringvar sRemarks:=''
 
Formula 3 
whileprintingrecords;
global stringvar sRemarks;
 
 
Formula 4
(not onlastrecord
and
{table.EmpID}=next({table.EmpID}))
 
KevV


Edited by KevV - 20 Jun 2011 at 7:24am
IP IP Logged
tanu
Newbie
Newbie
Avatar

Joined: 19 Jun 2011
Online Status: Offline
Posts: 10
Quote tanu Replybullet Posted: 22 Jun 2011 at 5:45am
Hey thanks for replying..
do i have to put formula 2 in group selection formula of empid??
I am placing it there but it shows an error that a boolean has to be used for storing result of selection..
IP IP Logged
KevV
Senior Member
Senior Member


Joined: 19 May 2011
Online Status: Offline
Posts: 106
Quote KevV Replybullet Posted: 22 Jun 2011 at 7:29am
Create a new formula called "restart" or whatever and place it in the empid group header somewhere not in group selection. If you look in the design view one of them should show empid if you are grouping them by it.
 
KevV
IP IP Logged
tanu
Newbie
Newbie
Avatar

Joined: 19 Jun 2011
Online Status: Offline
Posts: 10
Quote tanu Replybullet Posted: 22 Jun 2011 at 8:02am
i dont know..its not working now.
Actually whenever i place unbound string fields to add formula to it, all my values to other columns disappear.

Right now I am getting a blank sheet other than field headings
IP IP Logged
cmhrider
Newbie
Newbie
Avatar

Joined: 08 Jul 2014
Location: United States
Online Status: Offline
Posts: 1
Quote cmhrider Replybullet Posted: 10 Jul 2014 at 9:47am
Originally posted by FrnhtGLI

Try using a string variable formula. Something like:
 
whileprintingrecords;
 
global stringvar sRemarks:=sRemarks & (if next({table.EmpID})={table.EmpID}
or previous({table.EmpID})={table.EmpID}
     then {table.Remarks} & ','
          else '';
 
sRemarks;
 
You will have to make two more formulas: One that will restart it on the Emp ID group and one that will display it on the detail line.
 
The restart formula will just be:
Whileprintingrecords;
global stringvar sRemarks:=''
 
and the display one will be:
whileprintingrecords;
global stringvar sRemarks;
 
Place the restart formula in the Emp ID Group Header and the display one on the detail line. Also place the calculate formula in the detail line, but suppress it.
Then suppress the detail lines when {table.EmpID}=next({table.EmpID}).
You'll have to throw something in there for when it reaches the last record also so maybe:
 
(not onlastrecord
and
{table.EmpID}=next({table.EmpID}))
 
 
Not sure if the logic is completely sounds, but it may give you a good starting point.
I got this to work but the first record doesn't show.  Instead of:
 
Emp ID       Remarks
11              Good,Excellent,Very Nice
99              Nice,Wow
22              Excellent,Good
 
I get:
 
Emp ID       Remarks
11              
99              Nice,Wow
22              Excellent,Good
 
Any ideas on how to get the first record to display?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 Jul 2014 at 10:18am
do you have any nulls in your data set, particularly the missing grouping?
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.