Anyone have reports on Reports? (Things that go beyond the samples

Hi all,
I'd like to get a report on the reports that are deployed.
It would tell me things like this:
Report Name
DataSourceName
Connection String
Stored Procedures Called
Views/Tables Used (Probably can't be
shown if its calling a stored procedure)
Modified date
Modified Last by
Folder it is stored under in Report
ManagerFolder it is stored under in Report Manager (this is in the samples)
Last Run By
Last Run Date
Can this be done? Does anyone have it?
Are there any user created updates or changes to the samples that MSFT has
written?
KeithOn Jul 25, 1:32 pm, greenmtnsun
<greenmtn...@.discussions.microsoft.com> wrote:
> Hi all,
> I'd like to get a report on the reports that are deployed.
> It would tell me things like this:
> Report Name
> DataSourceName
> Connection String
> Stored Procedures Called
> Views/Tables Used (Probably can't be
> shown if its calling a stored procedure)
> Modified date
> Modified Last by
> Folder it is stored under in Report
> ManagerFolder it is stored under in Report Manager (this is in the samples)
> Last Run By
> Last Run Date
> Can this be done? Does anyone have it?
> Are there any user created updates or changes to the samples that MSFT has
> written?
> Keith
There are two useful tables under a database named
"ReportServer" (this is where the report server stuff is stored, like
history). Catalog and ExecutionLog are the tables and they can be
joined at reportid (execution log) and itemid (catalog). That way you
can pull the names of the report users, reports, and other fun stuff.
There are also some other valuable tables in that database, but I
don't use them that much.|||You are not supposed to access reporting services' database directly
(although you can with appropriate permission). Reporting services provides
web services to get the information the OP wants. reporting services' report
mamaner is an ggod example of an application consuming the web services to
get that kind of information. I believe the OP does not need to know more
information that report manager can show.
"Ayman" <aymantg@.gmail.com> wrote in message
news:1185386825.627743.70110@.q75g2000hsh.googlegroups.com...
> On Jul 25, 1:32 pm, greenmtnsun
> <greenmtn...@.discussions.microsoft.com> wrote:
>> Hi all,
>> I'd like to get a report on the reports that are deployed.
>> It would tell me things like this:
>> Report Name
>> DataSourceName
>> Connection String
>> Stored Procedures Called
>> Views/Tables Used (Probably can't be
>> shown if its calling a stored procedure)
>> Modified date
>> Modified Last by
>> Folder it is stored under in Report
>> ManagerFolder it is stored under in Report Manager (this is in the
>> samples)
>> Last Run By
>> Last Run Date
>> Can this be done? Does anyone have it?
>> Are there any user created updates or changes to the samples that MSFT
>> has
>> written?
>> Keith
> There are two useful tables under a database named
> "ReportServer" (this is where the report server stuff is stored, like
> history). Catalog and ExecutionLog are the tables and they can be
> joined at reportid (execution log) and itemid (catalog). That way you
> can pull the names of the report users, reports, and other fun stuff.
> There are also some other valuable tables in that database, but I
> don't use them that much.
>|||On Jul 25, 1:32 pm, greenmtnsun
<greenmtn...@.discussions.microsoft.com> wrote:
> Hi all,
> I'd like to get a report on the reports that are deployed.
> It would tell me things like this:
> Report Name
> DataSourceName
> Connection String
> Stored Procedures Called
> Views/Tables Used (Probably can't be
> shown if its calling a stored procedure)
> Modified date
> Modified Last by
> Folder it is stored under in Report
> ManagerFolder it is stored under in Report Manager (this is in the samples)
> Last Run By
> Last Run Date
> Can this be done? Does anyone have it?
> Are there any user created updates or changes to the samples that MSFT has
> written?
> Keith
If you have access to those databases, you can make your own custom
reports to show whatever you want. I have one set up to show
executions per user and which reports they executed. Others include,
most recent executions, total executions per report, etc. There is
much you can do in terms of custom reporting if you use the
ReportServer database that is created by default by SSRS.|||I have all the permissions I could ever need. If not, I'll give me them! :-)
Could you by chance share these reports you spoke of? My e-mail address is
keith underscore m underscore ramsey at yahoo dot com
I don't know if spam bots can get this address so I messed with it. _ is an
underscore. LOL.
I'd really appreciate it. They can show me how to get other peices of
information.
One thing I got frusted about was that at first blush I couldn't get an ID
number for a report to link from the RSExecutionLog to the RS databases. I
think the RSExecutionLog uses its own ID system. I hope I'm wrong. I also
don't know how they break out the connection string from things that are
encrypted.
"Ayman" wrote:
> On Jul 25, 1:32 pm, greenmtnsun
> <greenmtn...@.discussions.microsoft.com> wrote:
> > Hi all,
> >
> > I'd like to get a report on the reports that are deployed.
> >
> > It would tell me things like this:
> >
> > Report Name
> >
> > DataSourceName
> >
> > Connection String
> >
> > Stored Procedures Called
> >
> > Views/Tables Used (Probably can't be
> > shown if its calling a stored procedure)
> >
> > Modified date
> >
> > Modified Last by
> >
> > Folder it is stored under in Report
> > ManagerFolder it is stored under in Report Manager (this is in the samples)
> >
> > Last Run By
> >
> > Last Run Date
> >
> > Can this be done? Does anyone have it?
> >
> > Are there any user created updates or changes to the samples that MSFT has
> > written?
> >
> > Keith
> If you have access to those databases, you can make your own custom
> reports to show whatever you want. I have one set up to show
> executions per user and which reports they executed. Others include,
> most recent executions, total executions per report, etc. There is
> much you can do in terms of custom reporting if you use the
> ReportServer database that is created by default by SSRS.
>|||On Jul 26, 11:54 am, greenmtnsun
<greenmtn...@.discussions.microsoft.com> wrote:
> I have all the permissions I could ever need. If not, I'll give me them! :-)
> Could you by chance share these reports you spoke of? My e-mail address is
> keith underscore m underscore ramsey at yahoo dot com
> I don't know if spam bots can get this address so I messed with it. _ is an
> underscore. LOL.
> I'd really appreciate it. They can show me how to get other peices of
> information.
> One thing I got frusted about was that at first blush I couldn't get an ID
> number for a report to link from the RSExecutionLog to the RS databases. I
> think the RSExecutionLog uses its own ID system. I hope I'm wrong. I also
> don't know how they break out the connection string from things that are
> encrypted."Ayman" wrote:
> > On Jul 25, 1:32 pm, greenmtnsun
> > <greenmtn...@.discussions.microsoft.com> wrote:
> > > Hi all,
> > > I'd like to get a report on the reports that are deployed.
> > > It would tell me things like this:
> > > Report Name
> > > DataSourceName
> > > Connection String
> > > Stored Procedures Called
> > > Views/Tables Used (Probably can't be
> > > shown if its calling a stored procedure)
> > > Modified date
> > > Modified Last by
> > > Folder it is stored under in Report
> > > ManagerFolder it is stored under in Report Manager (this is in the samples)
> > > Last Run By
> > > Last Run Date
> > > Can this be done? Does anyone have it?
> > > Are there any user created updates or changes to the samples that MSFT has
> > > written?
> > > Keith
> > If you have access to those databases, you can make your own custom
> > reports to show whatever you want. I have one set up to show
> > executions per user and which reports they executed. Others include,
> > most recent executions, total executions per report, etc. There is
> > much you can do in terms of custom reporting if you use the
> > ReportServer database that is created by default by SSRS.
Here are the generic ones you can use. I posted them here to everyone
will benefit.
--Detailed
select el.username,c.name,el.timestart,el.timeend
executionlog el join
catalog c on el.reportid=c.itemid
order by timestart desc
--Reports used by user
select el.username,c.name, count(1)
from executionlog el join
catalog c on el.reportid=c.itemid
group by el.username,c.name
order by el.username,c.name
--Usage by Report
select c.name, count(1)
from executionlog el join
catalog c on el.reportid=c.itemid
group by c.name
order by count(1) desc
--Recent usages per user per report
select el.username,c.name,el.timestart,el.timeend
from executionlog el join
catalog c on el.reportid=c.itemid
where username =''INSERT IT HERE'
order by timestart desc
Hope that helps! You can put them in a report if you don't like to
see them in SSMS. By the way, it's a good way to track who has been
using the reports you developed for them! You last name sounds
Egyptian by the way.|||Thank you.
For the record, I am still looking for these to place in a report about
reports...
Connection String
Stored Procedures Called
Views/Tables Used (Probably can't be
shown if its calling a stored procedure)
Modified date
Modified Last by
"Ayman" wrote:
> On Jul 26, 11:54 am, greenmtnsun
> <greenmtn...@.discussions.microsoft.com> wrote:
> > I have all the permissions I could ever need. If not, I'll give me them! :-)
> >
> > Could you by chance share these reports you spoke of? My e-mail address is
> > keith underscore m underscore ramsey at yahoo dot com
> >
> > I don't know if spam bots can get this address so I messed with it. _ is an
> > underscore. LOL.
> >
> > I'd really appreciate it. They can show me how to get other peices of
> > information.
> >
> > One thing I got frusted about was that at first blush I couldn't get an ID
> > number for a report to link from the RSExecutionLog to the RS databases. I
> > think the RSExecutionLog uses its own ID system. I hope I'm wrong. I also
> > don't know how they break out the connection string from things that are
> > encrypted."Ayman" wrote:
> > > On Jul 25, 1:32 pm, greenmtnsun
> > > <greenmtn...@.discussions.microsoft.com> wrote:
> > > > Hi all,
> >
> > > > I'd like to get a report on the reports that are deployed.
> >
> > > > It would tell me things like this:
> >
> > > > Report Name
> >
> > > > DataSourceName
> >
> > > > Connection String
> >
> > > > Stored Procedures Called
> >
> > > > Views/Tables Used (Probably can't be
> > > > shown if its calling a stored procedure)
> >
> > > > Modified date
> >
> > > > Modified Last by
> >
> > > > Folder it is stored under in Report
> > > > ManagerFolder it is stored under in Report Manager (this is in the samples)
> >
> > > > Last Run By
> >
> > > > Last Run Date
> >
> > > > Can this be done? Does anyone have it?
> >
> > > > Are there any user created updates or changes to the samples that MSFT has
> > > > written?
> >
> > > > Keith
> >
> > > If you have access to those databases, you can make your own custom
> > > reports to show whatever you want. I have one set up to show
> > > executions per user and which reports they executed. Others include,
> > > most recent executions, total executions per report, etc. There is
> > > much you can do in terms of custom reporting if you use the
> > > ReportServer database that is created by default by SSRS.
> Here are the generic ones you can use. I posted them here to everyone
> will benefit.
> --Detailed
> select el.username,c.name,el.timestart,el.timeend
> executionlog el join
> catalog c on el.reportid=c.itemid
> order by timestart desc
> --Reports used by user
> select el.username,c.name, count(1)
> from executionlog el join
> catalog c on el.reportid=c.itemid
> group by el.username,c.name
> order by el.username,c.name
> --Usage by Report
> select c.name, count(1)
> from executionlog el join
> catalog c on el.reportid=c.itemid
> group by c.name
> order by count(1) desc
> --Recent usages per user per report
> select el.username,c.name,el.timestart,el.timeend
> from executionlog el join
> catalog c on el.reportid=c.itemid
> where username =''INSERT IT HERE'
> order by timestart desc
>
> Hope that helps! You can put them in a report if you don't like to
> see them in SSMS. By the way, it's a good way to track who has been
> using the reports you developed for them! You last name sounds
> Egyptian by the way.
>|||Since we're already into things you aren't supposed to do -
If you have a lot of reports and report execution records, look at the
indexing on the execution log file - by default, I don't think the
report ID is indexed (which seems odd, since it acts as a foreign key
to the catalog table...)