RPT management utilities

Monday 19 July 2010 @ 2:29 pm

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 Miner by the Retsel Group ($379)

Report Miner is actually a three-tool set which allows you 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 or an entire folder of reports (including subfolders).  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.

4A. Report Analyzer by Cortex Systems (about $382 after currency conversion)
Report Analyzer is another set of tools to explore report features but not report comparison.  If you want to do comparison you partner Report Analyzer 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).

4B. Object Compare by Cortex Systems (about $130 after currency conversion)
Cortex Systems has chosen to create separate tools for analysis and comparison.  Use Object Compare to find the differences between reports.  Object Compare can also be used to find the differences between objects that are not RPT files, like folders, registries, database schemas and several other object types.

5A. Crystal SpellChecker by Redeemed Software.($150)
Check your reports for proper spelling, including labels, text fields, formula fields, and report properties (report name, description, etc.).

5B. Search and Replace by Redeemed Software. ($100)
Search through the formulas of multiple reports to find tables, fields or specific literal values.  It can also replace one table/field/value with another across multiple reports.

5C. Mass Verify by Redeemed Software. ($100)
Mass Verify is used to set the database location or to change other database connection settings on a group of reports.  Mass Verify also has some special features for Visual Dataflex users who are using the Native Dataflex tables (see their site for more info).

So, say you use MSSQL and your server location is SERVER1ACCOUNTING but your client uses GDTMSSQL1. With Mass Verify you would simply select the properties you wish to change and what the new values should be.

6. .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 has no peer 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), documention of the enterprise environment, and tools to monitor instances and schedules.









Leave a Reply

Recrystallize Pro