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.

$ReportList = @(
    "Coretech - Software Update Group"
    "CTGlobal - App Crash"
    "CTGlobal - Blue Screen"
    "CTGlobal - GPO Processing"
    "BitLocker Enterprise Compliance Dashboard"
    "Enterprise Compliance Report"
    "Scan 2 - Last scan states by site"
    "Troubleshooting 1 - Scan errors"
    "Computers for a specific user name"
    "Hardware 09A - Search for computers"
)

$webServiceUrl = "http://CMTP001"

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.

#Gets an array of CatalogItems
$CatalogItems = Invoke-RestMethod -Uri $webServiceUrl'/reports/api/v2.0/CatalogItems' -ContentType 'application/json' -UseDefaultCredentials -Method Get

#Retrieves a collection of items of type CatalogItem which have been designated as favorites
$FavoritesItems = Invoke-RestMethod -Uri $webServiceUrl'/reports/api/v2.0/FavoriteItems' -ContentType 'application/json' -UseDefaultCredentials -Method Get

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

#Get specifict CatalogItem
$CatalogItem = $CatalogItems.value | Where-Object Name -eq $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

#Check if CatalogItem is received or report is allready favorite
If ($CatalogItem -and !($FavoritesItems.value -match $CatalogItem.id)){
 $Item = @{
 Id = $CatalogItem.Id
 }
 $Body = $Item | ConvertTo-Json
 
 #Designate a CatalogItem as a favorite
 Invoke-RestMethod -Uri $webServiceUrl'/reports/api/v2.0/FavoriteItems' -UseDefaultCredentials -ContentType 'application/json' -Method Post -Body $Body | Out-Null
}

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

#Requires -version 3.0
# //***************************************************************************
# // Solution:  Configure SSRS Favorites
# // Author:	Per Borreskov Weimann, CTGlobal. http://www.ctglobalservices.com
# // Purpose:   Add reports to favorites in SSRS and add a shortcut on the desktop
# // Note:      Requries SSRS 2017 or above
# //***************************************************************************


#//----------------------------------------------------------------------------
#//  Variable declarations
#//----------------------------------------------------------------------------

$ReportList = @(
    "Coretech - Software Update Group"
    "CTGlobal - App Crash"
    "CTGlobal - Blue Screen"
    "CTGlobal - GPO Processing"
    "BitLocker Enterprise Compliance Dashboard"
    "Enterprise Compliance Report"
    "Scan 2 - Last scan states by site"
    "Troubleshooting 1 - Scan errors"
    "Computers for a specific user name"
    "Hardware 09A - Search for computers"
)
$webServiceUrl = "http://CMTP001"

$ShortcutUrl = $webServiceUrl+"/reports"


#//----------------------------------------------------------------------------
#//  Main routines
#//----------------------------------------------------------------------------

#Gets an array of CatalogItems
$CatalogItems = Invoke-RestMethod -Uri $webServiceUrl'/reports/api/v2.0/CatalogItems' -ContentType 'application/json' -UseDefaultCredentials -Method Get

#Retrieves a collection of items of type CatalogItem which have been designated as favorites
$FavoritesItems = Invoke-RestMethod -Uri $webServiceUrl'/reports/api/v2.0/FavoriteItems' -ContentType 'application/json' -UseDefaultCredentials -Method Get

Foreach ($Report in $ReportList){
    #Get specifict CatalogItem
    $CatalogItem = $CatalogItems.value | Where-Object Name -eq $Report
 
    #Check if CatalogItem is received or report is allready favorite
    If ($CatalogItem -and !($FavoritesItems.value -match $CatalogItem.id)){
        $Item = @{
            Id = $CatalogItem.Id
        }
        $Body = $Item | ConvertTo-Json
        
        #Designate a CatalogItem as a favorite
        Invoke-RestMethod -Uri $webServiceUrl'/reports/api/v2.0/FavoriteItems' -UseDefaultCredentials -ContentType 'application/json' -Method Post -Body $Body | Out-Null
    }
}

#Create desktop shotcut
$Shell = New-Object -ComObject ("WScript.Shell")
$ShortCut = $Shell.CreateShortcut($env:USERPROFILE + "\Desktop\" + ($webServiceUrl -replace 'http://','') + " Favorites.url")
$ShortCut.TargetPath=$ShortcutUrl
$ShortCut.Save()

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!