Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Combining multiple records from one field Post Reply Post New Topic
Author Message
Andrewarbogast
Newbie
Newbie
Avatar

Joined: 21 Aug 2017
Location: United States
Online Status: Offline
Posts: 39
Quote Andrewarbogast Replybullet Topic: Combining multiple records from one field
    Posted: 09 Feb 2018 at 3:36am
Hello everyone,
I have a Credentials field and some staff will have multiple credentials in that field (LSW, LPC). I have it included in a cross tab. with staff name and credentials in rows and services in columns.

The problem is when a staff member has multiple credentials, it's causing their services to be duplicated.

Is there a formula to combine the multiple records from one field? I would think the result could look like (LSW,LPC) like I listed above.

Thanks,
Andrew
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 14 Feb 2018 at 4:01am
If you weren't using a cross tab, you could have a formula something like this:

StringVar creds;
if OnFirstRecord or {MyTable.staffid} = previous({MyTable.staffid}) then
creds := ""
else
creds := creds + ", ";
creds := creds + {MyTable.Credentials};
""

Put the formulain a details section to accumulate the credentials into a single string. To show the credentials, you would then have another formula like this:

StringVar creds;
creds

Put this in the group footer section of a staff member to show the credentials.

However, this won't work in a cross-tab. The only way I can think of to get this to work in a cross-tab would be to create a stored function in the database so that you pass the staff ID to the function and it will create the string using the same logic as the first formula above. Call this formula in a SQL Expression in your report so you can use it in the cross-tab.

-Dell
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.016 seconds.