Getting the Last Run Report Parameter Value for an SSRS Report

I recently had a request by a report user to change the default for a couple of parameters in an SSRS report. Because default values were set on the report, it would automatically run when opened and the user would have to change the parameters to his preference and run the report again… every time. This seemed like a reasonable request except for the fact that the report was used by many people who did not necessarily want the default values changed. The alternative of not setting default values and forcing all report users to select them prior to running the report was an option, but would negatively impact those report users who were already satisfied with the existing defaults.

As the users of this report were rarely changing these particular parameters (other than to change the default value), I thought that if I could get the last value that they specified when running the report, it would generally be the value they’d prefer to use as the default.

Having created a few reports summarizing SSRS usage in the past, I knew that the parameters were logged, however rather than trying to write something from scratch, I turned to Google for an answer and came upon this blog post by Jegan, SSRS - Method to retrieve last run report parameters, which laid most of the groundwork for me.

In the post, he lays out a simple query to access all of the entries in the ExecutionLogStorage table for a single report. He then goes on to get a parameter value from the Parameters field for the most recent entry using knowledge of the existence of a second parameter to parse the string. I take a different tact below, using the parameter name/value pair delimiter (i.e. the ampersand) and the equals sign to isolate the parameter name (that's why I added the ampersand to the beginning of the parameter list) and value.

The result below handles parameter name/value pairs at the beginning or the end (why I add the ampersand to the end of the parameters list) of the string as well as anywhere in between. It also handles the case where the parameter name doesn't exist in the string (IIF) or if no records exist (UNION ALL) for the report name and user name combination. There are many ways to write the query below and get the correct results... I tried to write this for performance and the clarity of the SUBSTRING function.

SELECT TOP 1 ParValue
  FROM (
    SELECT els.TimeEnd 
      , IIF(CHARINDEX('&' + 'ParName' + '=', ParsString) = 0, 'DefaultParValue',
        SUBSTRING(ParsString 
          , StartIndex
          , CHARINDEX('&', ParsString, StartIndex) - StartIndex)) AS ParValue
      FROM (SELECT ReportID, TimeEnd 
            , '&' + CONVERT(VARCHAR(MAX), Parameters) + '&' AS ParsString 
            , CHARINDEX('&' + 'ParName' + '=', '&' + CONVERT(VARCHAR(MAX), Parameters) + '&') 
              + LEN('&' + 'ParName' + '=') 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.rdl'
    UNION ALL
    SELECT CAST('2000-01-01' AS DateTime), 'DefaultParValue' 
  ) i
  ORDER BY TimeEnd DESC

The above could be re-written to get the values of several parameters at the same time to be used within a single SSRS dataset, however not much is gained as Reporting Services executes the dataset query for each parameter that it is attached to. Therefore, it may make sense to parameterize the arguments (i.e. the user name, report name, parameter name, and default parameter value) and turn it into a stored procedure. This may also prevent you from having to add a data source pointing to your Reporting Services database.

A few things to note when using this code to access parameter values for use within an SSRS report...

First, if you have Boolean parameters that you wish to use this with, you’ll have to convert them to Text. Also, it should be clear that this does not work with multi-value parameters (I'll save that for a future post).

Second, as Jegan notes in his blog post, "the parameter value passed to the report when previewing in visual studio won't be logged in ReportServer database as the report execution is not from ReportServer". This means you will need to deploy the report to see that the default changes on subsequent report launches.

Finally, in my environment, passing Globals!ReportName as a parameter value in Visual Studio 2010 returns “ReportName” (i.e. the report name without the extension), whereas 2012 SQL Server Reporting Services (Sharepoint integrated mode) returns “ReportName.rdl”. Just something to be aware of as you need to use the report name with the extension.

Oh, just one more thing. Many would advise against this as its hitting the Reporting Services database which could affect performance, or Microsoft could change the database structure, breaking your query, etc. Use at your own risk.