A report needs to be run on multiple sites, each with its own connection string. These site names and connection strings are stored centrally.
报告需要在多个站点上运行,每个站点都有自己的连接字符串。这些站点名称和连接字符串集中存储。
I want to make a report that
我想做一份报告
- Accepts the site name as a parameter
- Queries for the sites connection string
- Uses the sites connection string to get the report's data
接受站点名称作为参数
查询站点连接字符串
使用站点连接字符串获取报告的数据
I've tried using two data sources in the report, where the first data source uses the site name parameter to retrieve the connection string and the second uses an 'embedded connection' with a function for its connection string.
我尝试在报告中使用两个数据源,其中第一个数据源使用站点名称参数来检索连接字符串,第二个数据源使用带有连接字符串函数的“嵌入式连接”。
=First(Fields!ConnectionString.Value, "SiteDetails")
However when I run the report I get:
但是,当我运行报告时,我得到:
[rsFieldInQueryParameterExpression] The expression used for the parameter ‘SiteDataSource’ in the dataset ‘SiteDataSource’ refers to a field. Fields cannot be used in query parameter expressions.
[rsFieldInQueryParameterExpression]数据集“SiteDataSource”中用于参数“SiteDataSource”的表达式是指字段。字段不能用于查询参数表达式。
Is there a way around this, to use a connection string from a database?
有没有办法解决这个问题,使用数据库中的连接字符串?
I am using SQL Server 2008 Reporting Services.
我正在使用SQL Server 2008 Reporting Services。
2 个解决方案
#1
I have had this working in SSRS 2005 by storing the connection string in a hidden report parameter, then setting the connection string of the second query using that parameter.
通过将连接字符串存储在隐藏的报表参数中,然后使用该参数设置第二个查询的连接字符串,我已经在SSRS 2005中使用了它。
#2
I wrote a blog post about something similar a while ago, you might find it usefull - http://www.beakersoft.co.uk/2008/06/22/setting-a-data-source-at-runtime-on-sql-reporting-services/
我写了一篇关于类似内容的博客文章,你可能会发现它很有用 - http://www.beakersoft.co.uk/2008/06/22/setting-a-data-source-at-runtime-on- SQL-报告服务/
#1
I have had this working in SSRS 2005 by storing the connection string in a hidden report parameter, then setting the connection string of the second query using that parameter.
通过将连接字符串存储在隐藏的报表参数中,然后使用该参数设置第二个查询的连接字符串,我已经在SSRS 2005中使用了它。
#2
I wrote a blog post about something similar a while ago, you might find it usefull - http://www.beakersoft.co.uk/2008/06/22/setting-a-data-source-at-runtime-on-sql-reporting-services/
我写了一篇关于类似内容的博客文章,你可能会发现它很有用 - http://www.beakersoft.co.uk/2008/06/22/setting-a-data-source-at-runtime-on- SQL-报告服务/