SSRS - Method to retrieve last run report parameters

I was going through MSBI discussion posts in Linkedin and saw a discussion regarding methods to save SSRS report parameters. Below is an efficient way of retrieving the report parameters and reuse them for subsequent executions

 

When a report is run in report manager , its parameter values will get stored in ExecutionLogStorage table of ReportServer database . This database contains lot of other tables having useful information like  report  deployed to report server ,dataset used ,data sources , execution logs , parameters used , subscriptions , users etc.,

Catalog table will have report name , reportID , report path ,Content (this column has the xml code for the rdl file)

 

Each time a report is accessed using report manager , the log information about the report will be stored in ExecutionLogStorage table. This table stores the ReportID, UserName (User who accessed this report), parameters , timestart ,timeend , rowcount and other additional information

 

To get the last run report parameters for a particular report , ExecutionLogStorage have to be queried to get the latest record for the particular report for particular user

 

Consider Store_Contacts_2008R2  report from AdventureWorks sample report for this example

The report is having Store as a parameter which is set to default as 'Advanced Bike Components '

Each time the report is accessed , the default parameter will be passed to the database to retrieve the records

In our case , the default value have to be the last executed report parameter value

Below query will always give the last passed parameter for Store_Contacts_2008R2 report

 

USE ReportServer

SELECT  c.Name

, els.ReportID, els.TimeEnd

, CAST(els.Parameters AS varchar(MAX)) AS Parameters

FROM   ExecutionLogStorage AS els

INNER JOIN [Catalog] AS c ON c.ItemID = els.ReportID

WHERE  c.Name = 'Store_Contacts_2008R2'

Note: Your ReportServer DB will vary based on the report server instance , for default report server instance the corresponding database is ReportServer

Now , parameter in the above result set have to be passed to the default values for @StoreID  and @Showall parameter in Store_Contacts_2008R2 report

 

Create a dataset StoresHistory to get the StoreID parameter value for previous execution

 

 

 

Query for StoresHistory dataset :

 

SELECT        S.BusinessEntityID, S.Name

FROM            AdventureWorks2008R2.Sales.Store AS S

INNER JOIN

(SELECT        TOP (1) StoreID AS BusinessEntityID

FROM            (

SELECT  c.Name

, els.ReportID, els.TimeEnd

, StoreID = replace(left(CAST(els.Parameters AS varchar(MAX)),charindex('ShowAll',Parameters)-2),'StoreID=','')

FROM   ReportServer.dbo.ExecutionLogStorage AS els

INNER JOIN ReportServer.dbo.[Catalog] AS c ON c.ItemID = els.ReportID

WHERE  c.Name = 'Store_Contacts_2008R2'

 )W

 order by TimeEnd desc

)Q ON S.BusinessEntityID = Q.BusinessEntityID

 

 

In Parameters section,Change the default value for @StoreID parameter to use StoresHistory dataset

                              

 

 

 

Deploy the report to report manager

 

One another thing to note before deploying to report manager is that 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

So don't wonder why the default value for the Store parameter in report preview in Visual studio is not reflecting the last report execution value

 

Try accessing the report in report server , and pass a different parameter value other than the default value

 

 

 

 

 

Run the query in StoresHistory dataset , the last used report parameter will be displayed for Store

When the report is accessed next time , this value will be passed as default value for the Store_Contacts_2008R2 report

 

If the report is having multiple parameters , it is necessary to create a history dataset for each parameter

For multiple business user per prompt report , pass the username (from ExecutionLog table) as a parameter to the history dataset to get the parameter for respective user