Connecting to two different databases from the same report on different server paths
-
Hi everyone!
Does anyone know if it's possible to deploy a report to two different server paths on the same reporting server each pointing to a different data source?
The reason I'm asking is that we are in the process of upgrading our main business application and I need to figure out how to run dozens of our SSRS 2008 reports in parallel with our existing test environment for a few weeks.
We have one test SSRS server, two test versions of the same database on different SQL-Servers (one is a newer version). Reports are displayed on our Intranet (old ASP.NET web forms application).
I've created a new configuration in the SSRS solution, deployed all reports to two different server paths, refactored the old asp.net application to display reports using a single aspx page instead of an individual page for each report, all configurable for different environments through web.config transform. But I can't see how to deploy a shared data source to different server paths.
I would like to avoid a pain of going through each report to replace shared data sources with the embedded one.Is this possible?
If not, does anyone have any other solution for accessing different data sources from the same SSRS server? Or do you have to have different SSRS servers for different test environments?Thanks!
-
Are both instances 2008? This was actually be quite easy with 2012 onwards, as you can deploy datasources to the SSRS server, and then set the reports to use the datasources deployed on the server, rather than their own in built datasources.
You can't do this in 2008, so I'm hopeful that this is your older instance, which doesn't need changing?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Yes - you can deploy your reports multiple times to different folders on the same instance. The reports have to be built using a shared data source - not embedded - and you can then modify the report data source on the SSRS server to use the appropriate shared data source.
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 -
Thanks guys!
I, probably , haven't expressed myself clearly. I'll try again.
There is only one instance of SSRS 2008 on a web-server.
Two SQL databases on two different test SQL-servers (let's call them S1 and S2). The database has the same name on both servers.
I already deployed reports to two different folders (F1 and F2). Reports are the same in both folders and use shared data sources. But then I discovered that when I change DATASOURCE1 to point to S2 instead of S1 and deploy the reports into F2 it changes the connection for the reports in F1 as well.
I need to be able to have the same report in F1 and F2, using the same data source name, pointing to S1 and S2 respectively.I hope this diagram clarifies my jumbled explanation.
-
Okay - the problem is that you have your projects set to overwrite the shared data source, so when you deploy the report it will update the data source. To fix that - change the setting so it doesn't overwrite existing data sources. This way - when you deploy and the shared data source doesn't exist it will get created - but if it already exists nothing will happen.
Shared Data Sources are just objects in a folder - create a new folder or create duplicate data sources with different names. After deploying your reports to the second folder - go into the report configuration in SSRS and manage the report. From here you can change which shared data source is being used...just change this to the one you want that report to use.
Or, you can create a different data sources folder...then modify the project to use that new folder location for that set of reports and the system will create the new data sources with the same name in that new location.
I have done this on several occasions to test and verify reports are working on a new back-end database.
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 -
Thanks Jeffrey!
I'm quite new to SSRS, learning as I go.
So, it looks like you can't have 100% automated SSRS DevOps process with the configuration described above.
At the least you have to deploy your data sources to different folders first and then set Overwrite Data Sources to false for subsequent deployments.
Still, a big improvement for us here.Cheers
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply