Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: How to concantenate a field Post Reply Post New Topic
Author Message
CRW2
Newbie
Newbie


Joined: 14 Aug 2009
Location: United States
Online Status: Offline
Posts: 6
Quote CRW2 Replybullet Topic: How to concantenate a field
     Posted: 14 Aug 2009 at 10:40am
Hi,
 
Can anybody assist on how I can change a field posted to a detail to print
horizontally.
 
Out box right now it prints the serial number as ;
 
Serial no: 1111
Serial no: 2222
Serial no: 3333
 
And we need to print it as
 
Serial no: 1111,2222,3333
 
So we can save on paper when invoicing, I tried to do a variable an putting a formula on the header, detail and footer and it did succesfull go horizontal but its printing on the second page if value is in the footer.
 
Thank you in advance
 
CRW2
 


Edited by CRW2 - 14 Aug 2009 at 11:07am
Thank you

CRW2
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 14 Aug 2009 at 12:33pm
Unfortunately, for this type of thing, the only way it will work when you concatenate inside of Crystal is if it's in the footer.  Depending on your database, you could create a stored function that would return the concatenated value.   You would pass in some information from your data that would identify which invoices you want (key to the person/company being invoiced and possibly whatever other info you need to get the right data.)  Then, call the function in a SQL Expression using the fields from the report as your parameters.
 
For example, we have a stored function in our Oracle database that translates and employee id into a name.  So my SQL Expression for User Name looks like this:
 
zcutils.GetUserLNameFirst("MY_TABLE"."EMPLOYEE_KEY")
 
I then use the SQL Expression object on my report just like any other field.
 
-Dell
IP IP Logged
CRW2
Newbie
Newbie


Joined: 14 Aug 2009
Location: United States
Online Status: Offline
Posts: 6
Quote CRW2 Replybullet Posted: 14 Aug 2009 at 1:13pm
Hi Dell,
 
Thank you for the response, we are using a database called mas90 by Sage. I tried to even create a sub report and insert into the detail but would still show up as 3 reports becasue of the 3 serial numbers.
 
THis is what I did
 
in the header i did a
shared stringvar sn:='';
in the detail b
shared stringvar sn;
sn := sn  + {SO_InvoiceWrk.LotSerialNo}+ ","; --   Suppress
in the footer
shared stringvar sn;
sn;
 
but if I transfer:
 shared stringvar sn;
sn;
to the detail it would show
 
aaa
aaa,bbb,
aaa,bbb,ccc
 
If only I can suppress the first two lines that work okay.
 
Your thoughts
 
Again thanks
 
R
 
 
Thank you

CRW2
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 14 Aug 2009 at 1:42pm

Go back to your subreport idea - Does this really need to be in the detail or can it be in the header?  Link it based on the group info instead of the details, put the formula that concatenates the data in the subreport report footer and suppress all of the other sections. 

If the data must be in the detail line, leave the subreport in the group header and do the following:
 
1.  Make the subreport tiny or make the text in the subreport white so that the data doesn't actually appear in the group header.
 
2.  In the main report, create formula with a shared variable that will contain the concatenated data.
 
3.  In the subreport, set the formula that concatenates the data to use the shared variable from the main report.
 
4.  Put this new formula in the detail section of your main report.
 
-Dell
IP IP Logged
CRW2
Newbie
Newbie


Joined: 14 Aug 2009
Location: United States
Online Status: Offline
Posts: 6
Quote CRW2 Replybullet Posted: 14 Aug 2009 at 2:14pm
HI Dell,
 
Do you mind giving me a formula that link to the concatenated data?
 
Thank you in Advance
 
 
Thank you

CRW2
IP IP Logged
CRW2
Newbie
Newbie


Joined: 14 Aug 2009
Location: United States
Online Status: Offline
Posts: 6
Quote CRW2 Replybullet Posted: 14 Aug 2009 at 3:04pm
Hey Dell,
 
Got it to work! I just had to put the subreport in detail a without doing the link.
 
Thanks a lot for our assitance
 
RSmile
Thank you

CRW2
IP IP Logged
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