Setting a Table’s Alias

When fields are added to a report, they are referenced by a combination of their table name and their field name. During the course of designing a report and making changes to it, the fields can have their tables renamed or the table can be replaced with a different table. If you created formulas based upon a field in a table that has changed, then these formulas could potentially become invalid. In this situation, you would expect to have to go back through the formulas and update the table names for each field. Fortunately, Crystal Reports has a way around this problem.

In the prior discussion about re-mapping old field names to new field names, you learned that Crystal Reports automatically performs a search-and-replace to overwrite the old field names with new field names. However, it handles changes to the name of a table differently. The report still uses the old table name in the formulas, but behind the scenes these table names now reference the new table name. This can be a bit confusing because on the surface it appears that the formula still references the old table because its name hasn’t changed. However, you are really looking at the table’s alias.

An alias is a name that is assigned to a database table that isn’t the actual name of the table. You can think of an alias as a unique identifier that points to a table. You can change which table the unique identifier points to, but the name of this identifier never changes.

Every table in a report is referred to by an alias. When a table is first added to a report, an alias is created and its name matches the name of the table. Since the table and its alias have the same name you don’t even realize that an alias was created. When you use the Set Location dialog box to change a data source’s table to a new table, the alias name stays the same, but the table it refers to is now different.

As an example, consider a report that prints fields from a table called CustomerData. The table is later modified so that the name is now called Customer. You use the Set Location dialog box to change the CustomerData table to the Customer table. When you close the dialog box you will see that the formulas still reference the table using the CustomerData table name. You might get confused and wonder if you really changed the table name or not. Rest assured that you did change the table name, you are just looking at the alias.

You can also use aliases to make it easier to design a report. For example, if you are using a table name that is extremely long, you can use an alias to give it a shorter name. Some companies have large databases and use a very cryptic specification for naming tables. You can use an alias to give the table a more readable name. For example, rather than referring to a financial table as “AR2007EOY” you could refer to it as “Accts Receivable 2007 Year End”. Everywhere in your report where this table is referenced, you will see the alias name that you assigned it rather than the actual name of the table used in the database.

To manually change the alias of a table, you have to go into the Database Expert dialog box. Select the menu options Database > Database Expert. This opens the Database Expert dialog box with the selected tables shown in the right-most window. Click on the table that you want to assign an alias to and press the F2 key. This puts the name in edit mode and you can change it.

If you are using multiple data sources, it’s possible that you will add two tables with the same name to your report. Crystal Reports forces you to give one of the tables a new alias so that there isn’t a naming conflict. Before it lets you add the table, it prompts you with the Database Warning dialog box in Figure 10-21 It tells you that there is already a table with that same alias and asks if you really want to add another alias for that table. If you click Yes, then it uses the table name with “_1” appended to the end of the filename. The table gets added to your current connections using the new alias name.

Figure 10-21. The Database Warning dialog box.

It isn’t obvious when a table is using an alias that is different than the actual table name. If you are given a report that you didn’t design, a table that uses an alias can make it difficult to determine what the actual table name really is. To find out which tables have aliases and what table is used, open the Set Location dialog box. The drop down box lists the table aliases that are associated with each data source. Directly beside the alias name is the actual table name shown in parentheses. You can also click on the Properties folder to see the actual table name. See Figure 10-22 for an example of using an alias with the Customer table.

Figure 10-22. Finding a table’s alias.