Create Formula Field that Lists All Tables In Crystal Report

2
votes

I am looking for a way to create a formula field that will list out the table names of all tables that are being utilized as data sources in a Crystal Report.

I have not found any function that provides that capability in the application yet.

This would be used to put the list of tables as a supplemental for those users that do not have access to the report file but need to know what tables are used in the report.

It seemed better to do this as dynamically as possible - instead of having to provide a static list of current tables linked into the report.

Thanks.

1
crystal-reportscrystal-reports-2008

1 Answers

2
votes

Unfortunately, this type of functionality isn't present in Crystal Reports.

You might be able to use a UFL to solve the problem. General idea:

  • pass path of current report to UFL (the Filename function will give you this)
  • open the referenced report using the Crystal Reports SDK and examine the tables in DataDefintion class; the DatabaseFieldDefinition.UseCount will help determine if a field (and hence table) is referenced in the report
  • return the table names as a string array (note: CR only supports 1-dimensional arrays w/ a maximum of 1000 elements)

  • create a formula field to call the UFL's function; Join() the string array (formula fields can't return an array):

    Join(GetRptTables(Filename), ",")

  • distribute the UFL with the .RPT

Another option, if you have BusinessObjects Enterprise and a spare $40K, is Metadata Manager; this will give you a holistic view of your organization's reporting deployment.