Jon Hermiz Blog

The guide to programming and analyzing SQL, .NET, and SAP

Power of the ReportServer - How to pull data from Reporting Services Database

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!

Legacy Comments


Praveen
2008-03-05
re: Power of the ReportServer - How to pull data from Reporting Services Database
Is it possible to get subscription query from the database of any particular report

Randy
2009-02-05
re: Power of the ReportServer - How to pull data from Reporting Services Database
Is there a way to find which UNIQUE GUI ID goes with the appropriate report? Under the SQL Agent Jobs it only displays the UNIQUE GUI ID and its hard to know which report it goes with considering that I have 100+ jobs.

Jon
2009-02-05
re: Power of the ReportServer - How to pull data from Reporting Services Database
Randy certainly.

The three tables you need to look at are Schedule, Report Schedule, and Catalog.
Schedule lists the schedules (jobs in your sql server agent that have that GUID that you speak of).
Report schedule links to the schedule table and references a field called ReportID (that is the foreign key to the Catalog table). The ReportID from the report schedule table joins to the catalog table which will give you the name of the report.

Here is a sample query:
SELECT dbo.Schedule.ScheduleID, dbo.[Catalog].ItemID, dbo.[Catalog].Name
FROM dbo.Schedule INNER JOIN
dbo.ReportSchedule ON dbo.Schedule.ScheduleID = dbo.ReportSchedule.ScheduleID INNER JOIN
dbo.[Catalog] ON dbo.ReportSchedule.ReportID = dbo.[Catalog].ItemID

mahi
2009-04-01
re: Power of the ReportServer - How to pull data from Reporting Services Database
Thank you the queries were helpful

Sonya
2009-07-16
re: Power of the ReportServer - How to pull data from Reporting Services Database
I want to pull all the users, their assigned role, folder and report access, and path from the database. Then I will turn this into a report to help track user permission, access and all and folders and reports access. Then take this and automate how we maintain report access and add role assignments.

Sachin
2009-07-21
re: Power of the ReportServer - How to pull data from Reporting Services Database
How to find the history report details using execution log. How to relate the history table with ExecutionLog view.

Randy
2009-08-18
re: Power of the ReportServer - How to pull data from Reporting Services Database
I'm am trying to extract the email recipients of report subscriptions. To do this I'm getting the recipients from dbo.Subscriptions.Description but the recipients list cuts off the email addresses as if it is limited in character length. The email addresses do not exceed the character length that is designated for this column.

stanja
2009-09-24
re: Power of the ReportServer - How to pull data from Reporting Services Database
Hi ,

Is there a possibilty to qry the security of each report.
I want to see for a particular user which credentials he has on which reports in order to change/delete these by using a qry instead of open 1 by 1 the security at the reporting site.

tx
regards
Steven

Tim
2009-11-05
re: Power of the ReportServer - How to pull data from Reporting Services Database
I just wrote a query to pull the email recipients for each of our reports in RS. To do it you need to xquery the column "extensionsettings" in the subscriptions column. Here is an example, hope it helps:

SELECT

replace(replace(CAST(CAST(extensionsettings as XML).query('/ParameterValues/ParameterValue/Value[../Name = ''TO'']') as varchar(1000)), '</Value>', ''), '<Value>', '') + '; '

FROM
ReportServer.dbo.Subscriptions subs with(nolock)

Stacy
2010-07-13
re: Power of the ReportServer - How to pull data from Reporting Services Database
We have a website using a report viewer control to run the reports on the report server.
Now we know the user on the website from Activedirectory.
Since to run the reports from web site we have a generic account, under which all the reports run, all I can see is that ReportUser in ExecutionLog table.
is there a way we could get the name of the Actual user on the web site running the report?


Tacy
2010-08-24
re: Power of the ReportServer - How to pull data from Reporting Services Database
This article was excellent! With a few minor changes and some meshing of queries on this page I was able to get exactly what I was looking for. Thank you so much!

ss
2011-01-31
re: Power of the ReportServer - How to pull data from Reporting Services Database
We have set up a web site (intranet) to execute the reports for users. Now the website uses a report viewer control. The name of the server and path where the report is residing is passed to the report viewer and the default credentials are used. So when the report is executed, the UserName that gets entered into the ExecutionLog is the Web server name and not the actual user. Is there a way to capture the actual UserName and pass the informaion to ExecutionLog? Please suggest!

nancy
2011-05-25
re: Power of the ReportServer - How to pull data from Reporting Services Database
Thank you all. This is great information. One more thing....
We need to retrieve some lost SQL statements that were used in the query portion of the of a Data Driven Subscription. Does anyone know where that information is stored in the repository? And how to query it.

Brady
2011-06-21
re: Power of the ReportServer - How to pull data from Reporting Services Database
great article!

I created a simular report but now what I am wanting is a report which shows me how many times a field is used in all of my reports.

So if we are going to make changes to field 'widgetColor'.
I would like to be able to run a report and see how many reports the field 'widgetColor' is used in.

Is this possible?

Apurve Mehra
2012-01-30
re: Power of the ReportServer - How to pull data from Reporting Services Database
I've managed to get it all working.I eventually twigged that security is actually two-fold:reports having permission to access their data source and users having permissions to view reports from then on it was handy enough to set up.