Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Trimming Data in Crystal

Posted on 2002-05-29
13
Medium Priority
?
373 Views
Last Modified: 2008-03-06
I wish to retrieve data from a particular database field via Crystal, only I want to trim the data that comes in, and report against this data.

Ex. I am pulling in data from a field called REFERENCE, and it gives me a value of 30101301. I only want to display and work with the first three numbers here: '301'. I also want to do a lookup using this number, to another table that provides a correlating description for what 301 is.

Anyone know the SQL Statement or steps in Crystal to do this?

Thanks.
0
Comment
Question by:Mantissa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 7042288
DO you want to use CR for the SQL statement or can you build a query in the database?

If you can build in the database, what database are you using?

mlmcc
0
 

Author Comment

by:Mantissa
ID: 7042292
Hi,

I must use Crystal Reports to get this.
I can't build a query or procedure in the database.
I am working with an Oracle 8.0.5 instance.

Thanks!
0
 
LVL 6

Expert Comment

by:dbirdman
ID: 7042324
One way is to use a Crystal Formula Field and the Left() Function:

Left(REFERENCE, 3)
0
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 

Author Comment

by:Mantissa
ID: 7042694
dbirdman:

That sounds like what I need....but unfortunately I am not really a 'power user' of Crystal, and need a bit of help integrating that formula.

I tried making a formula field and using what you specified above, but the editor kept giving me errors (saying a ')' is expected here).

Could you possibly give me the steps to apply this properly? Also, do I need to speficy the table name in there somewhere? ie. Left(TABLENAME.REFERENCE, 3)

Thanks for the help, looks like it is almost there!
0
 

Author Comment

by:Mantissa
ID: 7042736
Aha....got it.

I changed the formula to this:
Left({TABLENAME.REFERENCE}, 3)

The additional brackets did the trick! I just need to see if I can pull the description in from another table....
0
 

Author Comment

by:Mantissa
ID: 7042840
Ok, this one is giving me a headache now...

I named the above formula @trim, and now I want to select a description field in a different table where the description's associated VALUE field = @trim

The sloppy SQL statement I am trying to execute is as follows:
select TABLENAME2.DESCRIPTION from TABLENAME2 where TABLENAME2.VALUE = {@trim}

I hope that isn't too confusing? Basically there are a set of values in TABLENAME2 that have associated DESCRIPTIONS, that I want to correspond to my @trim formula.

Thoughts? Thanks for the help so far!
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 7044408
Try something more like this

SELECT table2.Description
FROM
table1 LEFT JOIN table2 ON
left(Refernce,3) = table2.value

You have to get table1 involved to get the original value to trim.

good luck
mlmcc
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 300 total points
ID: 7045053
0
 

Author Comment

by:Mantissa
ID: 7047675
I have been unable to get this to work in Crystal despite a lot of work. I will spend some more time on it Monday and let everyone know how it goes.

Thanks!
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 7062547
Need any more help?

mlmcc
0
 

Author Comment

by:Mantissa
ID: 7063621
Hi mlmcc....sorry I've been busy! :S

I have someone else working on getting this to work, but they are basing the report on information gathered at the link you provided above...as well as your proposed join statement. Thus, I will award the answer to you, and adjust the grade to a "B" to compensate.

Thanks to you and dbirdman for the great tips...I'll come back soon with more Crystal Questions!
0
 

Author Comment

by:Mantissa
ID: 7063624
Put me on the right path...thanks!
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 7065246
Glad to help

mlmcc
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question