A lot of programmer’s / dba’s install RS and then simply use it to build reports for other databases. In fact many do not even realize that the ReportServer database that is installed on the SQL box has some nifty tables to snatch some relevant information. These 2 examples that I’ll go over I’ve posted in the past on SQLTeam here but I decided it’s probably best to place it on my blog so that others can easily get to it.
So our first example involves the Catalog and ExecutionLog tables in the ReportServer database. Let me give you the scenerio I ran into at work and the reason why I wrote this little nifty query. The manufacturing company I work for Johann A. Krause, Inc., a division of ThyssenKrupp, generally go through a lot of meetings to get a product out to one of our many customers. With meetings comes reports, this can include action item reports, issue reports, resolutions, and historical data. Well a lot of the time an employee may come into the meeting being unprepared, so we needed a way to track down whether employees were running specific reports or not. At first I thought to myself I could include some sort of INSERT mechanism right into the application itself when the user generated a report, but that seemed like too much. I dug around the ReportServer database and found exactly what I needed. All I had to do was make sense of it, perform a join, and voila a query that returns the user, date and time, and the report name of any report that came out of our rs box.
Here’s the query:
USE ReportServer
GO
SELECT
c1.Name AS ReportName,
e1.UserName AS Employee,
e1.Format, –what did they export the report to (optional)
e1.Parameters, –did they query the result set (optional)
e1.TimeEnd –when did they run it ?
FROM
[Catalog] c1
INNER JOIN
ExecutionLog e1 ON
e1.ReportID = c1.ItemID
ORDER BY c1.Name
GO
That should give you a listing of the employees who ran a report and what report they ran. The next example touches a bit on subscriptions in Reporting Services. Subscriptions in Reporting Services are simply Jobs in SQL Server (if you look in the Jobs section in SQL Server you will notice a UNIQUE GUI ID LIKE 000123-303204-9348-24024045032450, which is simply a subscription). That actual subscription is stored in the ReportServer database in the Catalog and Subscriptions table. So the reason behind this query is to avoid having to find out what subscriptions are going out of your RS box from Report Manager. A lot of times you want to find out what is going on, due to performance issues, on your RS box. So rather than dig around and find out what kind of subscriptions are going out you can simply write a query to display those subscriptions.
USE ReportServer
GO
SELECT
u.UserName AS TheUser, s.*, –you should list out the specific columns you need here and not use ‘*’
c.Name AS TheReport
FROM
[Catalog] c
INNER JOIN
Subscriptions s ON
s.Report_OID = c.ItemID
INNER JOIN
Users u ON u.UserID = s.OwnerID
ORDER BY c.Name
That should get you a listing of all subscriptions on your RS box. Hope this helps!