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?

score 0 · Answer 1 · answered Jan 04 '17 at 15:07

0

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

answered Jan 04 '17 at 15:07

LONG

4,490
2
17
35

This assumes that the stored procedures are only ever called by report execution – Paul Bambury Jan 04 '17 at 15:11
Hi Long, thanks for the reply its much appreciated, It seems troublesome to have to re-log all the parameters a second time when they have already been stored in the ExecutionLogStorage table, even if they are not in the most easily usable format. I also have many many stored procedures I would need to add this example code to which would take considerable time. – PJD Jan 04 '17 at 15:12
Hope this will help you in your case: http://jegansubramaniam.blogspot.com/2012/02/ssrs-method-to-retrieve-last-run-report.html – LONG Jan 04 '17 at 15:28

Paul Bambury · Answer 2 · 2017-01-05T11:40:55.130

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.

Hi Paul, thanks for the reply, I did come across this code on my travels and did try to run it but am getting very little back, I have removed the where clause to get everything from my ExecutionLogStorage but keep getting back one column called ParValue with a record value of ParameterName. Is there anything else that I need to do to make this work, any other help would be great! — PJD, Jan 04 '17 at 15:39
Query as provided is for a single report's most recent execution. To get all reports/executions, remove the "SELECT TOP 1 ParValue FROM (" and then everything after the "WHERE c.Name..." clause. You'd probably remove the UserName where clause as well. Of course we're stuck with getting it use the parameter names in each report. These can found the Parameter column of the Catalog table. Although ntext its actually XML so can be queried. — Paul Bambury, Jan 05 '17 at 09:47
It's a quiet morning (staff/students not back till next week!) and interest piqued so I've gone a little further. See my revised answer for a solution — Paul Bambury, Jan 05 '17 at 11:19
Hi Paul, Thanks for spending the time assisting with this, I had also gone somewhat in the same direction as yourself but your code looks a heck of a lot neater. This is indeed pulling what I wanted, but as you said does not work for multi-value parameters but is a great stepping off point and will point me in the right direction, if you want a copy of the finished code please let me know through private message. — PJD, Jan 05 '17 at 14:25

SSRS Report Parameters passed out

2 Answers2