I've been using SSRS, on and off, for about 10 years. I wouldn't call me an expert. In fact I'll prove that to you in this post.
One thing I've always done, when writing a new SSRS report, is create the data source and dataset as shared data sources and shared datasets. My reasoning is that, even if I don't reuse the dataset (I always reuse the data source), I might reuse a dataset down the line. However, I today I have made a discovery which blows my understanding out of the water. I've learned that a shared data source is saved in its own folder at the root of all folders in SSRS's report manager site. The same is true of shared datasets; they're saved in a root folder in SSRS's report manager site. That has, for me today, become a huge problem, due to my misunderstanding of what "shared" meant, in this case. I had thought that a shared data source and shared dataset were only shared within the SSRS project they're a part of. Not that shared data sources and shared datasets are global, through out all SSRS projects deployed to that SSRS server/site. This is a huge problem for us, as the DBAs have set up SSRS so that production, test and development (if its a part of that SSRS project) are all lumped together into one SSRS website/report manager.
You could argue that they shouldn't be all lumped together (and believe me, I'm going to), but it is what I've got to live with. The way I see it is we have 3 choices:
- Identify some other server for test SSRS
- Maybe, if its possible, install another instance of SQL Server onto the same server, a named instance, if it will allow us to define a separate SSRS report manager associated with that named instance. (I don't even know if this is possible, but if it is, then that would be an option.)
- I've got to go through the process of making all of the reports work with embedded data sources and embedded datasets.
Assuming I've got to do #3, I sure would like to make it so that I do not have to redo all of the reports I've written from shared data sources and datasets, to embedded data sources and datasets. Is that possible?
Or do I have to start over again from scratch?
Kindest Regards, Rod Connect with me on LinkedIn.