We have a need to be able to dynamically change the Data source for a report built/managed in Report Builder. Basically its the same DB structure but each client has their own DB so depending on the parameter for the ClientID it needs to dynamically go to the appropriate DB.
Anybody have any good solutions for this?
Thanks in advance, Tom
Instead of dynamically chaning the datsource (which i'm not even sure if that's possible), you could just create a stored procedure that dynamically pulls the data from different databases based on the parameter passed.
CREATE PROCEDURE ProcedureName
-- Add the parameters for the stored procedure here
@clientName nvarchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @sql nvarchar(max)
set @sql='select * from ' + @clientName + '.dbo.Products'
exec(@sql)
END
GO
This is similar to the problem of deploying report models to a server. You can accomplsh what (I think) you need by adopting an architecture like the one described below.
Create a folder for each client on the reporting server, . Set up permissions appropriately so clients can't run each others' reports. Put a folder under this folder called 'Data Sources' or some such and place all of the reports' data sources in this folder. This means that any tool that programatically reconnects data sources knows exactly where the data source lives relative to the report. Note that the data sources should also have specific, predictable names.
Write a little .net app to deploy the reports, data sources and report models. This is easier than it sounds as SSRS exports a fairly straightforward web service to do this.
IronPython is quite a good way to experiment with this web service and figure out how to drive it. To do this you need to use the WSDL wrapper generator (WSDL.EXE IIRC) and make some C# stubs. Compile the stubs and register the library with IronPython. Then use the interactive shell to experiment with the web services API. Rewrite in C# if you feel that way inclined.
There is also a tool bundled with SSRS called rs.exe that takes a VB.net file, tops and tails it and compiles the result so you can use VB.net to write scripts that frig with the report server.
This .net application will deploy reports and connect data sources. Set it up so that it can deploy under any given folder. When you set up a customer, set up their data sources under the 'Data Sources' folder you set up when you created their folder. You can programatically deploy a report file and connect to it a data source in a few lines of code. Deploy the reports under the client's folder.
Parameterised by the server name and directory off the command line, the application can deploy a report and connect its data sources to the one under the 'Data Sources' folder. The references to data sources in the reports can have relative paths so the reports can be blissfully unaware of their position in the hierarchy.
Wrap it in a batch file or use some other means to automatically deploy the entire suite of reports. As a bonus, this makes it easy to deploy to test environments. For extra bonus points you can extend the application so it can walk arbitrary directories under your customer's root and download the reports it finds there.
Deploying report models is similar but has an extra twist. One of the properties of the report model is a list of reports that depend on it. You will need to capture this list (again, this is straightforward to do programatically) and reconnect the reports to the data source (the report model is the data source for these reports).
This allows you to deploy (or clone if you've implemented the download functionality) any suite of reports and programatically deploy them to a directory on the server that is specific to the client. It also gives you a simple security model as you can secure access to the reports through the client's root directory.