Automating SSRS favorites with REST API

This information applies to SQL Server Reporting Services 2017 and later.

As more and more organizations rely on visualization and reporting to get the information they need, more and more reports come into play. With Configuration Manager 1710 you get almost 500 reports where only a subset are relevant to you and your team. This blog post will show you how you can automate the use of favorites in SQL Reporting Services 2017.

Prior to SSRS 2017 you had to find the report and mark it as a favorite. Beginning from SSRS 2017 we can now use a combination of REST API and PowerShell to automate the process and configure SSRS favorites for our colleagues.

First, we need to define some variables, here we need to specify the reports we want configured as favorites and the SSRS server.

Next step is to get a list of catalogitems as we need the id property to specify the report as favorite. We also get a list of already configured favorites.

Now let’s configure the favorites. First, we need the CatalogItem for the specific report.

Second, we check if the report is present within SSRS and not already defined as favorite, then set the CatalogItem Id, convert to Json and populate the report as favorite

 

Let’s put it all together, and create a shortcut on the desktop.

Now simply deploy the script. I tend to deploy similar scripts to Helpdesk staff and members of my Reporting User groups. Just remember to do a user deployment and that the user need to have access to SSRS in the first place.

Final result, your common used reports configured as favorites and easy to locate.

Happy reporting!

About the Author:

Per Borreskov Weimann
Enterprise Client Management Senior Consultant, with focus on Configuration Manager and Windows 10

6 Comments

  1. Solutions January 19, 2018 at 22:02 - Reply

    Great post Borreskov. Congratulation!

  2. Laptop Brands in india February 14, 2018 at 11:16 - Reply

    Interesting, support you hardwork

  3. Dolly Singh March 17, 2018 at 5:22 - Reply

    Thank you for sharing. <3

  4. Adam April 22, 2018 at 22:17 - Reply

    Thank you for sharing, can you hide the “Browse” in some way…? I do not want the user to browse… only see the favorites reports.

  5. architect tunisie June 30, 2018 at 8:34 - Reply

    Bonjour,
    merci beaucoup pour ce site ,il est magnifique

Leave A Comment