INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Data Connectivity

How do I get around using memo fields in Crystal Reports? by CestusGW
Posted: 19 Jul 02

Any text field with a maximum length greater than 255 characters gets treated as a memo field in Crystal Reports. While using CR, a memo field may not be used in:
Grouping Formulas
Selection Formulas
Formula Fields
Formatting Formulas
or any other formulas. However, frequently data that you need to manipulate is contained in these fields. Inevitably, the question is "How can I get at my memo fields?"
Solution #1:
Use a SQL Expression field to truncate the memo field. Depending on your database, one of the following may work:

left(TABLE.FIELD, 255)
convert(TABLE.FIELD, varChar(255))

This solution is ideal because it adds no extra layers to your report and processes the change on the database server or file. This is ideal for the web environment as excess processing can cause timeouts or other errors.
Solution #2:
This solution is less than ideal for the developer who is using Crystal Enterprise to build web based reports. Frequently the report programmer will not be the same one who has access to or manages the databases. Therefore, he or she cannot build a new view or virtual field in the database which will do the truncations needed. To solve this, try building a database file built on the original tables. An example of this would be importing or linking the tables to an MS Access database which is placed on an available server. A query can then be made which truncates the fields in the file, without programming from the Crystal Report. However: in the ideal web reporting situation, selection statements are passed down to the database server level through SQL. Using a linked Access table will only pass the SQL down to MS Access. This means an increase in server time and network traffic. Importing tables means that web services or batch files must be created to periodically update the data in the Access database: this means that the data will no longer be dynamic.
Solution #3:
Have the database programmers at your company (or wherever) create a new virtual field which will truncate the field you need down to a manageable size, or split the memo field into several smaller fields. Unfortunately, people can be the biggest problem in any business, and one of the other solutions may be easier than getting the database customized just for you :)

As a final note, the Left() and other functions built into Crystal Reports won't work to truncate the memo fields. Crystal Reports will throw an error if you attempt to place the memo fields into a formula field.

Back to Business Objects:Crystal Enterprise FAQ Index
Back to Business Objects:Crystal Enterprise Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close