SSRS Reporting

Backup SSRS Reports

by

If you need to backup your SSRS reports you can download each report individually, or use PowerShell to backup all of them quickly.

The information in this post was taken from the following articles:
https://www.recastsoftware.com/resources/how-do-you-backup-all-of-your-custom-configmgr-reports/
and
http://www.sqlmusings.com/2011/03/28/how-to-download-all-your-ssrs-report-definitions-rdl-files-using-powershell/

The website that the Recast article referrers to is no longer available and is only accessible via the web archive link given in the article. This post is so the script is easily available for later use.

The script has been updated and is available on GitHub.

The script requires two parameters, the SSRS FQDN, and the directory save the reports to. If the directory doesn’t exist, the script will create it.

The script assumes that HTTPS has been enabled on the SSRS site. If HTTPS has not been enabled, the script will need to be modified to switch back to HTTPS.

A progress bar is used to show progress.

Report backup in progress.
Reports have been backed up to the location passed to the script.

Collection query for reporting

by

In order to add a collection prompt to a custom report you need to add an additional dataset to your report that queries for all available collections, includes the collection name and ID, and sorts the list by name.

Below an an example of the required code to retrieve all collections from ConfigMgr.

Below is the output from SSMS.

To use the query in a report you need to add a new dataset. In this example the dataset is called “AllCollections.”

Now we can add the collection query to the report and the main dataset.

In order to make a report utilize the collection query, we need to add the view v_FullCollectionMembership_Valid to the report and join it to v_R_System_valid on the ResourceID column.

Now we need to modify the main report query to take advantage of the ConfigMgr collections. This is accomplished by modifying the report to filter on a variable. You do this by checking the box next to the “CollectionID” column from v_FullCollectionMembership_Valid box.

Now we’re ready to add the filter. Under the “Filter” column in the “CollectionID” row, enter ‘=@CollID’ without the quotes.

Now when you run the query you’ll be prompted for a collection ID. In the screenshot, SMS00001, or All Systems, is used.

Results from the query.

SQL to retrieve all collections

by

Below is the SQL code needed to retrieve all of the collections from ConfigMgr. This is useful when creating custom reports that need to be scoped to a specific collection. The query below selects the CollectionName and CollectionID columns and sorts the CollectionName column alphabetically.

Results from the query on my test system.

SQL to get last hardware scan

by

The last hardware scan is stored in the view v_GS_WORKSTATION_STATUS. When joined with either v_R_System_Valid or v_R_System, you can retrieve the last hardware scan for a computer.

The SQL code below will return the last hardware scan date for all computers in the ConfigMgr system.

Results from the above query.

If you need to return the last hardware scan for a single computer you can use the query below to filter by the computer name. In this example, we filtered on computer ‘pc01’.

Results from the above query.