Try MySite.GetContent() Catch(Useless Info) Finally Site.Close() End Try

The guide to programming and analyzing SQL, .NET, and SAP
posts - 18, comments - 280, trackbacks - 0

My Links

News

Hello World. I'm Jon Hermiz and am a software engineer here in Michigan. Feel free to look around and leave feedback!

Archives

Post Categories

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!

Print | posted on Tuesday, August 14, 2007 3:02 PM | Filed Under [ SQL Server Reporting Services ]

Feedback

Gravatar

# 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
3/5/2008 12:33 PM | Praveen
Gravatar

# 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.
2/5/2009 11:48 AM | Randy
Gravatar

# 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
2/5/2009 1:22 PM | Jon
Gravatar

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

Thank you the queries were helpful
4/1/2009 3:16 PM | mahi
Gravatar

# 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.
7/16/2009 10:39 AM | Sonya
Gravatar

# 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.
7/21/2009 4:54 AM | Sachin
Gravatar

# 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.
8/18/2009 12:05 PM | Randy
Gravatar

# 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
9/24/2009 3:49 AM | stanja
Gravatar

# 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)
11/5/2009 4:11 PM | Tim
Gravatar

# 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?

7/13/2010 1:30 PM | Stacy
Gravatar

# 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!
8/24/2010 1:51 PM | Tacy
Gravatar

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

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.

snow boots for women | columbia sportswear outlet | cheap mac makeup | the north face jackets

womens snow boots | columbia sportswear | cheap makeup | cheap north face jackets
10/21/2010 5:39 PM | columbia jackets
Gravatar

# 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!
1/31/2011 10:50 AM | ss
Gravatar

# 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.
5/25/2011 11:13 AM | nancy
Gravatar

# 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?
6/21/2011 3:01 PM | Brady
Gravatar

# 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.
1/30/2012 6:52 AM | Apurve Mehra
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET