Generate SSRS Report On Demand with PowerShell and a Web Service

By: Jeffrey Yao   |   Updated: 2024-02-14   |   Comments   |   Related: > Reporting Services Development

Problem

There are times when we need to generate the same SSRS report over and over again but using different parameters for each run. We may also need to generate a report in a specific format, such as PDF or Excel, and then send it to different users.

For example, let's assume I am a teacher with an SSRS report that can generate a student's test score report based on the student ID, and I want to send each student their score report by email. How can I accomplish this without having to manually run the report over and over again using different parameters for each run?

Solution

SSRS provides functionality through an XML web service with a SOAP API. As such, if we can make a program send a correct web service request with the right parameter values, we should be able to get the SSRS to render a report with the needed format.

Fortunately, in PowerShell, we have a cmdlet called invoke-webrequest that can make a request to a web service. As long as we compose the right request, i.e., with proper parameter values for the SSRS service, SSRS will generate the right report with the correct format (PDF of a CSV or HTML, etc.).

Let's demonstrate how this works. First, we need to create a demo package composed of a simple SSRS report on three simple tables plus one PS script:

use mssqltips
go
drop table if exists dbo.student, dbo.[subject], dbo.Score
create table dbo.[Student] (id int identity primary key, FirstName varchar(128), LastName varchar(128), Email varchar(250));
Create table dbo.[Subject] (id int identity primary key, SubjectName varchar(128));
create table dbo.[Score] (id int identity primary key, Stu_ID int, Sub_ID int, Score int);
go

-- change email values for your own needs
insert into dbo.[Student] (FirstName, LastName, Email)
values ('John', 'Doe',   '[email protected]')
     , ('Mary', 'Smith', 'msmith.hotmail.com')
     , ('Mike', 'Hsu',   '[email protected]')
go

insert into dbo.[Subject] (SubjectName) values ('Math'), ('English'), ('History'), ('Science');
go

-- for John
insert into dbo.[Score] (Stu_ID, Sub_ID, Score)
values (1, 1, 90) -- Math 90
     , (1, 2, 92) -- English, 96
     , (1, 3, 88) -- History, 88
     , (1, 4, 99) -- Science, 99
 
-- for Mary
insert into dbo.[Score] (Stu_ID, Sub_ID, Score)
values (2, 1, 80) -- Math 80
     , (2, 2, 99) -- English, 99
     , (2, 3, 100) -- History, 100
 
-- for Mike
insert into dbo.[Score] (Stu_ID, Sub_ID, Score)
values (3, 1, 88) -- Math 88
     , (3, 2, 94) -- English, 94
     , (3, 4, 79) -- Science, 79

Create a Simple SSRS Report

As shown below, it is a straightforward report, so I won't explain how to create it. However, I will provide the key information about what data the report will use (all screenshots are from SSRS Report Builder).

I created a data source named [dsScore], which is defined as the following:

Based on this data source, two datasets were created:

1. [dsetStudentID] with the following setup. This dataset is to provide values for the report parameter, i.e., [StudentID].

2. [dsetScore]

We also created an SSRS parameter [StudentID].

This parameter's available values are from the dataset [dsetStudentID]

After the report is published to the SSRS server, we run it. If we chose, for example, StudentID = 2, we will get the following report:

This report can be saved as a PDF file by clicking the save button and choosing "PDF." This score_report.pdf file will automatically save to your computer's [Download] folder.

To do this for each student is very manual. First, we must choose a Student ID and then save the generated report to a PDF file. If we need to generate a score report for each student in a class, operating manually can be very tedious.

Create PowerShell Script to Run SSRS Report with Parameter

To automate this via programming, we can use the following PowerShell script to do the work. I will assume you have installed the latest PowerShell SQL Server module.

#save ssrs report to PDF file
#this script will generate each student's score report and put them into a folder
import-module sqlserver; # assuming the latest sqlserver PS module is installed
 
$srv = "localhost"; # change to your own sql server instance name
$db = "mssqltips"; # change to your own db name
 
