Crystal Reports: Extract Sentences from Memo Fields in Crystal Reports

	Resources
	Best sellers: cView Report Analyzer cViewSERVER ReCrystallize

	Articles: Administration Advanced Basic Crystal eNL Database Financial Problems Solved Books: CR Books Database Books Developer Books
	Tools: Analyzers Bestsellers CR Schedulers CR UFLs CR Viewers DataBase Tools Graphics International Mail UFLs ReCrystallizePro
	Add'l: About us Contact Us cViewSUITE Ppt Support
	CrystalReports on Steroids

Crystal Reports: Extract Sentences from Memo Fields

One of the welcome improvements to formulas in Crystal Reports 9.0 and 10.0 was the ability to process memo fields. You can search and extract text from a memo field.

Use the INSTR function to find the first full stop in the memo field and then use the LEFT function to extract the first sentence in that field.

If INSTR({table.memo},”. ”)>0
then LEFT({table.memo}, INSTR({table.memo},”. ”))
else {table.memo}

This brings up a related question. How do you find a sentence?

Some people use two spaces between sentences, but the standard is one space between sentences. The two-space rule dates back to the era when hand-written communication was normal. It added a bit more clarity, which was needed because of the inconsistency of handwriting. In typewritten communication, the extra space is not needed.

The two-space practice is not used in books or magazines, at all. And because many people find it irritating, it is rapidly giving way to a single space between sentences in all forms of written communication. So, don't use two spaces in the search string to look for a sentence.

You can't use a period to define the end of a sentence, because someone may use a period for another purpose. Examples of this would be an abbreviation (e.g., St. Patrick, etc.). You need to exclude those from the search. Use a variable to work your way to the end of the sentence:

Local numbervar endpos := INSTR({table.memo} + “. ”,”. ”);
If mid({table.memo},endpos-2,3)=”St.” then
Endpos := instr(endpos+1,{table.memo},”. “); //remove St. Xxxx
If mid({table.memo},endpos-3,4)=”Etc.” then
Endpos := instr(endpos+1,{table.memo},”. “); //remove Etc.
//Include other frequently used abbreviations
Left({table.memo},endpos)

OK, so you probably aren't going to be searching for sentences very much. But you will have a need to find all sorts of other things. Can you devote time to sitting around developing specific formulas for them? Nope. You'd get fired. So what's the solution?

It's Report Analyzer.

In addition to letting you search and find just about any kind of object, Report Analyzer automatically detects and documents potential performance issues--including record selection formulas, grouping options, and database options. Now you can improve report reliability and maintenance, speed development, improve resource utilization, decrease infrastructure load, and manage projects better.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.

Uses for Report Analyzer

Report Analyzer empowers you to:

Simplify and optimize. Use the complexity matrix to remove unnecessary complexity. Remember, as complexity goes up, reliability and performance usually go down.

Identify, analyze, and record the attributes of design objects. Over 700 kinds of them! Report Analyzer will provide you with detailed information, so that you aren't operating in the dark. Know what you need to know about report databases, SQL codes, fields, formulas, and various design elements. How many times have you pulled an all-nighter trying to do the same thing manually? Get it right and do it fast, with Report Analyzer.
Perform cross-referencing of Objects, Fields, and Formulas. Let Report Analyzer's comprehensive cross-referencing show you where and how all objects, tables, fields, parameters, and formulas are used throughout your entire report. You can examine this information by using any of the cross- reference reports, or by using the XRef feature.
Perform detailed formula analysis. Wouldn't it be great if someone came up with a way for you to have formula flow diagrams that show how your formulas call or reference each other? Well, guess what? Report Analyzer provides that functionality! Report Analyzer's comprehensive formula analysis will document formula code, functions, fields/parameters referenced, and section formulas. Formula flow diagrams will show you exactly how formulas call or reference each other.
Generate user-friendly error analysis. Report Analyzer examines your report code for errors. So far, so good, right? What will blow you away is how you can see these errors. In the Formula window, they'll be underlined in red! Not only that, they'll have design suggestions.
Detect and correct. Detect potential design errors and common report setting mistakes, including unused objects, invalid database server settings, invalid printing settings, use of heavy weight objects such as OLE objects and Sub Reports, using special fields such as TotalPageCount (requiring extra passes over data) and many others.
Do a cleanup. Let Report Analyzer identify unused formulas, inappropriate use of formulas for grouping/sorting and record selection, and data type conversions in record selection. You'll get complete function cross referencing, call tree diagrams, and have the ability to produce professionally-formatted printouts of all code.
Find things. You can search any report object or settings across all reports including text, formulas, tables, fields, parameters, running totals, and subreports.
Export. Report Analyzer allows you to export report layouts and specifications to Word, Excel, HTML, XML, PDF and Ascii formats. You can export drawing files in the jpg or bmp format. You can export entire Worbenches or folders as HTML documentation to share over the network or intranet.

Click to enlarge

Click to enlarge

Report Analyzer also provides you with:

Suggestions to improve your report performance.
Powerful viewing and reporting of documentation.
An included library of standard documentation reports written in Crystal Report format
Documentation Explorer, which includes property information, diagrams (report layouts), formula code, suggestions, cross referencing and complexity for the currently nominated design object in the explorer.

Use Report Analyzer for development and maintenance.

Large report projects have numerous design elements and complex relationships between data and code. Consistent, automated documentation and analysis reduces development time and maintenance costs. Report Analyzer is designed to provide automated solutions. Do you really want any other kind of solution?

Development

Catalog design elements into the Report Analyzer database. This creates a repository of valuable information
See clearly how to re-use formulas, determine database layouts/structures, and maintain consistency across reports.
Measure just how complex a report is, using Report Analyzer's complexity metrics.

Automatically detect:

Potential performance issues in your report design settings, including record selection formulas, grouping options and database options.
Reports that do not take advantage of server side database processing
Record selection formulas that use data type conversion functions
Group fields that use formulas instead of SQL Expression fields.
Special fields which cause an additional pass over the report data source.

Maintenance

Reduce maintenance learning curves with the report blue prints, formula diagrams, and specification reports
Perform instant impact assessment for database changes and formula changes across all documented reports.
Apply the complexity metrics. These assist with test case planning and maintenance estimates. Reports with large numbers of formulas with lots of dependencies and conditions are typically more costly to maintain than simpler reports.
Discover unused Formulas, Fields, and Variables.
Identify group settings that cause blank pages and footers.
Locate which reports use specific functions, parameters, and design settings
View dependencies between report objects such as formulas; SQL expression fields, parameters and database fields.
Select design objects and view/print settings, formula code (including conditional formatting formulas), dependencies with other design objects, and call stacks (for formulas only).
Use interactive diagrams (trees) of formula hierarchies, object dependencies and report design elements.

System Documentation Report Analyzer does most of the work for creating professional system documentation for your report projects.

You can generate summary and detailed documentation.
Easy to use documentation explorer to browse and compare report design settings.
Print report design diagrams.
Print report data dictionaries.
Extensive formula printouts including call trees, object cross references, formatted listing, and conditional formulas.
All documentation can be exported in a variety of formats including PDF, HTML and Word.