Viewing the Available Tables
After selecting the data source to pull data from, you get to select the tables that have the data you want to print. Below the data source name is a list of the available tables, views and stored procedures to choose from. You might assume that this is a static list of tables. But Crystal Reports gives you the ability to customize what is shown in this list. By right-clicking on the data source and choosing Options, it opens the Database Options dialog box. You can also open this dialog box by choosing the menu options File > Options and clicking on the Database tab (shown in Figure 10-7).
Figure 10-7. Setting the database options.
The middle portion of this dialog box, titled Data Explorer, determines what is listed below the data source. There is a checkbox next to each option. The checkboxes that are selected by default are Tables, Views and Stored Procedures. The options to show System Tables and Synonyms are disabled. You can check and uncheck any of these options if you want to show or hide them on the Database Expert dialog box.
On the right side are two filters for determining which tables should be displayed. In corporations that have specifications for how tables are named, there are usually naming conventions in place which assign standard prefixes to the table name based upon the data they store. For example, financial application tables could have a prefix of ‘fn’. If you are working with a database with hundreds of tables, it can be difficult to find the table you need. By using the option to filter tables by name, you could tell Crystal Reports to only display the financial tables that start with ‘fn’.
When specifying the table name filter Crystal Reports gives you two characters that serve as wildcards. The ‘_’ represents any single character and the ‘%’ represents any length of characters. To specify that you only want to see the financial tables, you would use the filter ‘fn%’.
When you create a connection to the database server and look at the list of available tables and stored procedures, you might be confused to see a group of names that you don’t recognize. This is because SQL Server uses internal system tables and stored procedures for managing each database. These are tables that you do not need to be familiar with because they are used internally to maintain the database. When the Data Explorer section has the System Tables option unchecked, Crystal Reports hides the system tables. Unfortunately, this option doesn’t apply to stored procedures and there is no way to hide the system stored procedures. But once you get used to seeing them, it will be easy to ignore them and just locate the stored procedures you need.
The top portion of the Database Explorer determines how the tables and fields are shown throughout the report. This includes the Field Explorer window and anywhere you can pick from a list of fields. You can set the tables to either be shown by their table name, description or both. You can also set whether the tables and fields should be sorted alphabetically or shown in the natural order.
Crystal Reports XI R2 gives you the ability to change the sort order within the Field Explorer window. By right-clicking on the table name in the Field Explorer, you have the option to sort the database fields alphabetically.
The bottom portion of the dialog box, Advanced Options, controls different aspects of the report which effect report performance and how Crystal Reports talks to the database server. We cover these options in the areas that apply to them.
