Combining multiple records from one field
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22544
Printed Date: 28 Apr 2024 at 7:38pm
Topic: Combining multiple records from one field
Posted By: Andrewarbogast
Subject: Combining multiple records from one field
Date 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
|
Replies:
Posted By: hilfy
Date 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
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
|