Deploying Reports With Powershell

Jana Sattainathan has created a few Powershell functions to automate dealing with SQL Server Reporting Services report deployment:

In this post, I want to publish a few functions that I created around SSRS. They are related to and depend on each other.

  • Get-SSRS – Given the SSRS URI returns the WSDL endpoint

  • Get-SSRSReport – Returns one or more reports based on inputs

  • Get-SSRSSharedDataSource – Returns one or more shared data sources based on inputs

  • Get-SSRSReportDataSource – Returns the data source information on a report by report basis based on inputs

  • Set-SSRSReportDataSource – Sets the data source of a report to the given data source.

  • Install-SSRS – Deploys an SSRS report to a specific folder and also optionally sets the datasource for the deployed report

Very useful.

Advanced Report Design

Paul Turley excerpts a chapter from his new Reporting Services book:

With respect to page layout, reports have two sizing modes: interactive and printable. When users run a report in their web browser and use it interactively, they typically don’t care that much about the page size. This is particularly true with reports that have wide content like a matrix region that can dynamically grow horizontally with the data. When a report is printed or rendered to a print- able format like a PDF or Word file, we need to be mindful about fitting the content on pages.

The report designer does not make page sizing and dimensions particularly obvious so it’s an easy thing to miss. Fortunately, the science behind page sizing is pretty simple. Page dimension properties are grouped into two objects that you can select in the designer; these are shown in Figure 7-1. With the Properties window visible, click outside the report body to show properties for the report. Here you will see the InteractiveSize and PageSize properties. Expand these to see the individual Width and Height properties for each group.

Read on to get the better part of a full chapter’s worth of material.

SSRS Mobile Report Drillthrough

Patrick LeBlanc shows how to drill from a mobile SQL Server Reporting Services report to a paginated report (built on Analysis Services):

17. The report appears but does not execute because the parameters are not set. Why not?

Well, after inspecting the URL (http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fHigher+Education+Solution%2fReports%2fAnnual+Enrollment+Details&DateSchoolYear=2007&Term=Spring), it passed the values as expected. What is the problem? Remember, the parameters are populated from and SSAS model, so that means we need to send the values formatted as such. This format is:


No problem, just build that string as part of the URL. Guess what, that doesn’t work either. What you need to do encode certain characters in the URL. For example, to pass year it needs to look like this [Date].[School Year].&[{{SelectionList.SelectedItem}}].

Click through for a step-by-step guide.

SSRS: SendGrid Without Encryption

Denny Cherry shows how to configure Reporting Services to use SendGrid (sans encryption) to send e-mails:

We setup our nice new SQL 2016 SSRS servers to authenticate using the new SQL 2016 GUI which just supports it now. One problem with the GUI is that it requires that you use encryption. No big deal, SendGrid supports encrypted SMTP.

The problem with SendGrids encrypted SMTP is that it has a funky certificate chain which isn’t trusted by default. And it’s a convoluted process to get the correct certificate chain installed on a few servers (we have an SSRS farm as there’s thousands of reports being delivered every morning).

Click through for the solution.

SSRS Category Charts & Ints

Kathi Kellenberger notices an oddity with SSRS Mobile Report category charts:

Notice that OrderYear displays decimal points. I switched the dataset in the Series field name property, and found that neither of the columns in the dataset can be used.

Numeric columns cannot be set as a Series name field. To work around this, I modified the dataset, casting OrderYear as a CHAR(4).

That’s not a great situation, but at least there’s a workaround.

SSRS With Natural Earth Geospatial Data

Jeff Pries shows how to use the Natural Earth data set in a SQL Server Reporting Services report:

After proceeding through the New Layer Wizard three times to add three layers to the map, we have all of our data present.  We now just need to do a little housekeeping to make the map more presentable.  We’ll go through each layer and make slight tweaks to each.

Before adjusting the layers, first notice that we essentially have two legends.  The Legend box and the Map Scale box.  They both give us the same information.  Since the Legend is using more real estate, delete it.

There are a lot of steps involved, but the end result is a nice report.

Troubleshooting SSRS 4xx Errors

Jeff Pries explains how to troubleshoot various Reporting Services configuration errors:

After installing SQL Server Reporting Services (SSRS), are you receiving an Error 404, Error 400, “Invalid Request” error, or “Bad Connection” error on first visiting the SSRS web portal (the error message seems to vary based on version, browser, and whether accessing via http/https or /reports vs /reportserver) ?

I’ve run into this a few times so I’m listing the steps I’ve used to fix it.  For me, the root cause of this error has been the SSRS Configuration Wizard automatically configuring SSRS to use HTTPS, but assigning an invalid machine SSL Certificate.  The fix is to self-generate a new and valid SSL certificate for the SSRS website to use.

Jeff then provides step-by-step instructions.

SSRS Sender Display Names

Andy Mallon shows how to customize a display name for a sender e-mail address in Reporting Services:

The business wants the sending email address to be pretty. They want to identify the application, project, company, or… who knows what. The business wants anything but the server name. They might want it to come from or or or…who knows what.

The problem with these more generic sender addresses is that they don’t help me troubleshoot. Is coming from SSRS or the application server? Which SSRS server is coming from? I don’t want to check 10 SSRS servers to figure out which one sent a report.

Click through to find out how to make the troubleshooting DBA and the business side happy.

Caching KPI Reports

Kathi Kellenberger discusses caching in SQL Server Reporting Services KPI reports:

Because these reports automatically show the data, the reports show cached data only. Imagine if hundreds or even thousands of report users brought the web portal page up each day causing the KPI reports to hit the database even when the report user was not interested in seeing the KPI reports at that time. That is why Microsoft decided to use cached data only in these reports.

When the data changes, the KPI report will continue to show the same information unless you configure a cache refresh plan on the dataset. Follow these instructions so that the KPI data will refresh on a scheduled basis.

Read on for a step-by-step guide on how to set up caching.

SSRS Log File Location Change

Wolfgang Strasser points out that SSRS log files are in a new directory structure for vNext:

The log files can be found in the Logfiles directory (it was the same directory also for the older versions). In SSRS vNext there more different log files..

The logging information seems to be splitted into multiple log files – if you want for example dig into the Power BI on-premises logging I propose to have a look at the RSPower*.log files.

This happens every once in a while, so it’s good to know when the log files move somewhere else.


April 2017
« Mar