SSRS Report Parameters passed out

Question

I am currently building a number of logging and analysis tools to keep tabs on our SQL environment. We are currently using SQL Server 2014.

What I want to do is keep check of all the parameters that are passed to our reports during the day. All of the reports are currently using stored procedures so in my table or a select statement based on a table is output the stored procedure with the parameters for every time the report was run.

At the end of the day I would then like to be able to take the outputted statement and run it in SSMS without having to use the report. I have been looking at the ExceutionLogStorage table and the ExecutionLog view's and though it has most of the information that I need, the parameters are not in an easily usable state.

Has anyone done something similar to what I have described?

Answer 1

You need to add logging part in your original SP, for example:

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

Then query directly against that loggingTable for getting the history of used parameters

Answer 2

A Google search around this topic quickly brought up the following blog post already identified by the OP as useful and shown below (this query itself is actually an expansion of work linked to by LONG's answer below)

  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. DOMAIN\Joe_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;

Both these approaches though really only give us a starting point since they (variously) rely upon us knowing in advance the report name and parameter names. Whilst we can quickly make a couple of changes to Ken Bowman's work to get it to run against all executions of all reports, we still have the problem that the query hardcodes the parameter name.

The parameters required to execute a report are stored on the Catalog table in the Parameter column. Although the column has a datatype ntext, it is actually storing an XML string. Meaning we can use an XPath query to get at the parameter names

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;

Executing this query will list all reports on the server and their parameters. Now we just need to combine this with Ken Bowman's query. I've gone with a CTE approach

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;

Note that the parameter values are passed to the report as part of a URL, so you'll still need get rid the literal space encoding and so on. Also, this doesn't (yet...) work for multi-value parameters.