Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Help needed to get desired result Post Reply Post New Topic
Author Message
BrianK
Newbie
Newbie
Avatar

Joined: 15 Apr 2008
Location: United States
Online Status: Offline
Posts: 3
Quote BrianK Replybullet Topic: Help needed to get desired result
     Posted: 15 Apr 2008 at 11:03am

Hi,

 

I am having a hard time writing a formula that will give me the desired results.  I am using Crystal Reports 9.

 

I have a text field in a SQL database that has data that looks like:  ,1,2,

 

This field refers to the id number in a user table:

     ID        Name

1                    Jane Doe

2                    John Smith

3                    Dave Jones

 

*my user table has over 6000 names in it

 

 

Instead of the field showing:

,1,2,

,3,

,1,3,

 

I need the field to show:

Jane Doe; John Smith

Dave Jones

Jane Doe; Dave Jones

 

And I am not sure how to do that in Crystal Reports.

 

Thanks for your help
Brian
IP IP Logged
Iago
Groupie
Groupie
Avatar

Joined: 01 Oct 2007
Location: United States
Online Status: Offline
Posts: 52
Quote Iago Replybullet Posted: 15 Apr 2008 at 11:51am
1nf
 
I would try and get the data fixed.  each value should be in its own field, and maybe table if the number of fields vary.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 15 Apr 2008 at 1:01pm
There is no way to do this in Crystal itself.  You would have to have a stored procedure in the database to do this, if your database allows stored procs.  What type of database are you using?
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
BrianK
Newbie
Newbie
Avatar

Joined: 15 Apr 2008
Location: United States
Online Status: Offline
Posts: 3
Quote BrianK Replybullet Posted: 15 Apr 2008 at 1:21pm

Iago, I can't change the data.  The data is created by an application, and changing the data would break the application.

Dell, I am using SQL Server 2003.

 
Brian
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 15 Apr 2008 at 3:04pm

Do the DBA's maintain stored procedures or are they written by a developer?

Basically, you need a function that takes the contents of the field as input, parses the string to get the individual numbers, and for each number does a lookup to the user table to get the user name so that it can be added to the string that's the return value.
 
You can then set up this function as a SQL Expression in your report to get the values that you need.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
BrianK
Newbie
Newbie
Avatar

Joined: 15 Apr 2008
Location: United States
Online Status: Offline
Posts: 3
Quote BrianK Replybullet Posted: 16 Apr 2008 at 11:52am
Thanks for your help.
 
I was able to create a SQL function on the database.  I then did what you said, and created a SQL Expression in CR that calls that function.
Brian
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.