This blog post is tested on SQL Server 2014 and PowerShell V5.
PowerShell has become more feature-rich in every version. We are now looking at PowerShell V5 (currently can be downloaded as part of Windows Management Framework v5 April 2015 Preview).
The support landscape with SQL Server hasn’t changed much. There isn’t a drastic increase in SQL Server cmdlets. However, the language and feature improvements in PowerShell in general improve how we can work with SQL Server.
One area where we can use PowerShell is with SQL Server Reporting Services (SSRS). I blogged about this a while back, but it’s time to revisit and expand on how we can use PowerShell to automate report generation.
In this blog post I will focus on generating PDF reports via scripting. Let’s tackle this piece by piece first, and we’ll put everything in a nice little script at the end of the post.
We can use PowerShell to discover many SSRS properties, including the different parameters available in a report. This blog post is based on SQL Server 2014 and PowerShell V5.
Here is an example:
SSRS parameters in SSDT Preview
This particular report utilizes different variations of parameters. To review, here are the available data types for SSRS report parameters:
SSRS Parameter Data Types
In the example above, Customer ID is an integer. Order Date From and To are dates. Online Flag is a boolean. Min Total Due is a float. Status is a multi-value text.
There are new rendering extensions supported in SSRS 2012. Welcome extensions are the OPENXML rendering formats, to be used with Word 2007/2010 (.docx) and Excel 2007/2010 (.xlsx).
To list the rendering extensions, you can use the ReportViewer from the Reporting Services Redistributable Package.
You can download from here
[System.Reflection.Assembly]::Load("Microsoft.ReportViewer.WinForms, Version=126.96.36.199, Culture=neutral, PublicKeyToken=89845dcd8080cc91") | Out-Null
$ReportServer = "http://KERRIGAN:80/ReportServer"
$rv = New-Object Microsoft.Reporting.WinForms.ReportViewer
$rv.ProcessingMode = "Remote"
$rv.ServerReport.ReportServerUrl = $ReportServer
This should list all the currently supported extensions.
In order to work with report items with SQL Server Reporting Services 2008, most of the time you need to get a handle to the report items.
Here is a sample code snippet that uses the ReportingService2010 web service and PowerShell V3 CTP.
SQL Server 2008 R2 was an exciting release for Reporting Services. It gave us features such as:
– Shared Data Sets
– Report Parts
– New Visualization, including spatial visualizations
There’s are also significant changes and new features to SSRS 2012 – but a some of these can only be materialized if you are using SSRS 2012 integrated in SharePoint 2010. Nonetheless these are still pretty exciting changes, and can change the landscape of reporting for our end users (read: more Self Service BI):
One more SQLSaturday done 🙂 I love SQLSaturdays, it’s always a fun experience for me. Uhm, ok, maybe let me qualify that. It’s always a nervewracking AND fun experience for me – when I’m speaking. I just always want it to be worth people’s time to attend my presentations.
I think there was a good crowd at my session, and I’m quite happy to see they seemed to have fun too (I hope!) …
When you’re working with PowerShell and SSRS, you may occasionally come across a script that works once, then just mysteriously decides not to work anymore on a second or third invocation. Or it may just not work period, even though you think the syntax is short and straightforward, and you know you’re not misspelling any syntax.
Please note I am running this on the PowerShell ISE, and PowerGUI – and tried on both PowerShell V2 and V3.
I wanted (needed!) to write a script to automatically generate a number of reports based on sets of parameters. Most of the scripting I’ve done before are for straight up parameters that I can pass using SMO or through the URL string.
This time, I had a cascading query-based parameter, ie one parameter that is filled out based on another parameter. I was doing my usual drill with PowerShell when I got the following infamous error:
“This report requires a default or user-defined value for the report parameter. To run or subscribe to this report, you must provide a parameter value.”
Which is weird because I did provide all the required parameters. A quick search leads me to a few blog posts, most of which mentioned that it’s because of my query based parameter. Most also suggest the resolution is to convert my parameter which has query-based parameter to be non-query based.
The only problem is I can’t. And I don’t want to. That’s not a resolution. This report is meant to be used both by users for ad hoc purposes, and for massive downloading for month-end reports. Removing the query-based values for a parameter *IS NOT* a resolution. Not for me anyway.
Here’s a short PowerShell script that :
1. Connects to your report server
2. Loops through a set of parameters stored in an array
3. Saves the PDF version of the report to a local folder, with appropriate names
Here’s a snippet of code to set your SSRS parameters programmatically using PowerShell
$params = $null;
#here's a sample usage http://msdn.microsoft.com/en-us/library/microsoft.reporting.winforms.reportparameterinfo(v=vs.80).aspx
$params = new-object 'Microsoft.Reporting.WinForms.ReportParameter' 3
$params = new-Object Microsoft.Reporting.WinForms.ReportParameter("FISCALYEAR", $fiscalyear, $false);
$params = new-Object Microsoft.Reporting.WinForms.ReportParameter("MONTHENDDATE", $monthenddate, $false);
$params = new-Object Microsoft.Reporting.WinForms.ReportParameter("SALESGROUP", $salesgroup, $false);
Of course this is just a sample, you can definitely extend this by using SMO to automatically pull parameter values, or use values stored in a file (among a million other things you can do with ubercool PowerShell)
Just wanted to say thank you to everyone who attended the August 2011 VANPASS meeting at the Sierra Systems building. And thank you to the August sponsors Sierra Systems and QueryWorks Solutions!
If you need to contact me with questions or comments about the presentation just send me an email at
donabel dot santos at queryworks dot ca
Here is a recap of the presentation: