'Concatenate mulitple rows' formula
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
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=11608
Printed Date: 27 Apr 2024 at 11:32pm
Topic: 'Concatenate mulitple rows' formula
Posted By: SunnyA84
Subject: 'Concatenate mulitple rows' formula
Date 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, ------------- Sunny |
Replies:
Posted By: FrnhtGLI
Date 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. ------------- |< /\ '][' ( ) |
Posted By: SunnyA84
Date Posted: 26 Nov 2010 at 9:42am
|
Thank you so much for ur help ..it worked for me. :) ------------- Sunny |
Posted By: tanu
Date 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. |
Posted By: KevV
Date 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 |
Posted By: tanu
Date 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.. |
Posted By: KevV
Date 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
|
Posted By: tanu
Date 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 
|
Posted By: cmhrider
Date Posted: 10 Jul 2014 at 9:47am
|
Originally posted by FrnhtGLI
I got this to work but the first record doesn't show. Instead of:
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. 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? |
Posted By: DBlank
Date Posted: 10 Jul 2014 at 10:18am
| do you have any nulls in your data set, particularly the missing grouping? |
Posted By: Titima
Date Posted: 01 Apr 2016 at 12:19am
|
Hello ,
I am creating a report and I need your help i m using those Formula But I still also have a probleme Data example : Facture client TTT REG BNQ SERIE 1501732 X 28000 28000 bnq1 17777 1501733 X 30000 20000 bnq1 14777 1501733 X 30000 10000 bnq1 14778 Plz Can u help me plzzzz ------------- titimaa |