Ever worked on a quick little project, come across what you assume is a common need and think “I’ll just google up some code that someone else has written and then be off to the races”?  Have you done this and not been able to find the code you’re looking for? Well, that was me yesterday. I needed to parse a few parameters from the URL string stored in the SQL Server Reporting Services ExecutionLog3 Parameters column and thought for sure I’d be able to quickly find the code floating around the interweb. Strangely, no luck. I tried a few different google searches like: “SSRS Parse ExecutionLog Parameters”, “SQL Server Reporting Services Parse URL Parameters”, “ExecutionLog Parameters column”. Nothing. And thus this post was born.

There are a few reasons why one might want to parse out the parameters from the ReportServer ExecutionLog table. In our case we were interested in seeing which parameters were the most frequently used which would allow us to optimize the reports for those queries. In particular we were curious what date ranges were most commonly used by our report users.

The scalar function below takes two parameters, the column to search (typically, ExecutionLog.Parameters) and the specific parameter to search for.  For example, If we want to know the typical date range being used by our users when they run the “Top Perfomers” we can use the following query to parse out the StartDate and EndDate parameters in the Parameters column:

 

select top 1000 Parameters
, dbo.ParseSSRSExecutionLogParameters('StartDate',parameters)
, dbo.ParseSSRSExecutionLogParameters('EndDate',parameters)
, TimeDataRetrieval
, [RowCount]
from dbo.ExecutionLog3
where ItemPath = 'Top Performers'

 

This will return a result set which looks like:

Ta da! Beautiful right?
A few specs:

The parameter can be entered with or without a trailing equal sign. “StartDate” or “StartDate=” will return the same result.

If the entered parameter name is not found the function will return a null.

The function handles the most common URL encoding characters:

%2F is converted to forward slash (typically found in dates)

%20 is converted to a space

%3A is converted to a colon (typically found in time data).

Questions? Comments? Enhancements? Love it? Hate it? Let me know in the comments section!

IF object_id('dbo.ParseSSRSExecutionLogParameters', 'FN') is not null
BEGIN
	DROP FUNCTION dbo.ParseSSRSExecutionLogParameters
END
GO

CREATE FUNCTION dbo.ParseSSRSExecutionLogParameters
    (
     @ParameterToFind varchar(1000)
    ,@ParameterColumnName ntext
	)
RETURNS VARCHAR(1000)
AS
/*

Desc: Parse a specific parameter out of a URL string. 
Specifically designed to work with the SSRS ExecutionLog table and 
"parameters" column.

Sample exec:
select top 1000 dbo.ParseSSRSExecutionLogParameters('StartDate',parameters)
, dbo.ParseSSRSExecutionLogParameters('GroupId',parameters)
, dbo.ParseSSRSExecutionLogParameters('LocaleId',parameters)
, dbo.ParseSSRSExecutionLogParameters('EndDate',parameters)
, dbo.ParseSSRSExecutionLogParameters('VehicleId',parameters)
, parameters
, ItemPath
, *
from dbo.ExecutionLog3
order by 1 desc,2 desc,3 desc,4 desc,5 desc


--------------------------
2017-09-27	Phil Steffek		US25315 - Initial version.

*/
    BEGIN
	--If the input doesn't end with an equal then add it
	if substring(reverse(@ParameterToFind), 1,1)<>'='
	begin
	select @ParameterToFind=@ParameterToFind+'='
	end

        DECLARE @ParameterValue VARCHAR(1000)
	
        SELECT
            @ParameterValue = case when @ParameterColumnName like '%'+@ParameterToFind+'%'
		then 
		replace(
			replace(
				replace (	
					substring(
							@ParameterColumnName -- string
							,charindex(@ParameterToFind,@ParameterColumnName)+len(@ParameterToFind) --starting position
							, 	case
									when charindex('&'	, @ParameterColumnName, charindex(@ParameterToFind,@ParameterColumnName)+len(@ParameterToFind))=0 --If an "&" is not found then use the end of the string for the ending position.
									then len( cast(@ParameterColumnName as varchar(8000))) - (charindex(@ParameterToFind,@ParameterColumnName)+len(@ParameterToFind))+1
									else  (charindex('&'	, @ParameterColumnName, charindex(@ParameterToFind,@ParameterColumnName)+len(@ParameterToFind))) 
											- (charindex(@ParameterToFind,@ParameterColumnName)+len(@ParameterToFind))
								end
							)
					, '%2F'
					, '/'
				)
			, '%20'
			, ' '
			)
		, '%3A'
		, ':'
		)
		else null
		end 

        RETURN @ParameterValue 


    END 

GO