Keeping Crystal Reports Portable Across Databases
At the risk of sounding too much like a Wikipedia entry, synonyms are words that have identical or very similar meanings with different words. And what does that have to do with a budding Crystal Report writer? Portability - that's what.
Supposing you are part of a development environment and have the luxury of multiple state instances (i.e. Development, Test, UAT, Live, etc) in MS SQL 2005 or higher. You would, therefore, have experience moving reports from one environment to another.
Now, on occasion (and it does happen, believe me), you will need the report to point to different tables/store procedures within the local database depending on the environment. For example, on the test and development environments you require locally held table data, whereas on the live box you have an independent replicated reporting server, and you need to point to that instead.
Here enters the humble synonym. It creates a very handy abstraction layer.
So in the above example 'syn_DATA' points to [SERVER1].[DEV].[dbo].[DATA] on the development box, however on the live box [SERVER3].[LIVE].[dbo] it would point to [REPORTS].[LIVE].[dbo].[DATA]. All of this is set at SQL Server level, so is transparent to the reports themselves.
This means, from a Crystal Reports point of view, that all you will need to do, when moving from one environment to another, is just re-point the server and the job's a good 'n.
Provided that the Synonyms are set up on the database first, Crystal Reports will be able to select them from the GUI.
I won't go into setting up a synonym, as there are reams of pages out there already covering that particular topic.