Post reply

execution logging turned on, but no new data showing up in ExecutionLog table

  • April 10, 2008 at 10:49 am

    #801092

    OK, I just solved my issue. Like I said, I thought it had to be something stupid and it was. I forgot how the reporting executionlog table was populated because I set it up so long ago. Details here:

    http://technet.microsoft.com/en-us/library/aa964131.aspx

    Basically a DTS package has to run to populate that table. I had also set up the job to run the package nightly, but this machine is ONLY used for reporting services and that job is the only job. The agent service was never set to "automatic" and therefore at some point it was turned off (possibly after a server reboot) so the execution logging job had not run in a long time. I just changed the service to automatic (always on) and manually ran the job to populate the execution logging database and everything looks great now, I have all the data that I thought was missing.

    Here's a nice little query you can use to summarize results (report hits by month):

    declare @TimeStart datetime, @TimeEnd datetime

    set @TimeStart='1/1/2008'

    set @TimeEnd='12/31/2008'

    SELECT ReportPath, Report, UserName, Count(*) Hits, Month, MonthNum FROM (

    SELECT reports.path ReportPath, reports.[Name] Report, u.UserName, ExecutionLogs.TimeStart [Date], convert(varchar(30), datename(month, ExecutionLogs.TimeStart) ) Month,datepart(month, ExecutionLogs.TimeStart) MonthNum

    FROM ExecutionLogs

    join reports ON reports.ReportKey = ExecutionLogs.ReportKey

    join users u on ExecutionLogs.userkey = u.userkey

    WHERE ExecutionLogs.RequestType = 0 AND

    TimeStart Between @TimeStart AND @TimeEND

    --and reports.path like '/cms drug%'

    )x

    Group BY reportpath,Report, UserName, Month, MonthNum

    order by MonthNum ASC,report

Viewing post 1 (of 2 total)

You must be logged in to reply to this topic. Login to reply