June 18, 2010 at 6:08 pm
Is there a way to check report execution history in SSRS? We would like to periodically retire reports that haven't been executed in 6 months or so, and I'd like to know if there was a way to view who executed a report and when.
June 19, 2010 at 10:44 am
Hi
As per my knowledge there is no direct method, but explicitly you can do it by:
1. Create one general SP,say sp_ReportHeistory
2. For each repot use this SP as the Dataset so that this sp will take the parameter as 'exec sp_for_particular_report @parameter1,@paramter2,... '
3. In side sp_ReportHeistory write the logic to trace the history like whether it is executed first time or not...
The logic you can apply as requirement.
I just gave my suggestion, there can be some other method also.. 🙂
Thanks & Regards,
MC
June 19, 2010 at 12:16 pm
Yes there's an history reporting built in but ou must activate it first.
ssms
connect to reporting engine
righ click on the report server name
properties
logging.
check activate. If you chose not to delete the hostory after x days the db will grow by maybe a couble GB per year depending on your usage.
the reporting table has tons of valuable info (especially for performance)
those tables are kept in the reportserver database which you can access by connecting to the database engine of the same server.
in sql 2008 the table is named [ExecutionLogStorage], but I'm pretty sure it's different in 2005
June 19, 2010 at 12:18 pm
only4mithunc (6/19/2010)
HiAs per my knowledge there is no direct method, but explicitly you can do it by:
1. Create one general SP,say sp_ReportHeistory
2. For each repot use this SP as the Dataset so that this sp will take the parameter as 'exec sp_for_particular_report @parameter1,@paramter2,... '
3. In side sp_ReportHeistory write the logic to trace the history like whether it is executed first time or not...
The logic you can apply as requirement.
I just gave my suggestion, there can be some other method also.. 🙂
If you use the build in history you also know who used the report and what parameters they used along with rendering time of all the steps (query, html rendering. The netwrok time can be calculated by using total time - all steps time.)
It's also usefull to see who used what and what reports are the most important to tune with total rendering times.
June 19, 2010 at 12:24 pm
Thank you boss... your reply about the build in history is a new information for me.. thanks a lot...
Thanks & Regards,
MC
June 19, 2010 at 4:39 pm
Ninja's_RGR'us (6/19/2010)
Yes there's an history reporting built in but ou must activate it first.ssms
connect to reporting engine
righ click on the report server name
properties
logging.
check activate. If you chose not to delete the hostory after x days the db will grow by maybe a couble GB per year depending on your usage.
the reporting table has tons of valuable info (especially for performance)
those tables are kept in the reportserver database which you can access by connecting to the database engine of the same server.
in sql 2008 the table is named [ExecutionLogStorage], but I'm pretty sure it's different in 2005
Thanks - that is very useful information.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 22, 2010 at 1:47 pm
As a start piont:
SELECT c.name
,[UserName]
,[RequestType]
,[Format]
,[Parameters]
,[ReportAction]
,[TimeStart]
,[TimeEnd]
,[TimeDataRetrieval]
,[TimeProcessing]
,[TimeRendering]
,[Source]
,[Status]
,[ByteCount]
,[RowCount]
,[AdditionalInfo]
FROM [ReportServer].[dbo].[ExecutionLogStorage] e
INNER JOIN dbo.CATALOG c
ON e.ReportID=c.itemID
June 23, 2010 at 12:08 am
Thanks Ninja_RGR and POCTOB for the great info! I am able to see the report execution info now.
Next up on my wishlist is knowing the identity of the person that executed the report. Field UserName in table ExecutionLog only has one value though. I asked a DBA at my company about that and he replied that all execution is done under system user context since we do not want users having direct permissions on DB server.
Is there a way to not give direct permissions, but still know who executed the report? I'm a developer, so I'm not that familiar with the configuration of RS.
June 23, 2010 at 4:13 am
I've never done that, but my first instinct would be to add an extra parameter in the report (hiden maybe) and to find a way to set that report to the machine id. Maybe it's possible to make a datasource that selects user_name from sql server but I'm not sure this would work.
Also this assume you use the standard web interface.
Could that help you?
June 23, 2010 at 12:44 pm
Yes, we're using the standard web interface for RS. Actually, we're looking to replace a web based reporting front end developed in house years go, and we're looking at RS in a dev environment to see what it can and can't do.
We have hundreds of reports, so ideally I'd like to not have to modify every one by adding a parameter. Also, if we did that, we'd have to store that data in a table, and then find a way to join to the RS execution log data.
June 23, 2010 at 4:13 pm
Not quite true, if you add the parameter, then it's saved in the execution log already. So nothing more to do there.
As for the parameter it's pretty simple. Do the job once. Then open the report in source code mode.
Lookup for the extra parameter's xml, then simply copy and paste 100 times. Redeploy everything once and you're done.
I can't think of any solution other than moving everything to let's say asp.net where you could do that whois job only once, but then again you have to reinclude all the reports somehow which is still the same pita.
Hope this helps.
June 23, 2010 at 6:36 pm
That's right! All the parameters are stored in the log anyway, so I can just get the hidden one from there. Thank you very much!
June 24, 2010 at 7:52 am
FWIW - we do just that here at work, although we don't have access to the reportserver tables (working on that, politics...). We write into our own table, capturing the User!UserID, report number and timestamp every time a report is run.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply