PowerShell and SSRS: Passing Values to Query-Based Parameters Programmatically

Written by belle
Posted October 13, 2011 at 11:59 pm

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.

I guess I’m stubborn. I tried a few more things, bonked my head a few more times, and finally found the issue. D’oh, I should’ve known too! The only reason my parameter passing wasn’t working was because I was passing a GUID parameter as a string, when I should have strongly typed the variable before I passed it!! It also helps that the value I am passing is indeed a valid value based on one of the other parameters I am passing.

 
#this needs to be the right datatype
[System.Guid]$salespersonid = [System.Guid]$($line.salespersonID);

Not to make excuses, but somewhere in the error message should have said “Check your type please”. Anyway, I’m happy. Script works now, and didn’t have to do too much OT

Takeaways from this:

– sometimes, you really have to dig deeper and not completely trust the error messages. True some of them are helpful, some are intuitive, but I’ve been in too many instances where the error message cannot be farther from what real problem is
– if you are passing a value for a query-based parameter, you need to make sure that the value is in the domain of valid values (ie correct data type, correct actual value) based on other parameters you are passing.

Anyway, here’s my whole script. I loop through my parameter values, which are stored in a text file, and use them to generate a whole bunch of PDF reports.

#==============================================================
#PowerShell and SSRS
#http://www.sqlmusings.com / http://www.twitter.com/sqlbelle
#==============================================================
cls                                                                     
 
#I am qualifying this because I have more than one version in my system
[void] [System.Reflection.Assembly]::Load("Microsoft.ReportViewer.WinForms, `
	Version=10.0.0.0, Culture=neutral, `
	PublicKeyToken=b03f5f7f11d50a3a")
#If you need webforms, use Microsoft.ReportViewer.WebForms
 
#Windows.Forms for viewing
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
#System.IO because I want to export to Excel
[void][System.Reflection.Assembly]::LoadWithPartialName("System.IO")
#System.IO because I want to open Acrobat
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Diagnostics")
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Net")
 
 
$rv = New-Object Microsoft.Reporting.WinForms.ReportViewer;
$rv.ProcessingMode = "Remote";
$rv.ServerReport.ReportServerUrl = "http://server/ReportServer";
$rv.ServerReport.ReportPath = "/Custom/Tracking Report";
 
#--------------------------------------------------------------
#PDF
#--------------------------------------------------------------
$mimeType = $null;
$encoding = $null;
$extension = $null;
$streamids = $null;
$warnings = $null;
 
$monthname = "September2011";
$monthenddate = "9/30/2011 12:00:00 AM";
$fiscalyear = "2012";
 
#get parameters and load into array
$filename = "C:\MonthlyIndividualTracking\salespersons.csv";
$salespersons = @(Import-Csv $filename);
 
foreach ($line in $salespersons) 
{
  $params = $null;
 
  #parameter value from file
  $department = $($line.BU);
 
  #another parameter value from file
  #this needs to be the right datatype
  $salespersonid = [System.Guid]$($line.salespersonID); 
  $salespersonname = $($line.NAME);
 
  $params = new-object 'Microsoft.Reporting.WinForms.ReportParameter[]' 6
  $params[0] = new-Object Microsoft.Reporting.WinForms.ReportParameter("FISCALYEAR", $fiscalyear, $false);
  $params[1] = new-Object Microsoft.Reporting.WinForms.ReportParameter("MONTHEND", $monthenddate, $false);
  $params[2] = new-Object Microsoft.Reporting.WinForms.ReportParameter("DEPARTMENT", $department, $false);
  $params[3] = new-Object Microsoft.Reporting.WinForms.ReportParameter("SHOWSALESDETAILS", $false, $false);
  $params[4] = new-Object Microsoft.Reporting.WinForms.ReportParameter("SHOWACTIVITYDETAILS", $false, $false);
 
  #Don't ask me why our SALESPERSONID is a GUID. It just is. 3rd party db, need I say more? 
  #I dont think they have attended Kimberly Tripp's sessions yet, or subscribed to SQLSkills blog :)
  #http://sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx
  $params[5] = new-Object Microsoft.Reporting.WinForms.ReportParameter("SALESPERSONID", $salespersonid, $false);
 
  $rv.ServerReport.SetParameters($params);
  $rv.ProcessingMode = [Microsoft.Reporting.WinForms.ProcessingMode]::Remote;
 
  $rv.ShowParameterPrompts = $false;
  $rv.ServerReport.Refresh();
 
  $bytes = $null;	
  $bytes = $rv.ServerReport.Render("PDF", $null, 
    [ref] $mimeType, 
    [ref] $encoding, 
    [ref] $extension, 
    [ref] $streamids, 
    [ref] $warnings);
  $file = "C:TrackingTracking - " + $department + " - " + $monthname + " - " + $salespersonname +".pdf";
  $fileStream = New-Object System.IO.FileStream($file, [System.IO.FileMode]::OpenOrCreate);
  $fileStream.Write($bytes, 0, $bytes.Length);
  $fileStream.Close();
  #if you want to open
  #[System.Diagnostics.Process]::Start($file2);
 
}