Need a good version control for SQL and Crystal Reports

Question

We have several large data stores where we warehouse data for metric reporting. One is SQL 2000 and the other is 2005. We use Crystal Reports 11 as our reporting tool.

Over the past several weeks we've had a couple very visible reports "crash" due to changes in the Db or changes within the reports.

To minimalize these errors I am looking into putting the scripts for our databases and Crystal reports into some sort of version control. Can someone point me the right direction on how I can get these assets under some sort of version control? We do have subversion in our company, would this work?

Answer 1

There are 2 ways to handle this:

1. You can use any Version control (Visual Source Safe, SVN etc). Each time you change the report file export it to report definition format (menu File->Print->Export , format: Report Definition) and add both the report and the report definition files to the version control. Report definition file is a text file and could be compared as any other text file, so you will be able to see what was changed. The report file is a binary file and cannot be compared with regular version control software, but you can use it to restore the previous version if necessary. The advantage of this method is that it is free. The disadvantages are that you have to do additional steps and the comparison is limited to what is exported to report definition file. For example formats, positions, sizes etc will be not exported.

2. There is a tool : R-Tag Crystal Version Control. This is a version control just for Crystal reports and will work as a regular version control but will also compare the binary report files. In addition you will be able to search the reports' structure. This tool will compare much more than report definition from method 1 and will save you time because there are no additional steps and exports. The disadvantage compared to the first method is that this is a paid software. You can find more information here: http://www.r-tag.com/Pages/VersionControl.aspx

Answer 2

You can use standard version control tools for Crystal Report files. However dealing with the databases is a bit more difficult.

Visual Studio Team System 2008 Database Edition (Data Dude)

You can use this version of Visual Studio to manage your database, the definition of the database tables, views, stored procedures, functions, etc are stored as create scripts (as if you were starting from a blank DB). The visual studio functions then will create a database differential (Schema Compare or Data Compare) and generate the scripts that would be required to go from one version of the database to another (i.e. between DEV and TEST instances).

The database definitions are what gets put into version control (so you can see at any point in time what the database looked like) and Visual Studio fills in the rest by generating the proper scripts to go from one version to another.

The Hard Way

Keep track of your databases, scripts that modified the database and the migration pattern. If you wanted to get to a version of the database, you would start with an empty db, then run each script in succession until you reached the desired version of the database.

This is basically what Ruby on Rails does when using the db_migration features, it however can go backwards through versions if you coded the migration files correctly - but I assume you are working with .NET on Windows.

Answer 3

We are currently looking at a product called RptDiff by ReportMiner to help with crystal report version control. If we make significant changes to a report in our standard product and our customer has customized the old version, we would like to be able to tell what changes need to be applied easier than visually inspecting the report. I am currently at stack overflow looking for another option before we buy just to do our homework. So far we haven't seen any else report diff and what crystal offers for free (exporting text definitions).