Create a SQL Server Reporting Services (SSRS) Catalog Report in 15 Minutes

One big question I have gotten often is “I need data, what kind of reports do you have?” It is a valid question from a user, especially with a server that may gave hundreds of reports. The solution? Create a Reports Catalog Report. I have provided the simple instructions and code to create this report in 15 minutes or less.

Step 1
Create a new report using the report wizard; it’s just easier to use the wizard.

Step 2
Select your Reports server and point the data source to ReportServer database. Click next.

Step 3
Use the following Query:

Alternatively, you can click HERE to download the .sql file if you wish. Under Code for Posts the file is called Report Catalog.sql.


SELECT Name,CreationDate,ModifiedDate,ExecutionTime,
SUBSTRING(path,
1+CHARINDEX('/',path),
CASE WHEN CHARINDEX('/',PATH,2) <> 0 THEN CHARINDEX('/',path,2)-2 ELSE LEN(path) END
) AS FolderPath,

‘http://%5BYourServerName%5D/Reports/Pages/Report.aspx?ItemPath=%2f’+ SUBSTRING(PATH,2,LEN(PATH)) as ReportURL

FROM dbo.Catalog
WHERE type NOT IN (1,5)
AND path NOT IN (‘/Data Sources’,‘ ‘)
AND name NOT IN (‘Reports Catalog’)

Step 4

Select Tabular Report. Then use the following setup for the Design the Table screen.

Step 5
Press next and then select Stepped as the style and if you like click Enable Drilldown. I prefer to use this method so that the report is a little more compact when it first loads up.

Step 6
Select your theme and setup your deployment options.

Finally, name your report and click finish.

Step 7
Add report URLs to the Catalog so your users can jump to the report directly. To do this, right click on your name field and press Text Box Properties.

On the dialog that pops up go to the Action tab. Click on Go to URL and under the selection box pick the field called ReportURL.

I would recommend changing the font for this text box so that users will see that it is a URL. Pick a different color and have it underlined for example. This can be done under the font tab.

Step 8
Deploy the report and inform your users of the new catalog you have created for them.

Step 9 – Optional
Have the report run off of a snapshot which will make loading the report faster. Go to the report properties to set this up. With SSRS 2008R2 & 2012 you just click on the report name when you have it up and go to Processing Options.

For SSRS 2008 & 2005 you click on the properties tab when you have the report up and go to execution.

You can use a shared schedule or create your own for this specific report.
This is not necessary but think about two things. How many users do you have? How often to you add new reports? If you have tons of users that access the Report Catalog things will slow down. I set my report processing options for a daily refresh. If I happen to load a new report, I could always create a report snapshot on the fly afterward. So in my opinion, it seems like a better idea to run the report off the snapshot since it will not change that often and you may have many users accessing it simultaneously.

Final Report

Advertisement