Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Tips and Tricks
Message Icon Topic: How to display values on one row. Post Reply Post New Topic
Author Message
CrystalNewbie99
Newbie
Newbie


Joined: 22 Mar 2010
Location: Canada
Online Status: Offline
Posts: 3
Quote CrystalNewbie99 Replybullet Topic: How to display values on one row.
    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...
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet 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
IP IP Logged
CrystalNewbie99
Newbie
Newbie


Joined: 22 Mar 2010
Location: Canada
Online Status: Offline
Posts: 3
Quote CrystalNewbie99 Replybullet 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. 
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet 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.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet 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}.
IP IP Logged
CrystalNewbie99
Newbie
Newbie


Joined: 22 Mar 2010
Location: Canada
Online Status: Offline
Posts: 3
Quote CrystalNewbie99 Replybullet 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};
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 22 Mar 2010 at 11:55am
The example data did not have duplicate defect_codes, so I was hoping that was the case.
IP IP Logged
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.