SQL Server 2005报表生成器如何动态更改数据源？

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.

我们需要能够动态更改报表生成器中构建/管理的报表的数据源。基本上它是相同的数据库结构,但每个客户端都有自己的数据库,因此根据ClientID的参数,它需要动态转到相应的数据库。

Anybody have any good solutions for this?

有人有这个好的解决方案吗?

2 个解决方案

#1

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.

而不是动态地修改datsource(我甚至不确定是否可能),您可以创建一个存储过程,根据传递的参数动态地从不同的数据库中提取数据。

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

#2

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.

  编写一个小的.net应用程序来部署报告,数据源和报告模型。这比听起来更容易,因为SSRS导出了一个相当简单的Web服务来执行此操作。

• 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.

  IronPython是一种很好的方式来试验这个Web服务并找出如何驱动它。为此,您需要使用WSDL包装器生成器(WSDL.EXE IIRC)并制作一些C#存根。编译存根并使用IronPython注册库。然后使用交互式shell来试验Web服务API。如果你觉得那样倾向于用C#重写。

• 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.

  还有一个捆绑了SSRS的工具叫做rs.exe,它接受一个VB.net文件,顶部和尾部并编译结果,这样你就可以使用VB.net编写与报表服务器一起编写的脚本。

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.

此.net应用程序将部署报告并连接数据源。将其设置为可以在任何给定文件夹下部署。设置客户时,请在创建文件夹时设置的“数据源”文件夹下设置其数据源。您可以以编程方式部署报告文件,并使用几行代码将数据源连接到该文件。在客户端的文件夹下部署报告。

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.

这允许您部署(或克隆,如果您已实现下载功能)任何报告套件,并以编程方式将它们部署到服务器上特定于客户端的目录中。它还为您提供了一个简单的安全模型,因为您可以通过客户端的根目录保护对报告的访问。