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.

Common Task

What was driving me crazy (at some point, I promise I’m back to my sane self now) was trying to create a folder with property. The syntax is pretty straightforward, like this:

Import-Module SQLPS -DisableNameChecking;
$ReportServerUri  = "http://localhost/ReportServer/ReportService2010.asmx"
$proxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential -Namespace SSRS ;
$proxy
$proxy.GetType().Namespace;	#gives me SSRS
 
#create a folder
$property = New-Object  "SSRS.Property" 
$property.Name = "Description"
$property.Value = "This folder is for any HR related reports"
 
#we need a property array to pass to the CreateFolder method
$properties = New-Object "SSRS.Property[]" 1;
$properties[0] = $property;
$foldername = "HR_" + (Get-Date -format "yyyy-MMM-dd-hhmmtt");
$proxy.CreateFolder($foldername, "/", $properties);

Broken??

Should be simple, right? PowerShell says, nope, not today. You get this error:

Cannot convert argument "Properties", with value: "SSRS.Property",
for "CreateFolder" to type "SSRS.Property[]":
"Cannot convert the "SSRS.Property" value of type "SSRS.Property"
to type "SSRS.Property"." 
At C:\Users\Administrator\Scripts\SSRS\SSRS.ps1:22 char:1
+ $proxy.CreateFolder($foldername, "/", $properties);
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument

Uh, what? Doesn’t that sound a little – wrong? It’s complaining about casting from SSRS.Property to SSRS.Property. It’s the same thing!
Read the rest of this entry »

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.

Read the rest of this entry »

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;
 
  #set parameters
  #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[0] = new-Object Microsoft.Reporting.WinForms.ReportParameter("FISCALYEAR", $fiscalyear, $false);
  $params[1] = new-Object Microsoft.Reporting.WinForms.ReportParameter("MONTHENDDATE", $monthenddate, $false);
  $params[2] = new-Object Microsoft.Reporting.WinForms.ReportParameter("SALESGROUP", $salesgroup, $false);
 
  $rv.ServerReport.SetParameters($params);

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)

Read the rest of this entry »

Here’s a short PowerShell script that :
1. Connects to your report server
2. Creates the same folder structure you have in your Report Server
3. Download all the SSRS Report Definition (RDL) files into their respective folders

In addition to backing up your Source Project, your ReportServer database, or good old RSScripter (see http://sqlserver-indo.org/blogs/mca/archive/2009/03/08/extract-and-transfer-rdl-files-from-ssrs.aspx) this is just another way you can “backup” or archive your reports.
Read the rest of this entry »

Just a short tidbit on how to list your ReportServer Items using PowerShell

#note this script is tested on PowerShell v2 and SSRS 2008 R2
 
$ReportServerUri = "http://yourserver/ReportServer/ReportService2005.asmx";
$Proxy = New-WebServiceProxy -Uri $ReportServerUri -Namespace SSRS.ReportingService2005 -UseDefaultCredential ;
 
#check out all members of $Proxy
#$Proxy | Get-Member
#http://msdn.microsoft.com/en-us/library/aa225878(v=SQL.80).aspx
 
$items = $Proxy.ListChildren("/", $true);
 
$items | select Type, Path, ID, Name | sort-object Type, Name

Read the rest of this entry »

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

Read the rest of this entry »

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”.
Read the rest of this entry »

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 …
Read the rest of this entry »