Hi,

We are using an ERP system (Epicor Vantage) and Crystal Reports 11.  A few of our reports, that include parts and part descriptions in them, "bomb" if the part description is too long (over so many characters).  Most of the time, we can find the part/s with the long description/s and shorten the description/s to fix the issue.  However, this time, we can’t.

I am looking for a simple Crystal report with a formula/function to include part.partnum and some type of function for part.partdescription to count or show which decriptions are the longest (just the Part table would be needed).   I could then sort decending by the count number and find the culprit part descriptions.

I tried LEFT and LEN/LENGTH and the report still bombs probably because it still had to pull the long  description in first in order to trim or count it. 

SOLUTION:

In some cases the problem is that the data you are trying to retireve is just simply too large.  You can try the SUBSTR function in your SQL statement.  Below is an example of what that might look like, Part Description is used for this example.

SELECT SUBSTR(PartDescription, 1, 100) shortPartDesc FROM PUB.Part

This will work if you're using a pass-thru query and if you want the whole description, you can pull it down in pieces:

SELECT SUBSTR(PartDescription, 1, 100) PartDesc1, SUBSTR(PartDescription, 101, 100) PartDesc2 FROM PUB.Part

I don’t think that SUBSTR is a function in Crystal (at least I could not get it to work)…  This "concept" seems like it would work however. 

Anyone have any ideas?

Thanks so much!