May 5, 2009 at 10:46 am
Does anybody have any experience with collecting
Reports usage statistics using Reporting Services 2005?
May 5, 2009 at 11:00 am
this should get you started:
select ExecutionLog.UserName,Catalog.Name,Catalog.Path,ExecutionLog.TimeEnd,
ExecutionLog.Parameters
from Catalog inner join ExecutionLog on
Catalog.ItemID = ReportID
where
ExecutionLog.TimeEnd between @begdate and @enddate
Order by ExecutionLog.TimeEnd
This is in the reportserver database
May 5, 2009 at 12:50 pm
Should I exclude "NT AUTHORITY\SYSTEM" records?
UserNameNamePath
-------------------------------------------------------------------------------------------
NT AUTHORITY\SYSTEMCommodities - Stress Worst Case/Market Risk/Stress Testing/Commodities - Stress Worst Case
NT AUTHORITY\SYSTEMStress Worst Case/Market Risk/Stress Testing/Stress Worst Case
NT AUTHORITY\SYSTEMStress Worst Case/Market Risk/Stress Testing/Stress Worst Case
NT AUTHORITY\SYSTEMLimit Monitoring - FLM US Non-Trading/Business Management/Funding New York/Limit Monitoring - FLM US Non-Trading
NT AUTHORITY\SYSTEMLimit Monitoring/Market Risk/Limit Monitoring/Limit Monitoring
NT AUTHORITY\SYSTEMLimit Monitoring/Market Risk/Limit Monitoring/Limit Monitoring
May 5, 2009 at 12:59 pm
Those are probably scheduled subscriptions. I happen to include them in my reports, but it's up to you. I like to provide a total number of reports ran each month for management here and I include those...
May 5, 2009 at 1:20 pm
When I click on "Preview"
it takes 30 sec to generate a report in Visual Studio.
It's only 229 records (I checked it in Query Analyzer).
Does it always take that long to generate a report the first time?
May 5, 2009 at 1:27 pm
That is strange. I can run 350 records in under 1 second with the same query. What kind of date range are you looking at? You may have some other processes eating up resources right now. ???
May 5, 2009 at 1:48 pm
In QA it takes 1 sec to get the same results.
Our Report Server is running on
Windows XP Professional SP3; HP Pentium 4 CPU 3.20 GHz; 2 GB RAM.
Can it be a problem
or maybe my Visual Studio is slow?
May 5, 2009 at 1:58 pm
It could be a lot of things. Running report services on xp pro is a bit unusual. Your production server is probably just over utilized for this query to run quickly. Start another post in the performance tuning area... http://www.sqlservercentral.com/Forums/Forum360-1.aspx
May 5, 2009 at 2:01 pm
Is there a forum on SQLServerCentral for discussions on Reporting Services ?
Or maybe there is a better place than SQLCentral?
May 5, 2009 at 2:08 pm
This is great. I'm assuming the time elements in the ExecutionLog table are in ms, is that correct? And, Where can I go to for a full explanation of all the attributes in the ExecutionLog table? Thanks.
May 5, 2009 at 2:09 pm
There are others but none as good at SQLServerCentral!
Here's the reporting services board http://www.sqlservercentral.com/Forums/Forum150-1.aspx?Update=1
Some other reporting links I use are:
http://www.sqlpass.org/LearningCenter/Top10Lists.aspx#learnrs
http://msdn.microsoft.com/en-us/library/ms170246.aspx
http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1172656,00.html#
However, the question about the speed of this query won't be answered in any of those links.
May 5, 2009 at 2:25 pm
Scott Solice (5/5/2009)
This is great. I'm assuming the time elements in the ExecutionLog table are in ms, is that correct? And, Where can I go to for a full explanation of all the attributes in the ExecutionLog table? Thanks.
Yes, milliseconds....http://msdn.microsoft.com/en-us/library/ms159110(SQL.90).aspx
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply