A utility to document your Crystal reports

Use this FoxPro program to list all the tables and fields in your reports

Note to non-FoxPro users: A free-standing version of the program described in this article is also available. Please see Find the tables and fields used in your Crystal reports.

A client recently asked us if we could "document" his Crystal reports. On closer investigation, we found that what the client really wanted was to obtain a listing of all the database tables and fields used in all his reports. Database designers find this sort of listing extremely valuable, as it helps them judge the impact of any changes that they might be considering to their table structures. We decided that if we could find a simple way of achieving this goal, it would benefit other CR users as well.

Our first reaction was to look at some of the third-party utilities that are available for documenting Crystal reports. For example, Report Analyzer for Crystal Reports (available from Crystalkeen, www.crystalkeen.com) is an extremely powerful report analysis program that can produce all manner of documentation. Among many other types of output, it can churn out problem reports, data dictionaries, report design diagrams and a great deal more. Producing a simple list of database fields would be plain sailing for this sophisticated utility.

However, since this particular client's requirements were relatively simple, we concluded that a utility such as Report Analyzer for Crystal Reports would be overkill in this case. We decided instead to write a quick FoxPro program to do the job. We also decided to make the program as generic as possible, so that we could make it available to other developers and CR users (with our original client's permission, of course).

Use the RDC?

Our first thought was to take advantage of the Report Design Component (RDC). The RDC, which is part of Crystal Reports Developer Edition, lets the programmer access all of a report's objects programmatically. It is implemented as a COM server, and can therefore be called from within VFP (or any other programming language that can act as a COM client).

To find the names of all the tables and fields in a given report, you would first obtain an object reference to the report. You then loop through all the sections in the report, obtaining a reference to all the objects within the section. Next, you test the object's Kind property to see if it is a field object. If it is, you use its Field property to find its field definition object, which in turn tells you if it is a database field. Finally, you use the Name property to determine the underlying table and field names.

However, a moment's thought told us that even that tortuous procedure was not the end of the story. Although it would have successfully found the fields that were placed directly in the report, it would have missed any fields that were embedded in Crystal formulae, SQL expressions, sort orders, selection criteria, sub reports and other types of report objects. It would have been possible to programmatically access each of those objects and to extract the fields from them, but it would have been a lot of work to do so.

Let CR do the work

We then realised that we could get Crystal Reports itself to do most of the work for us. One of the many report export options that CR supports is something called a report definition file. This is a text file that contains a description of all the objects used in the report - including the names of the all database fields, conveniently delimited with curly brackets (Figure 1). It doesn't matter if the field was placed directly in the report or if it appears in a formula, SQL expression, selection criterion or elsewhere. It will still show up somewhere in the report definition file.

Figure 1: The name of every table and field used in the report will appear somewhere in the report definition file.

You can create a report definition file interactively within CR - just select the appropriate option from the Export dialogue. You can also do the job programmatically, by calling the report object's Export method. Either way, the resulting text file can be parsed programmatically to extract all the strings that are contained within curly brackets. These can then be written to a table.

There was one small complication. The names of formulae, parameters, SQL expressions and running totals also appear in curly brackets within the report definition file. Fortunately, these are easy to recognise, as their names are preceded by special characters (@, ?, % and # respectively). To filter out these objects, you just need to check that the first character of the name is a letter.

The listing (below) shows our program. It assumes that you have already obtained the report definition file. The program writes its output to a Foxpro table named CR_Documentation.DBF. The table will have two fields: one to hold the table name and the other for the field name. It would be a simple matter to create a report from this table, listing the field names grouped by table name. Note that the program does not attempt to eliminate duplicate field names (where the same field is used more than once), but this can easily be handled within the final report.

It turns out that Visual FoxPro is the ideal language to write this sort of utility, given its highly-efficient string-handling functions. In particular, the STREXTRACT() function provides a powerful way of extracting substrings enclosed in delimiters, which was exactly what we needed for this job. (Note that STREXTRACT() is only available in VFP 7.0 and above.)

Here then is our program. If you wish to use it yourself, just copy and paste the following code into a PRG file, then go ahead and run it.

* Crystal Reports Documentation Utility.
* Written by Mike Lewis Consultants Ltd, December 2003
* Requires Visual FoxPro 7.0 or above.

* Reads a report definition file created by Crystal Reports.
* Stores the names of all the database fields and their 
* corresponding tables in a FoxPro table, which can in turn 
* be used as the data source for a documentation report.

LOCAL lcInputFile, lcContents, lcOldSafety, lnPos, lcField

* Start with a clean slate
CLOSE DATABASES

* Ask user for the name of the report definition file
lcInputFile = GETFILE("Report Defintion (*.txt):TXT")
IF EMPTY(lcInputFile)
  * User cancelled
  RETURN 
ENDIF

* Read the contents of the file into a string
lcContents = FILETOSTR(lcInputFile)

* Create a table to hold the results
lcOldSafety = SET("SAFETY")
SET SAFETY OFF
CREATE TABLE CR_Documentation ;
  (Tablename C(128), Fieldname C(128)) 
SET SAFETY &lcOldSafety

* Extract all possible field names from the input
lnPos = 1
DO WHILE .T.
  * Look for the next occurrence of a string delimited
  * with curly brackets
  lcField = STREXTRACT(lcContents,"{","}",lnPos)
  
  IF EMPTY(lcField)
    * No further string found
    EXIT
  ENDIF 
  
  lnPos = lnPos + 1 
  
  * Check that the string is a database field (as opposed 
  * to a formula, parameter, running total, etc.)
  IF ISALPHA(lcField)
    * Begins with a letter, so assume it is a database
    * field. Add it to the table.
    APPEND BLANK 
    REPLACE Tablename WITH JUSTSTEM(lcField), ;
      Fieldname WITH JUSTEXT(lcField)
  ENDIF
ENDDO

* Tell the user how many fields were found
MESSAGEBOX("Successfully created list of fields" + ;
  CHR(10) + TRANSFORM(RECCOUNT()) + " fields detected")

* Close the output table
* USE IN CR_Documentation

RETURN

Note to non-FoxPro users: A free-standing version of the program described in this article is also available. Please see Find the tables and fields used in your Crystal reports.

See also:

• Using Crystal Reports with Visual FoxPro
• More articles on Crystal Reports

Mike Lewis Consultants Ltd. May 2007.