What lessons have we learned about managing report projects and what are the best practices?
Since each report is stored in a separate XML-based RDL file, Reporting Services projects play well with version control. Visual Studio Team System and Visual Source Safe integrate with the Visual Studio/BIDS shell and third-party source control solutions like SVN may be managed from the file system. Reports that have subreports, drill-through reports or other dependancies should be managed in a BIDS project. Report Builder 2.0 or 3.0 may also be used but designers should mange their reports on the server rather than in the file system. It's best to use one tool or the other for any given report.
The deployment approach can be no different than your application development build
process but it shouldn't be over complicated. Over all, experience has proven
that report projects are best kept simple because you typically don't have the same
level of component interdependancies that you would in dev. projects.
In my experience, there are two different approaches that may work depending on your team structure and methodology. You can manage the reports through all deployment phases from Visual Studio. This puts the developer/report designer in the position to manage reports coming out of testing and into production, which may not always be ideal in a formal IT environment. The other approach is to have the developer/report designer deploy reports to a dev server and then for a deployment manager to capture and manage the RDL files in file system based version control, using scripts to deploy to QA, UAT & Production.
Keeping report definitions in projects and the server(s) synchronized can be tricky but it's managable as long as you set ground rules for the team, use version control and conduct regular status checks. RDL files don't have build numbers so it's important to keep one official copy of the most recent RDL in a designated project or folder. The RDL Scripter, mentioned in another blog post, can be a useful tool for cloning deployed reports to the file system.
Weblog by Paul Turley and SQL Server BI Blog.