Have you ever wanted to:
1) Find all the reports that use a particular table, field or calculation?
2) Compare two versions of a report to see what was changed?
3) Make the same change to a group of reports?
The more reports you have, the more likely that you will face these challenges, and you can solve problems like these with the tools in this article. All of these tools can be found in the LINKS page of my site under RPT Management Utilities.
Below is a brief rundown on the core features of each product as well as what sets each product apart. I have also posted a more detailed feature matrix (xls) that shows some of the specifics for comparison, including prices.
In most cases I have not used these tools so I must rely on the vendor for information. So if you plan to purchase one of these tools, you will want to confirm with the vendor that it will do what you want in a way that works for you. I recommend that you use this review to narrow your search, and then use the free trial before making a decision.
First lets talk about what you can do for free. There are two features in all versions of Crystal Reports that allow you to accomplish (in a rough way) the first of the three challenges mentioned above.
1. The menu options Database-Show SQL Query
You can open any report that connects to SQL Based data and view the SQL that the report sends to the database. It may look awkward (with lots of usually unnecessary quotes and aliases) but it is workable. And there is one thing this query does for you that most people miss. It lists exactly which tables and fields are used by the report. So if you copy the SQL from each report and put them all in a document you would have a quick way to determine which reports use which tables or database fields. You could also look for outer joins or specific criteria (like “Customer”.”Dept” =). But creating and maintaining this document would be time consuming. Note that tables linked within the report but not USED in the report will only be included in the SQL if the table bridges the gap between two other tables that are used.
2. The report definition export.
One of the exports that are available in Crystal is the “Report Definition”. This is a text document that lists the key settings in the report such as:
File Version
Record Sort Fields
Group Sort Fields
Record Selection Formula
Group Selection Formula
Other Formulas (that are physically placed on the report)
Groups in the report
Objects in each section.
So compiling these text files would allow you to search for database information and formula information. But again this has to be generated one report at at time.
3) Report Runner Documentor by Known Keep (Free with any other licensed product)
This is a simple tool that comes for free with any other licensed product. You can import individual reports or entire folders which translates the primary report properties into a SQL Server database. You can then run the standard reports or you can connect Crystal Reports to the database to create your own reports (about your reports).
The standard reports include a report overview and several cross-reference reports showing which reports use which tables, fields and formulas. These standard reports can be exported to most of the export formats supported by CR.
Report Runner Documentor requires registration but is otherwise free. The registration Email must come from a business Email address. The initial download will actually work for 3 days and beyond that the free registration is required.
4) R-Tag Documentation & Search by R-Tag
($749 for 1 user + $99 for each additional user)
R-Tag Documentation and Search allows you to search, compare and generate documentation for your reports. You can scan one or multiple reports. The scan will include all report properties including tables, fields, formulas, conditions, formatting options, database, printer and custom properties. Scanned reports can then be searched, documented and compared based on the scanned properties of each report.
After scanning you can:
1) Search for a specific text string (table or field name), for a specific object name (*logo*) or a specific property value (printer, paper size).
2) Compare any two reports to find differences in any report property. Objects will be marked as changed, added or removed, with the changed values displayed. When formulas are found to have changed the changes are highlighted with additions and deletions in different colors.
3) Generate documentation for a single report or a folder of reports. The default layout displays the SQL query, parameters, tables, tables fields, formulas , SQL Expressions, connection information. This can be customized based on your requirements. Documentation can be exported as a PDF with a bookmark for each report and each page showing the selected properties.
R-tag Version Control ($1499)
R-Tag Version Control does everything mentioned above in R-Tag Documentation and Search, but adds a comprehensive source code control system for RPT files. The version control is provided by the R-Tag application, without the need for an external product (like VSS). To modify reports you check them out which locks that report until it is checked back in as a new version. Prior versions of a report are saved so that changes in each version can be identified and rolled back if necessary. R-Tag Version Control not only tracks the changes but can store related documentation for these changes like request documents and Emails.
5. CR Data Source Updater by R-Tag ($199)
CR Data Source focuses on updating the connection information in a group of reports. This includes updating table aliases and qualified table names as well as performing a “Verify Database”. It can even update reports to use the new TLS 1.2 drivers required for PCI compliance.
6. Visual Cut with DataLink Viewer by Millet Software ($300)
Visual Cut by Millet Software comes with a free license for DataLink Viewer. With this combination of tools you get several RPT management features. The main one is a new “expression inspector” feature.
This allows you to scan the objects in one or multiple reports, including their subreports. The inspector will then load all of the report objects into a grid. The list of objects includes all types of expressions (formulas, SQL expressions, selection formulas, condition formulas). The grid will includes database fields, database tables, data connections, selection formulas, subreport links, page and printer settings, text objects and parameters, in those target reports and all their subreports.
The grid allows you to group, sort and search the results. You can also mass-update text, SQL expressions, formulas, and property expressions through find & replace operations followed by saving all the updated reports to a new folder. You can even export the grid to Excel where you can use Excel logic to generate completely new formulas. These updated expressions and new formulas can be re-imported to create updated versions of the original reports. A slightly different method can be used to mass update the database connection information for a group of reports.
DataLink Viewer also adds a feature which taps into the Google translation API. This allows you to automatically translate the text and column headings of a report to or from any language that Google supports.
Visual CUT is $300 and includes a free license for DataLink Viewer. If your purchase DataLink Viewer all by itself ($25) you won’t be able to do the Find and Replace or the import of formulas and SQL expressions, but you can do everything else.
7. Report Miner by the Retsel Group ($369)
Report Miner provides you with user-configurable documentation of all report features (tables used, selection criteria, formulas, groups, totals, etc.) It can process a single report or an entire folder of reports (including subfolders).
Report Miner generates documentation as a formatted Word document containing an image of the report or as a plain text file. All report elements are extracted and described in detail. The level of detail that is extracted can be adjusted by the user.
Report Miner also creates a database of the individual report details from which further report analysis can be done using our standard reports (or your own reports). The provided Report Miner reports include a report-to-table-column cross-reference which identifies reports and the tables/columns they reference as well as how they are referenced (e.g., in the report selection formula, in a user formula, in a group-by, in a sort-by, and/or displayed on the report). The report is available sorted by table/column or by report. The table/column version would let you evaluate the impact of any planned database changes on your reports.
Report Miner can also compare two separate rpt files and identify differences between them. The two files compared can be two versions of the same report or two separate reports. You can choose to see just the differences, or a complete list of all features with the differences highlighted.
8. Code Search Pro by Find it EZ Software Corp. ($369)
This tool was designed to find all uses of a particular table or field in an entire software project. So it will find a table or field used in RPT files, application source code, database SQL code and data, documentation, config files and just about anything else. So when a schema changes you can see the overall impact of the change in one place. It even includes plugins for Visual Studio and Eclipse.
One unusual feature is that FinditEZ can scan the SQL generated by a report without requiring a database connection or parameter input. The SQL will not be exactly the same as the “Show SQL Query” window but is equivalent. That allows FinditEZ to search the query generated by a report including SQL expressions and commands.
FinditEZ can also provide documentation for a library of Crystal Reports, Oracle Reports and SSRS. It provides an enhanced Report Definition document that goes beyond the built-in Report Definition. It covers all of the objects and core features in an RPT file and adds Tables and Joins, SQL Commands, SQL Expressions, Images, Charts, Cross-tabs and even lines and boxes. And you can have it automatically generate a set of documents for your entire RPT library at once.
Find it EZ is an SAP partner, so their software is certified to work with all recent versions of Crystal Reports, including Crystal Report 2016, CR Server 2016 and Business Objects Enterprise XI 3.1 & BI 4.2.
9. Dev Surge 365 by Find it EZ Software Corp. ($39/mth/user)
Dev Surge 365 has all of the features found in Code Search Pro (see above) plus it adds integration with all major version control systems like git, TFS, VSS and SubVersion. It also adds the ability to mass update connection information within a BOE repository (Server, DB UID, PWD). It can do this at the report level or at the scheduled event level. And it adds the ability to do mass comparisons of files and database objects. In all cases you can drill down to granular differences between the objects. Dev Surge 365 also supports more BI and development tools including Crystal Reports, Oracle Reports, SSRS, SSIS, Tableau, WEBI Reports, Business Objects Universes, database objects, several programming languages and even office documents.
10. .rpt Inspector 3 Professional Suite by Software Forces, LLC ($395)
.rpt Inspector has something in all three of the key areas I discuss in this article – documentation, comparison and mass update. The documentation and comparison capabilities aren’t as robust as the tools described above. For instance when comparing reports it can automatically list all formulas or parameters that exist in only one instance of the report. But it can’t automatically list all the differences within these objects, like when a formula is changed or a parameter property has been modified.
However .rpt Inspector is unique when it comes to mass update. It has the ability to open multiple reports at the same time and then change virtually any object or property, across all of the open reports, in one stroke. There are specific “Object Editors” for each type of object in the report. These allow you to search, filter and group reports by essentially any object property. You can then update those properties across reports including:
Text Objects (Text, Font, Color, Size, Position, etc)
Formula and SQL Expression contents
Running Total, Parameter, Alert and Section settings
Database Analyzer – Data Source, Database, Driver, Connect String, Table, Field, etc.
Database Migration – Change Data Source, Set Location, Convert Driver, Verify Database.
Spell Checker that checks all text objects and formula contents.
And if you are an BO Enterprise user .rpt Inspector Enterprise Suite supports both Business Objects Enterprise and Crystal Enterprise. This tool adds version control integration (Microsoft Visual SourceSafe), documentation of the enterprise environment, and tools to monitor instances and schedules.
11. .rpt Inspector Online by Software Forces, LLC (Free)
.rpt Inspector Online is being listed for the first time in 2018. It has most of the features of .rpt Inspector Pro and new features are being actively added. You can see the feature differences in detail within the feature grid. The online version is currently available for free with unlimited use.
The main area where the online version is playing catch-up to the desktop tool is in mass update. It can currently mass update connection info, formulas and parameters. Note that the online version supports the latest runtime while the desktop version only supports up to version 11.5 (XIr2).
12. Report Analyzer by Cortex Systems.
(This vendor last responded to me in 2014, so contact them before purchasing this product.)
Report Analyzer is another set of tools to explore report features but not report comparison. If you want to do comparison you use Report Analyzer along with their Object Compare tool (see below). Report Analyzer comes with a library of stock reports that provide commonly needed analysis of RPT files. For example, the Database Dictionary report lists all tables and fields used in each report. You can also create your own customized reports. Here are the key components:
Search:
Find over 20 report objects quickly using the Search function. For example, find which reports are using a particular table or find which reports contain subreports.
Cross Reference (Field/summary Usage):
Select a field, formula or summary see where that field is used including other formulas, sorting, grouping, etc.
Formulas:
It will list all report formulas (including conditional formatting formulas) while highlighting potential errors and making design suggestions. It will also list formula properties, formula call trees (formulas that rely on other formulas), variables used and more.
Performance Suggestions:
Report Analyzer automatically detects potential performance issues in your report design and provides suggestions to correct design errors and common report setting mistakes, such as including unused objects or invalid database server settings.
There is also a Report Analyzer Enterprise Edition which allows you to document reports located on your Enterprise server (supports Crystal Enterprise 10 and Business Objects Enterprise XI).