How to find query/stored procedure used in an SSRS report

If yor are troubleshooting an SSRS report which you have not created by yourself. Finding it’s location may take sometime even though you know that issue is with the query. Hence to find the query/stored procs used in the report we can directly query ReportServer database and get those details from it’s report dataset.

SELECT Name As ReportName,
FROM ReportServer.dbo.Catalog
where Type = 2 
order by Name

it will give an XML result which will look like this.

    <DataSet Name="EmployeeDetails">
                <QueryParameter Name="@EMPLOYEEID">
    <DataSet Name="Department">
                SELECT DepartmentId AS ID, DeparmentName 
                FROM Department               

Query tag will tel you about the sql query and stored proc used in the reports. You can start troubleshooting right away if it’s a data or a query issue.

I hope it helps!


About saxenapraveen

I have 14 years of experience in enterprise software development,designing and leading team to provide reliable and scalable solutions for cloud and device ready businesses.

Posted on April 4, 2013, in Sql Server, SSRS and tagged , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: