Print Page | Close Window

How to display values on one row.

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=9451
Printed Date: 03 May 2025 at 4:21am


Topic: How to display values on one row.
Posted By: CrystalNewbie99
Subject: How to display values on one row.
Date Posted: 22 Mar 2010 at 5:39am
Hi i am new to Crystal Reports and I am not sure how to do the following:
 
I have duplicate records for all fields but defect_codes.  I want to only display one record for each duplicate row but append all defect_code so that they only print on one line.
 
Piece_id       defect_code
1                       a1
1                       a2
1                       a3
2                       b1
2                       b2
3                       c1
3                       c2
 
I want it to look like this:
 
Piece_id         defect_codes
1                     a1, a2, a3
2                     b1, b2
3                     c1, c2
 
Hopefully someone can show me how to do this. 
 
Thanks...


Replies:
Posted By: kevlray
Date Posted: 22 Mar 2010 at 6:19am
I can think of a way to do this.  Not sure if is the best.  Group the report by Piece_Id.  Create three formulas.  The first one would define a shared StringVar and set it to "" (example: shared stringvar defect_cd := "";).  The second one (of course would have the shared stringvar, the same as the first formula) which would be in the detail section, would have a line something like this: defect_cd := defect_cd & ", " & {defect_code}.
The third formula would be in the group footer and it just would have the same shared variable defined and the same shared variable name (example:
shared stringvar defect_cd;
defect_cd;
 
I hope this helps

Posted By: CrystalNewbie99
Date Posted: 22 Mar 2010 at 7:21am
Hi thanks for the reply:  i want the concatenated defect_cd string to print in the details section of the report not the footer???  Like I said I am very new to Crystal so even creating fomulas is new. 

Posted By: kevlray
Date Posted: 22 Mar 2010 at 9:53am

You would not be able to print what you want in the details section, thus that is why I suggested putting it in the group footer (suppress group header and details).  To anyone viewing the report, they should not be able to tell it is not in the details section.

As far as I know, formulas would be the only way to go with this.

Posted By: kevlray
Date Posted: 22 Mar 2010 at 10:06am
I was just testing my formulas and found an issue.  If forgot to test for a empty string before concatinating the ", ".  Should read like this.
if defect_cd = "" then defect_cd = {defect_code) else
defect_cd := defect_cd & ", " & {defect_code}.

Posted By: CrystalNewbie99
Date Posted: 22 Mar 2010 at 11:17am
Ok thanks I actually got it working but I also had to just show the one defect code if the piece_ids did not match.  If not the code just appended all of the defect_codes together...
 
 shared stringvar defect_cd;
 
 if {r_reportname.PIECE_ID} <> previous({r_reportname.PIECE_ID}) then
    defect_cd := {r_reportname.DEF_CD_1}
 else
    Defect_Cd := Defect_Cd  & " " & {r_reportname.DEF_CD_1};

Posted By: kevlray
Date Posted: 22 Mar 2010 at 11:55am
The example data did not have duplicate defect_codes, so I was hoping that was the case.


Print Page | Close Window