SSRS报告参数已传递

Question

我目前正在构建许多日志记录和分析工具，以监视我们的SQL环境。我们目前正在使用SQL Server 2014。

我想做的是检查白天传递给我们报告的所有参数。所有报告当前都在使用存储过程，因此在我的表或基于表的select语句中，每次运行报告时都会输出带有参数的存储过程。

最后，我希望能够获得输出的语句并在SSMS中运行它，而不必使用报告。我一直在查看ExceptionLogStorage表和ExecutionLog视图，尽管它包含了我需要的大部分信息，但参数并不处于易于使用的状态。

有人做过类似我所描述的事情吗？

Answer 1

您需要在原始SP中添加日志记录部分，例如：

Alter procedure a
(@parameter)
As
Begin
..
..
Insert into loggingTable(col)
Values(@parameter)
..
..
End 

然后直接查询该日志表，以获取所用参数的历史记录

Answer 2

围绕这个主题在谷歌上搜索很快就找到了下面的博客文章，OP已经认为这篇文章很有用，如下所示(这个查询本身实际上是LONG下面答案链接到的工作的扩展)

SELECT TOP 1 ParValue
FROM (
SELECT els.TimeEnd 
, IIF(CHARINDEX('&' + 'ParameterName' + '=', ParsString) = 0, 'ParameterName',
SUBSTRING(ParsString 
, StartIndex
, CHARINDEX('&', ParsString, StartIndex) - StartIndex)) AS ParValue
FROM (SELECT ReportID, TimeEnd 
, '&' + CONVERT(VARCHAR(MAX), Parameters) + '&' AS ParsString 
, CHARINDEX('&' + 'ParameterName' + '=', '&' + CONVERT(VARCHAR(MAX), Parameters) + '&') 
+ LEN('&' + 'ParameterName' + '=') AS StartIndex 
FROM ExecutionLogStorage
WHERE UserName='UserName' -- e.g. DOMAINJoe_Smith
) AS els 
INNER JOIN [Catalog] AS c ON c.ItemID = els.ReportID
WHERE c.Name = 'ReportName'
UNION ALL
SELECT CAST('2000-01-01' AS DateTime), 'ParameterName'
) i
ORDER BY TimeEnd DESC;

尽管这两种方法实际上都只是给了我们一个起点，因为它们(不同地)依赖于我们提前知道报告名称和参数名称。虽然我们可以快速对Ken Bowman的工作进行一些更改，使其在所有报告的所有执行中运行，但我们仍然存在查询硬编码参数名称的问题。

执行报告所需的参数存储在"参数"列的"目录"表中。尽管该列的数据类型是ntext，但它实际上存储的是一个XML字符串。这意味着我们可以使用XPath查询来获取参数名称

with
CatalogData as (
select ItemID, [Path], [Name], cast(Parameter as xml) 'ParameterXml'
from Catalog
where [Type] = 2),
ReportParameters as (
select ItemID, [Path], [Name], ParameterXml, p.value('Name[1]', 'nvarchar(256)') 'ParameterName'
from CatalogData
cross apply ParameterXml.nodes('/Parameters/Parameter') as Parameters(p))
select *
from ReportParameters;

执行此查询将列出服务器上的所有报告及其参数。现在我们只需要将此与Ken Bowman的查询结合起来。我选择了CTE方法

with
CatalogData as (
select ItemID, [Path], [Name], cast(Parameter as xml) 'ParameterXml'
from Catalog
where [Type] = 2),
ReportParameters as (
select ItemID, [Path], [Name], p.value('Name[1]', 'nvarchar(256)') 'ParameterName'
from CatalogData
cross apply ParameterXml.nodes('/Parameters/Parameter') as Parameters(p))
select
els.TimeEnd
, c.[Name]
, rp.ParameterName
, iif(
charindex(
'&' + rp.ParameterName + '=', ParametersString) = 0
, rp.ParameterName, substring(ParametersString
, StartIndex, charindex('&', ParametersString, StartIndex) - StartIndex
)) 'ParameterValue'
from (
select
ReportID
, TimeEnd
, rp.ParameterName
, '&' + convert(varchar(max), Parameters) + '&' 'ParametersString'
, charindex(
'&' + rp.ParameterName + '=',
'&' + convert(varchar(max), Parameters) + '&'
) + len('&' + rp.ParameterName + '=') 'StartIndex'
from
ExecutionLogStorage
inner join ReportParameters rp on rp.ItemID = ReportID) AS els
inner join [Catalog] c on c.ItemID = els.ReportID
inner join ReportParameters rp on rp.ItemID = c.ItemID and rp.ParameterName = els.ParameterName;

请注意，参数值是作为URL的一部分传递到报表的，因此您仍然需要去掉文字空间编码等。此外，这(还…)不适用于多值参数。