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.
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.
