Step by Step Tips - Analyzing which tables are used in your Crystal Reports using .rpt Inspector

Finding which tables are used, or not used in your Crystal Reports can be very useful. It can help you do impact analysis when making changes to your tables, views, or stored procedures. It can help you document. And it help you analyze and standardize.

Impact Analysis

Let's take for example...

• There's a table called Customer. You're planning on migrating to a new version of your database and now need to know which reports this migration will impact.
• The field Customer_Name has changed in table Customer to CustName and you need to know which reports this will impact.
• The DBA has examined the reason some of your Crystal Reports run slow and feels that moving some of the logic to a stored procedure will improve performance but isn't sure which reports this change will impact.

These are just a handful of common issues that Crystal Reports designers face. Wouldn't you like to know which of your Crystal Reports would be impacted by any of the above and many other scenarios like them to analyze and/or document (i.e. export the information to a CSV file or an Excel .XLS file) the tables that are used and/or not used in your Crystal Reports?

After you've learned how to analyze and/or document the fields that are used in your Crystal Reports you can then use the Change data source / database migration and conversion wizard in .rpt Inspector to update the affected Crystal Reports.

That's where .rpt Inspector's Database Analyzer tool comes in!

With .rpt Inspector's Database Analyzer tool you can quickly and easily find the tables that are used in your Crystal Reports.

I. Open all the Crystal Reports you want analyze in .rpt Inspector

Click here for an explanation of the .rpt Inspector user interface and an explanation of what each of the tabs in the Objects pane do.

II. On the Reports tab of the Objects pane or on the Reports Tree pane, from the opened Crystal Reports, select the Crystal Reports that you want to work with. In most cases, it would be all the Crystal Reports you opened.

Throughout the product, there are context sensitive right mouse click pop-up menus. For example, you can quickly select all Crystal Reports on the Reports tab of the Objects pane by right clicking on the grid on this tab to reveal a pop-up menu and then clicking on Select All or using the keyboard accelerator keys Ctrl A to do the same.

III. Click on the Database tab.

IV. Unlike other tabs in the Objects pane, the Database tab grid contains items that are pre-grouped by ReportFileName property and TableAliasName property. But for our purpose, we'll create our own groupings.

The properties on this tab are displayed as read-only. To make changes to the data source / database, or to make conversion / migration -- steps that Crystal Reports refers to as Change Data source / Set Location / Convert Driver / Verify Database -- you can use .rpt Inspector's Change data source / database migration and conversion wizard available in TOOLS|WIZARDS menu. The concept of this wizard is to work with one or many sources and databases and point them to one destination at the same time and therefore supports batch Change Data source / Set Location / Convert Driver / Verify Database.

V. In the filter, click on the drop-down and change the filter from Fields and Tables to Tables.

VI. Right click on any row (except the top most row which is the header that says: "Grouped by: ReportFileName") in the grid of the Objects pane, of the Database tab. Then from the popup menu, select Ungroup All.

VII. Click on any row in the grid of the Objects pane, of the Database tab to have its properties displayed in the Properties pane (top middle pane) for the table.

You can dynamically add any listed property to the Objects pane, of the Database tab grid. Such as ConnectBufferString, DatabaseType, DllName, Location, LogOnDatabaseName, LogOnServerName, etc. Click here for a more in-depth explanation and step by step guide that discusses our Drag and Group concept.

Taking it further...

VIII. You can now even Export the contents of this tab's grid with the dynamically created column properties to either a CSV file or an Excel .XLS file.

IX. Click on the LogOnServerName property label in the Properties pane and drag and drop this property to the grid on the Objects pane, of the Database tab to create a new column dynamially in this grid called LogOnServerName.

Click here for a more in-depth explanation and step by step guide that discusses our Drag and Group concept.

X. Right click on the newly created column LogOnServerName and select Group by LogOnServerName from the pop-up menu.

XI. You can also add another level to the grouping by also grouping by the Report Title by right clicking on the column ReportFileName and selecting Group by ReportFileName from the pop-up menu.

To change the grouping order (i.e. move Report Title down a level) you can right click on the grouping property label row (row that has a background color, i.e. Report Title) and you'll see options in the pop-up menu to Move the order around. The concept of drag and group can be applied to other properties and other tabs.

There you have it. These steps take only a few minutes execute. Now you can analyze and/or document the tables (and apply this concept to fields, views, drivers, fonts, colors, formulas, parameters, etc.) in your Crystal Reports with confidence! Whether it's one report or a couple hundred Crystal Reports at a time -- in only a few minutes the easy way with .rpt Inspector.