RPT Management Utilities

July 13, 2017

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 (PDF) that shows some of the specifics for comparison, including prices and the install base of each product.  If you have tried one of these products, or are currently using one, please let me know.  That way I can get a feel for the number of people who use the product.  And if you have time I would love to get your opinion of the product(s), either on or off the record.

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 they will do what you want in a way that works for you.  I have also not tried to evaluate “ease of use” because it is subjective. I recommend that you use this review to narrow your search, and then use the free trial before making a decision.

One note on compatibility with different CR versions.  My matrix shows the versions that are FULLY supported.  So if it says through v12 (CR 2008) that means that all the features of CR v12 are shown by the tool.  But even the tools that only fully support v11 can still be used with v12 reports. If you aren’t using any of the newest v12 features then the tool should still provide complete documentation. If you are using any of the new v12 features you can still generate documentation, but the new features will not be included.  If you rely heavily on the new features (calculated cross-tab columns, optional parameters, etc) you should use a tool that fully supports v12.

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 Jeff-Net (Free)
This is a simple tool that has been released for free. 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 ($499)
Reports are imported into a database. Imported reports can be analyzed on the screen and different types of report specifications can be printed.  All report properties can be searched, including subreport properties.  Any two reports/versions can be compared to find the differences in any report property.  This includes formula fields, formatting formulas, SQL expressions and even the SQL generated by the report.  When formulas are found to have changed the changes are highlighted.  It looks very similar to what you would see in a document comparison tool with additions and deletions in different colors.

R-tag Version Control upgrade ($1499)
For an additional $700 you can add R-Tag’s comprehensive source code control system for RPT files. The report files are imported directly into the database as binary objects. 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.  Version Control includes the license for Documentation and Search.

5. Report Miner by the Retsel Group ($379)

Report Miner is actually a three-tool set that was rewritten in 2013 using .NET.  Use the following components to explore a group of reports in greater detail:

Extract:
This is used to generate configurable report definition documents for RPT files. It can read a single report, an entire folder of reports (including subfolders) or a BOE repository.  The output can be in MS Word, text or in an MS Access database format. To me MS Access sounds the best because then you can use Crystal Reports to run your own reports … about your reports.  In each case you can adjust the level of detail that is extracted.  It even allows you to assign an “owner” for each Report in the Access database, which allows users to query the reports by owner.

RptDiff:
This allows you to compare two report files and shows all of the differences. 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.  There is even a $99 option to integrate this with Visual Source Safe.  This allows you to view the differences between reports directly from within VSS.

XRef (Table Cross-reference):
Provides a an Excel style quick cross-reference of the tables used in each report. The columns are file name, report title, sub-report name (if any) table name and record selection formula. Sort by any of the column and copy to Excel if needed.

6. Code Search Pro & Dev Surge 365 by Find it EZ Software Corp. ($369/$468)
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 unique 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, Oracle or SSRS reports. 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.

Dev Surge 365 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. This includes Crystal Reports, database objects, several programming languages and office documents. In all cases you can drill down to granular differences between the objects.

Find it EZ is an SAP partner, so their software is certified to work with the latest versions of Crystal including CR 2016 and CR Enterprise. They are also certified Microsoft Platform Ready for the latest editions of Windows (10), Windows Server (2012.R2), SQL Server (2016) and SQL Azure. They have a floating license edition certified for Citrix Server and a new Enterprise edition certified for both Business Objects Enterprise XI 3.1 & BI 4.1 as well as Crystal Server Enterprise 2008 through 2016.

7. Report Analyzer by Cortex Systems (about $498 after currency conversion)
(This vendor is interested in selling the IP rights to this software.)

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).

8. .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.








Leave a Reply

Jeff-Net
Versa Reports

remiCrystal reporting solution