September 20, 2019 at 8:40 pm
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:
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.
September 20, 2019 at 9:37 pm
You shouldn't have to redo the shared datasource though, that should still work. I'm wondering if there's a way to do this in the rdl by editing the text, and then you could just write a Powershell script or something to modify the files en masse. I don't really see an attribute value for embedded or shared though (yet)
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
September 20, 2019 at 9:49 pm
By default, they all go the same place but you can change the target folder for datasets and sources from the properties on each project in SSDT.
September 21, 2019 at 3:58 pm
There is no reason you need to store 'shared' datasources and datasets in the default folder. You can create any folder structure you need to support your organization.
For deployment purposes - is there really a reason for deploying a report to a development instance/space? Is there some testing/validation that is required for that report that cannot be done from SSDT or from a local instance of SSRS on the workstation? For QA purposes - permissions to allow only the QA testers access to the report running against the appropriate environment should work just as well...
SSRS is very flexible in how you set it up...I would definitely not move to embedded data sources, as any change to the external systems (upgrades, migrations, etc...) would required modification of every reports data source and redeployment instead of a simple change to a shared data source.
As for shared data sets - I only find them useful for situations where it takes too long to generate and we don't need real-time data. Then we can schedule a refresh of the data set and use that in our reports instead of taking minutes to pull the data from the database every time the report is run...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 22, 2019 at 2:15 pm
Gilbert and Jeffrey,
You can change the target folder for shared data sources and (presumably) shared datasets? Really? How?
Jeffrey, to answer your questions, for development I test it on my workstation. I don't have an instance of SSRS report manager running on it. That's an interesting idea, which I didn't even think of and at this point have no idea how I'd do it. For QA/testing the testers do have access to the reports, but since the reports for QA/test and production are on the same server, and since I wasn't aware of the fact that it is possible to point to different target folders for shared data sources and shared datasets, right now both QA/test and production, when I deploy them, will stomp over whatever was there before. However, I really like you guys suggestion of targeting different folders!!! That seems like the best way to handle this situation.
Jonathan, you brought up an interesting idea too, of using PowerShell to modify the the files I'm guessing during deployment. We use TFS 2015 currently. I'd love to know how I could go about modifying the .rdl files and whatever the files are for shared data sources and shared datasets, so that I can change the database pointed to for test vs. production. Can you give me some direction as to how that can be accomplished, please?
Kindest Regards, Rod Connect with me on LinkedIn.
September 23, 2019 at 1:42 pm
I'll be honest, I haven't tried this, but I figured it might be possible. Looks like this might be what you need to start with browsing the multiple files using Powershell, but it depends on there being certain values in your rdl that can be modified to change one to the other, which I haven't found yet either.
I think, from reading this ( https://social.msdn.microsoft.com/Forums/sqlserver/en-US/15579c78-26f1-46ad-84dc-49db6c6c29ae/ssrs-2010-shared-dataset ) that if it is embedded it is defined within the rdl, including columns, but if it is shared, it is actually stored as an rsd extension, which means it would not be as simple as just adjusting a string in the xml, unfortunately. I just got a new laptop and have to get IT to install the SSRS extension in VS at the moment, so am unable to play with it. Perhaps it would be a "cut from the rsd, paste into the rdl at the right point" operation, but no idea.
There, one more of "Jon's bad ideas" completely spit out, have fun with it.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
September 23, 2019 at 3:42 pm
when you right-click -> Properties on the Project in SSDT Solution Explorer, you should see the options under Deployment. TargetDatasetFolder will just have Datasets but you can change it to something like MySpecificProject/Datasets. That would create a new Datasets folder in the Project's folder and that project's datasets would go there on deployment. Same deal for TargetDatasourceFolder.
September 23, 2019 at 6:55 pm
You can download and install developer edition.
To change where items are deployed - you change the project properties (Project | {project name} Properties). This is where you define the server to deploy to...the folders - as well as the target version.
By deploying to a QA/Test folder structure you can control who can access those folders for testing. The folder structure does not have to match your production folder layout - and it can be hidden from normal users (since they won't have permissions to that folder). In fact, it can also be marked as hidden so it isn't normally visible unless the user checks to view hidden folders.
If I need to test a report - I will deploy the report to a 'hidden' folder that only I have access. I have created separate data sources for test and prod in the default location - so after deploying the report I modify the shared data source in Report Manager and test it. Once I am satisfied, I reset the folder destinations to the final location and deploy.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply