how do i get report on reports

Question

Post reply

how do i get report on reports

phani kumar gotety

Ten Centuries

Points: 1213
More actions
May 18, 2007 at 9:48 am

#172514

Hi,
I am new in managing reporting services. I would like to create a report which can show "which reports are scheduled at what time in a given day and how many sent fine and how many failed".
Thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

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

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

I do something like this right now. You're going to want to query the following tables in your ReportServer database:

dbo.Catalog (contains report names and locations, as well as data sources, etc)

dbo.Subscriptions

dbo.ExecutionLog

You can build some nice reports off of those.

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 2

There is a utility you can setup which reads RS Execution Logs and it comes with some predefined reports. Google it and I'm sure you'll find it...

I also have this query below which I use for this which uses the tables in the ReportServer database.

/**************************************/

select

c.Path 'ReportPath',

c.Name 'ReportName',

j.name 'JobName',

s.NextRunTime 'ScheduleNextRunTime' ,

s.LastRunTime 'ScheduleLastRunTime',

j.enabled,

u.name 'Owner',

j.date_created 'JobCreated',

j.date_modified 'JobModified',

j.version_number 'JobVersion',

su.LastStatus

from

msdb..sysjobs_view j with (nolock)

inner join ReportServer..Schedule s with (nolock) on

cast(s.ScheduleID as varchar(256)) = j.name

inner join ReportServer..ReportSchedule rs with (nolock) on

rs.ScheduleID = s.ScheduleID

inner join ReportServer..Catalog c with (nolock) on

c.ItemID = rs.ReportID

inner join ReportServer..DataSource ds with (nolock) on

ds.ItemID = c.ItemID

left join sysusers u with (nolock) on

u.sid = j.owner_sid

left join ReportServer..Subscriptions su with (nolock) on

su.Report_OID = rs.ReportID

where

j.description like '%Reporting Services%'

order by ReportNAme

/**************************************/

Cheers,

Ben

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

Tim Pain Ten Centuries Points: 1136 More actions · Answer 3

See http://www.databasejournal.com/features/mssql/article.php/3459461 and http://www.databasejournal.com/features/mssql/article.php/3483751 for a tutorial on how to set up the RS Execution Logs that Ben refers to.

Rgds

Tim

phani kumar gotety Ten Centuries Points: 1213 More actions · Answer 4

Thank you all for the responses. after going through all your responses I am able to setup the reports I wanted to.

Thanks all again