$pth = 'c:\mssqltips\'; # where the score report PDF files are saved
 
$qry = "select StudentID = id from dbo.Student;"
 
$result = invoke-sqlcmd -ServerInstance $srv -Database $db -Query $qry -OutputAs DataRows -TrustServerCertificate; 
 
[int]$student_id=0;
foreach ($student_id in $result.StudentID)
{
    $uri = "http://localhost/ReportServer?/score_report&StudentID=$($student_id)&rs:Format=PDF"; #Parameter name is case-sensitive
    
    $Render = Invoke-WebRequest -Uri $uri -UseDefaultCredentials -UseBasicParsing;
 
    $filename = $pth+ "ScoreReport_$($student_id).pdf";
    [system.io.file]::WriteAllBytes($filename, $Render.Content); # write PDF file
    # [system.io.file]::writealltext($filename, $Render.Content); # for Format=CSV
}
 

The report rendering command, i.e., rs:Format, can have one of the following common formats: PDF, CSV, XML, Word, Excel, or IMAGE. For details, please see the links in the Next Steps section of this tip.

One special note here is that the parameter name in the Uri link is case-sensitive and should be exactly the same as the parameter name used in the SSRS report. In my case, it is StudentID (see Fig_Parameter above). If I use studentid instead of StudentID, it will not work.

To run the script quickly, we can copy and paste it into a PS ISE window and run it, and we will find three PDF files generated:

If I open ScoreReport_2.pdf with Acrobat Reader, I can see the following:

Report Delivery via Email

With all reports generated, we can easily create a T-SQL script to send out each report to individual students.

Assuming we already have SQL Server database mail set up, we can use the following T-SQL script to do the work:

-- send email to each student
use mssqltips;
go
declare @report_path varchar(256)='c:\mssqltips\';
declare @sqlcmd varchar(max), @crlf char(2) = char(0x0d) + char(0x0a);
declare @debug bit = 1; -- print out cmds, 0 = execute
declare @studentID varchar(3), @email varchar(128);
 
declare cStu cursor for
select stu_id=cast(id as varchar(3)), email from dbo.student;
 
open cStu;
fetch next from cStu into @studentID, @email;
while @@FETCH_STATUS = 0
begin
    set @sqlcmd = 'exec msdb.dbo.sp_send_dbmail @recipients=' + quotename(@email, '''') + @crlf
    + ', @subject= ''Score Report For Student ID =  ' + @studentID  + '''' +  @crlf
    + ', @body = ''Please check your attached report''' + @crlf
    + ', @file_attachments = ''' + @report_path + '\ScoreReport_' +@studentID + '.pdf'';';
 
    if @debug = 1
    begin
      print @sqlcmd;
      print @crlf; 
    end
    else
      exec (@sqlcmd);
   fetch next from cStu into @studentID, @email;
 
end -- @@fetch_status = 0
 
close cStu;
deallocate cStu;

If I set @debug = 1 and run the T-SQL script in an SSMS query window, I will get the following printed T-SQL commands:

We can examine the code generated and see whether it is logically correct. We can even copy and paste a few lines to run manually. Or we can set @debug = 0 so the whole delivery script can be executed. Of course, SQL Server database mail needs to be set up before any email can be sent out.

Summary

This tip uses PowerShell to generate an SSRS report via a web service request. Once the files are generated, we may further handle them for other purposes, such as sending them to end users.

We can generate SSRS reports in many other formats, such as Word, Excel, CSV, or XML formats, and these types of files may be consumed by other downstream applications. It extends the presentation of the data inside SQL Server databases.

Next Steps

We may also modify the PS script in this tip to be a function and package it into a customized module so we can use it more conveniently.

The following links provide more information about SSRS URL access details, which are the technical foundation of this tip. Also, at MSSQLTips, we have other tips about performing similar work within an SSIS package.

• SSRS URL Access
• SSRS Web Service URI Parameter.
• Execute a SQL Server Reporting Services report from Integration Services Package

About the author

Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips