Get Reporting Services Report List from Database


I recently had the need to get a list of all the reports that are on our SQL Server Reporting Services (SSRS) site. I didn’t want to have to go and type them all out or spend forever reformatting the text after copying and pasting it from the list on the site. So, I took a quick look at the database.

Here’s how to do it

  1. Open Microsoft SQL Management Studio.
  2. Connect to the server where the database resides.
  3. Open a new query window, connected to the SSRS database.
  4. Type the following in the query window.
    Select   [Name],
                  
    [Description],
                  
    SubString([Path],1,Len([Path]) (CharIndex(‘/’,Reverse([Path]))-1)) As [Path],
                  
    Case
                            
    When [Hidden] = 1
                           
    Then  ‘Yes’
                           
    Else    ‘No’
                  
    End As [Hidden]
    From    [Catalog]
    Where  [Type] = 2
    Order By SubString([Path],1,Len([Path]) (CharIndex(‘/’,Reverse([Path]))-1)),
                  
    [Name]
  5. Press F5 or click ! Execute.

This will give you a list of the report name, description, path and whether it’s hidden or not. I hope this is useful!

Advertisements

19 thoughts on “Get Reporting Services Report List from Database

  1. Hi Nathon,
    The post is really useful. I have a quick question for you , is it possible to change data source of reports using T-sql, the problem i am facing is , we have moved database to a new server. so all the reports data source should also be changed. we have around 200 reports , so it is very tidious job to change one by one. how would i change this at a single shot using any t-sql. please share if you have already done this ?

    Regards
    Chaithanya M

    1. Chaithanya,

      The data source exists in the XML of the report itself. However I also believe it exists in a table in the database as well. Things in the SSRS database are kind of strange, but I’ll try to figure out how to do this in SQL for you. It might require either using the SSRS API or parsing the XML of the report. I’ll reply again when I figure it out.

      Nathon

      1. Yes, the .rdl file is all XML. I’ve written several VB apps that use XPath to fix different things, including data sources. However, I would ask why you need to change all 200 reports when you move the database to a new server? Don’t you just need to update the server in the single Data Source that all 200 reports use?

      2. Hi DW. In this particular case we were not able to update the data source because others were using it. We needed to add a new data source and update the reports to use it, hopefully without republishing all of the reports. Thanks for the comment!

  2. Hmm it appears like your site ate my first comment (it was super long) so I
    guess I’ll just sum it up what I submitted and say, I’m thoroughly enjoying your blog.
    I as well am an aspiring blog writer but I’m still new to everything. Do you have any suggestions for first-time blog writers? I’d really appreciate
    it.

  3. Hey would you mind letting me know which web host you’re using?
    I’ve loaded your blog in 3 completely different internet browsers and I must say
    this blog loads a lot faster then most. Can you recommend a good
    internet hosting provider at a reasonable price?
    Thanks, I appreciate it!

  4. I got an error on the SubString([Path],1,Len([Path]) – (CharIndex(‘/’,Reverse([Path]))-1)) As [Path], part of the statement. It appears “path” is column in the db, so I don’t know why it would throw following error. ???
    Error follows: Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ‘–’.
    Please advise.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s