Sing a Song of Synonym

With the risk of sounding too much like a Wikipedia entry - 'Synonyms' are words who 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.  Setting up a synonym in a database is like instead of pointing at a table or a stored procedure you set up a pointer A -> B, (where B is the table/SP) however in another database you can point the same thing elsewhere ie A -> C.  As long as both table/stored procedure returns the required fields everything will be fine.

Supposing you are part of a development environment and have the luxury of multiple state instances (ie Development, Test, UAT, Live etc) in MS SQL 2005 or higher.  You would have therefore have experience of 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 independant 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 dev 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 to 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.