Split parameters from ExecutionLog in SSRS (String Functions)

Question

"Yet another string function" question for TSQL.

The ExecutionLogStorage table in the ReportServer$InstanceName database for SSRS 2008 puts all parameters called to the report in a single column -

FullName=LastName, FirstName&CalledBy=Lastname, FirstName&DateSelected=MM/DD/YY&CheeseorFries=Fries

Some are null values, so the user could just have DateSelect or FullName, or CalledBy... etc.I need to be able to parse out the 'CalledBy=Lastname, FirstName' value; but between the LOWER and LTRIM and Lefts and Substrings and Charindexes.... I'm not getting anywhere.

Any ideas? =/

The intent is to be able to retrieve a list of who called which report at which time....

The SSRS execution log has a username column, but due to the reports being called from a DNN portal, using linked servers, the managers decided the whole network-delegation thing was easier bypassed by just using a domain service account - so all 'username' fields are filled by the service account instead of the individual.

thank you!

C Vand C Vand · Accepted Answer · 2010-11-05T15:22:15

    SELECT  
RTrim(Substring(ex.Parameters, Charindex('&CalledBy=',ex.Parameters, 1) + 10, 1000)) 
as 'User',
ex.Format, ex.TimeStart, cat.Name as 'Report', 
CONVERT(varchar(max), ex.TimeStart, 100) AS rundate
FROM ReportServer$MCSQL1.dbo.ExecutionLog AS ex, ReportServer$MCSQL1.dbo.Catalog AS cat
where ex.ReportID = cat.ItemID

Not sure if it works in all instances. Basically, all reports ran from the DNN website are ran by an execution account, so the 'user' field of the execution report was useless. Instead, we created a CalledBy parameter & feed it the full name from the profile of the employee that pulled up the report in DNN.

Uh, also, I think I'm breaking some sort of programming rule by counting on the fact that 'CalledBy' will always be the last parameter in the string ('1000')..... but we're documenting it, so future people can follow along.

The rtrim(substring... code above has appropriately pulled up the name of the individual calling the report since the parameter was put into place several months ago. (invisible field at bottom right of the report)

Hope this helps!

Split parameters from ExecutionLog in SSRS (String Functions)

1 Answers