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)
Thank you to all the troopers who attended my really-early-morning-cold-and-snowy session at SQLSaturday#65 in Vancouver, BC. It was such a great event; thanks to all volunteers, sponsors, speakers and organizers (kudos to Scott Stauffer (blog | twitter) .. Thanks to Todd McDermid (blog | twitter) too for helping me give out swags during my session, and thanks for the great powerpoint slide decks which I’ve used in 2 SQLSaturdays now! 🙂
As promised, here are the SQLSaturday#65 presentation materials for SQL Server 2008/2008 R2 Reporting Services from the Ground Up
Brent Ozar (blog | twitter) has a few pictures of the event 🙂
Sample Reports (pdfs)
Report with different visualization components (data bar, sparkline, indicator, gauge, chart)
Report with drilldown, barcode, gauge
Report using map (from ESRI shapefile for Canada) and Bing Maps layer
Your report is only as good as your requirements.
When I first worked with reports and reporting services, I was excited and giddy. Beside my plain old text and T-SQL, I now get to work with some shapes and colors! And look ma, no hands, err, it’s drag and drop!
But the fascination with colors, drilldowns, drillthroughs, what-have-yous fade away as quickly as that drag and drop. You realize fast that – although managers typically like the pie charts, the drill downs, the colored legends – if any number, or any minor thing for that matter, is not “right”, the whole report is not right, and all your work really goes down the drain.
Sometimes, it’s not because the report is “completely wrong”.
Just a few queries against the ReportServer database that might come in handy…
Of course, these are just some sample queries. Tune and modify according to your needs …
border:1px solid #E6DB55;
Just wanted to share some common “tips” or “tricks” I find myself using over and over again when creating reports.
I think I’ve had this draft for a while, and just never was able to publish it because things got so busy. Anyway, I will just publish in parts. Better late (and something) than never (and nothing!)
Common Report Tasks
Common page headers and footers
Report (reportname) generated by DOMAIN/User on 02/03/2010 4:51:03 PM
="Report (" & Globals!ReportName & ") generated by " & User!UserID & " on " & Globals.ExecutionTime"
Page X of Y
="Page " & Globals!PageNumber & " of " & Globals!TotalPages
If you have start and end date parameters, and want to display them in your header or footer, with date formatted as MMM dd, YYYY
="Report Details from " & Format(Parameters!StartDate.Value, "MMM dd, yyyy") & " to " & Format(Parameters!EndDate.Value, "MMM dd, yyyy")