How can I share a Data Source between multiple projects in Microsoft SQL Server 2005 Reporting Services and keep Visual Studio "Preview"?

Question

I have a solution that contains multiple reporting projects (one per target deployment folder - I think this is the only way to achieve this effect, at least until I abandon Visual Studio for report deployment).

I want to specify my data source information "once and only once" for all these reports.

So far, I have created a separate reporting project that contains my shared data source. If I deploy things to a reporting server in the right order and offer sufficient prayers to appropriate gods, the reports seem to link up to the shared data source there and run (at least via the Report Manager in IE).

When I am developing a report, though, I can no longer "Preview" to try it out locally - I now must deploy it to a report server to try running it. This is a hassle.

Is my only recourse to add a whole bunch of copies of a data source (pointing at my development database), one in each project, set those not to deploy off my machine, and (probably) exclude them from source control?

Answer 1

Woody,

What we have tended to do is:

1. On the server have a folder called "DataSources", which is hidden from the users. In there will be all of the data sources.

2. For each reporting project in VS there will be a folder, also called "DataSources", but this time it will only contain the data source for this report.

As long as the folder structure is the same (i.e. report and data source have the same corresponding folder level on server and in VS) this seems to work for us.

Answer 2

A technique (dirty trick?) I am playing with now is to copy my data source (.rds) into each project, close Visual Studio, then in the underlying files/folders:

• Delete the copied .rds from my report projects (leaving only the one copy in my Data Sources project)
• In each report project's project file (Foo.rptproj), change the text of the Project.DataSources.ProjectItem.FullPath element from My Shared Data Source.rds to ..\Data Sources\My Shared Data Source.rds

This way all reporting projects reference the same underlying file on the filesystem, so they share a single data source definition, but each project also kind of has a "local" shared data source, so Visual Studio is kept happy.

Regarding source control: there is still only one copy of the .rds checked in, so we're not polluting the code base with lots of icky duplicates; the changes to the .rptproj files can be checked in, so we're not forcing developers into unnatural source-control gymnastics (selective partial commits etc.) to maintain a sane master copy.

Each reporting project will try to deploy this data source, though I've forbidden the overwriting of existing data sources on the server, so it's not too big a deal . . . and I suppose if I intended to overwrite the server's data source definition, it wouldn't really matter whether I overwrote it once or ten times with the same .rds.

Disclaimer: this is still an experiment. I don't have experience using this technique in practice yet, so I can't go so far as to actually recommend it.