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 updates to it, the fields can have the table 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 thought of 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 change the old field names with new field names. It handles changes to the name of a table differently. The report still uses the old table name in the formulas. But now these table names reference the new table name behind the scenes. On the surface, it appears that the table still references the old table because its name hasn’t changed. This new table name is called an alias.

An alias is a name that is assigned to a database table that isn’t the actual name of the table. If you replace a table with a new table, Crystal Reports refers to the new table using the same name as the original table. You can think of an alias as a variable that points to a table. You can change which table the variable points to, but the name of the variable 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 they are the same, you don’t even realize that an alias is being used. 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 CustomerData name. You might incorrectly think that the table name wasn’t changed. But it was.

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 that is a shorter name. If you have a table name that uses a cryptic naming schema, you can use the alias to give the table a more useful name. For example, rather than referring to a table as “AR970EOY” you could refer to it as “Accts Receivable 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.

The interesting thing about having an alias in a formula is that unlike re-mapping fields, aliases don’t change anything on the surface. Since formulas reference the name of the alias and this name never changes, then you don’t have to worry about updating the formulas.

If you are going to rename a table’s alias to make it easier to work with, make sure you do so before creating any formulas with that table. When you change a table’s alias, the formulas are not updated. Your formulas will quit working until you modify them to use the new alias name.

Another reason to use an alias is when want to use a table for a self-join SQL statement. You need to rename the alias of one of the tables before linking them together. The alias that the table is named doesn’t have any effect on the table’s actual name in the database. When you give a table an alias, Crystal Reports modifies the SELECT statement so that it uses the new alias name.

To manually change the alias of a table, open the Database Expert dialog box. 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 an 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 13-10. It tells you that there is already a table with that same alias and asks if you really want to add it to your current connections. If you click Yes, then it prompts you to enter a new alias name. The table gets added to your current connections using the new alias name.

Figure 13-10. 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 is. To find out which tables have aliases and which table is used, open the Set Location dialog box. When you click on the table name, the Properties node below the table shows you the details about the alias. The actual table name is shown in this information. The following figure shows that the alias CustomerData references the Customer table.

Figure 13-11. Finding a table’s alias.