List All Your SSRS Reports—With a SSRS Report

Reports have a habit of multiplying like crazy, no matter what reporting tool you use. You’re soon left to wonder, “Where did I put that report?” or, if you’re working with Dynamics GP, “What reports can I get out of this thing?” To help you answer these questions, I’m going to do three things in this post:

• Show a basic query of the Catalog table to get report and folder information right from SSRS.
• Use the data in the SSRS report to build a nice list of all stored reports, starting with a beginning base folder.
• Provide a link to an Excel sheet for Dynamics GP folks who just want a listing of the reports.

Please note: This isn’t an ideal solution—at least not yet. In the report, I can only select at the top of the hierarchy. I have to perform more coding in T-SQL to select lower levels. A more elegant solution would use a recursive query with T-SQL, and I’m working on that.

The Catalog table is stored in the ReportServer database. On many systems, the database will be named ReportServer (or something similar), but you’ll have to look to find out. Two things to note:

• Microsoft says you should use the Web Services APIs for SSRS to access this information. They don’t guarantee that queries working over the database directly will work. (You’ve been warned.)
• There are many solid posts available online about the SSRS ReportServer database. Scott Murray’s post on MSSQLTips.com is one of the most comprehensible.

That said, here’s the basic query I’m using:

I’ve only selected folders (type 1) and reports (type 2) because this listing is for end users.

I’m running this for a particular root folder, in this case “TWO” (the sample company for Dynamics GP). Of course, you can choose the folder you want or run the output for the entire catalog table.

Here’s the sample output dumped to Excel:

This is hierarchical data, so seeing only “child” and “parent” isn’t that useful, even if you sorted it. As mentioned above, you can create a recursive SQL statement to show the full hierarchy, but I haven’t done it yet. (You can do it easily in SSRS.)

I’m not going to explain all the details of a hierarchical report here. If you’re interested, Ahmad Ahmad on Code Project provides detailed instructions. While I’m using different data, I am using the same colors as Ahmad. (Thank you!)

Here’s a sample of the output:

See how the child names are indented to reflect the different hierarchical levels. The level is displayed in column 1. I’ve also created a drop down for the root folder at the top.

If you want all these materials, follow this link on listing SSRS reports to a zip folder. I’ve included:

• An Excel Sheet with a list of all the SSRS reports I found on GP 2013.
• The query used above.
• The SSRS report. The report uses an embedded data source, which I only do when sending out samples. Data sources always need to be changed anyway.
• Instructions for changing the data source. I didn’t create the instructions specific to this example, but if you haven’t changed the data source before it should still help.

If you’re interested in this kind of content, sign up for our email newsletter. The sign up box is located to the top right of this page.