Search code examples
sql-server-2008reporting-servicesdatasetssrs-2008

Using shared datasets across different projects

facts: Visual Studio 2010, SQL Server 2008 R2, SQL Server 2008 R2 Reporting Services

I have a solution that consists of several projects. This is because of the inability to create nested deploy folders.

So my solution looks like this:

  • Students -> reports deploy to Students
  • StudentsGraduated -> reports deploy to Students/Graduated
  • StudentsGraduatedWithHonours _> reports deploy to Students/Graduated/WithHonours
  • StudentsBlueEyes -> reports deploy to Students/BlueEyes
  • StudentsGreenEyes -> reports deploy to Students/GreenEyes

All reports should use one dataset, with proper filters.

So I set up deployment so that each project deploys its datasets in /Student/DataSets. I made sure that each project has a identical dataset so that, when deployed, they all use the same dataset.

But apparently this does not work. Only the last deployed report can use the dataset :(

  • If I put all reports using a dataset in the same project it works but I have manually to move all reports to their proper locations.
  • Setting up all projects to deploy their reports in their own DataSet folder works too, but then I have a lot of identical DataSets lying around, which kinda beats the purpose.
  • After deployment manually resetting the dataset using the reportmanager works but is rather a tedious exercise when you have like 7 datasets on avg in each report and 19 reports......

Is this known phenomenon? Is it possible to have reports from different projects use the same dataset? If so, how?

I really would like to know your thoughts on this......

Solution

  • The way I was doing it should according to this link be the right way. So I redid it, following the steps outlined in this link by Charlie Liao: 1. create the datasets in one of your projects 2. ADD these as existing items to another project 3. Set Overwrite Datasets to TRUE.

    I think it was step 2 where I went wrong. I just recreated the dataset in each project, I did not add them. I deleted all datasets in all projects (but the first) and then readded all datasets to all projects as existing items, set Overwrite to True and deployed.

    MAGIC!!

    :)