Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How do I get around using memo fields in Crystal Reports?
Data Connectivity
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:
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.
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:
Code:
left(TABLE.FIELD, 255)
convert(TABLE.FIELD, varChar(255))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.