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

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.

Continue reading

Download SSRS Parameterized Reports in PDF with PowerShell

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);


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)

Continue reading

Daring to Dream .. What It Means To Be an MVP (for me)

Just this week, UBC launched of a very ambitious campaign. The tagline is “Start an Evolution” – UBC is launching the biggest fundraising campaign in history. I am very proud to be part of this organization .. albeit in the shadows (I work on the databases, you see).

For the unfamiliar it probably sounds like just a “money thing”, but for those who are familiar, it is more than just raising money. It is all about building opportunities for students, inspiring them to become what they can become, and empowering them to take on challenges that they otherwise may not be able to take on by themselves. It’s about daring to dream. It sounds so cliche, but that’s what it is.

One example, there was one group of students, inspired by their professor and funded by someone who believed in the dream, went on to do an investigative journalism piece that won an Emmy. There are lots more inspiring stories like this (Read these success stories) … We have to remember that the littlest drop, the littlest change, can cause ripple effects of unfathomable lengths.

Somehow, this is what it feels, and means to be a SQL Server MVP – for me…

Just today I opened my inbox, and I saw an email saying “Congratulations! We are pleased to present you with the 2011 Microsoft® MVP Award! …”.

“huh … gasp!!!” …. I literally had to pause, and re-read my email. Because this is something I did not expect at all. I knew I was nominated, but knowing there are so many other great, expert SQL Server Professionals who are up for the award, I knew I didn’t have a chance.

But … I still dared to dream. Long before I got the email, I did dream and thought it might be nice to get the award. I’d be a hypocrite if I told you getting the award didn’t matter. For me, it’s this nice warm fuzzy feeling knowing that somehow, you’ve been noticed, and that somehow there’s this appreciation for some of the things you’ve done.

Continue reading