Press "Enter" to skip to content

Category: Reporting Services

SSRS Mobile Report Drillthrough

Published 2017-03-23 by Kevin Feasel

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:

[TableName].[Attribute].&[Value]

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.

Comments closed

SSRS: SendGrid Without Encryption

Published 2017-03-20 by Kevin Feasel

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.

Comments closed

SSRS Category Charts & Ints

Published 2017-02-28 by Kevin Feasel

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.

Comments closed

SSRS With Natural Earth Geospatial Data

Published 2017-02-27 by Kevin Feasel

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.

Comments closed

Troubleshooting SSRS 4xx Errors

Published 2017-02-13 by Kevin Feasel

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.

Comments closed

SSRS Sender Display Names

Published 2017-02-07 by Kevin Feasel

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 NoReply@domain.com or CRM@domain.com or Reports@domain.com or…who knows what.

The problem with these more generic sender addresses is that they don’t help me troubleshoot. Is CRM@domain.com coming from SSRS or the application server? Which SSRS server is Reports@domain.com 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.

Comments closed

Caching KPI Reports

Published 2017-01-31 by Kevin Feasel

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.

Comments closed

SSRS Log File Location Change

Published 2017-01-30 by Kevin Feasel

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.

Comments closed

In-House Power BI

Published 2017-01-19 by Kevin Feasel

Paul Turley reports that the bits to publish a Power BI report to SSRS are in the latest vNext preview:

“Power BI reports in SQL Server Reporting Services: January 2017 Technical Preview now available”  This feature addition will allow Power BI reports to be published to a local SQL Server Reporting Services server, entirely-on-premises without using the Power BI cloud service.

The January 2017 Technical Preview can be downloaded from: https://www.microsoft.com/en-us/download/details.aspx?id=54610

Reza Rad shows us installation and deployment:

We are in a world that rapidly running towards cloud. Your files are in Dropbox, or OneDrive these days, Your photos uploaded to a cloud storage, your emails are all backed up in a cloud backup media, and I’m in this thinking that in next few years, we might eat our food from a cloud kitchen! However there are still businesses and companies who require some on-premises solutions, and as long as a requirement exists, there should be an answer for it. Power BI for On-Premises bring the power of self-service, interactive reports of Power BI to these businesses. Power BI for On-premises is a great big step towards utilizing better data insight in all environments.

This will probably help more companies than you might think—Power BI is really useful as a reporting tool, but it can be hard getting sign-off to go to Azure.

Comments closed

Upgrading SSRS From 2008R2 To 2014

Published 2017-01-13 by Kevin Feasel

Dave Turpin needed to perform an upgrade of a Reporting Services installation:

So let me share with you my biggest take away from this project:  EVERYTHING ABOUT USER CREATED REPORTS IS STORED IN THE SQL Server Reporting databases.  So if you are reading this post you probably are about to move an instance of SSRS, and may be concerned about the many, many reports involved.

Based on my one experience with this, there is need to move individual reports.  If you follow the process carefully, all of the existing reports will be re-created on the new machine.  It’s not quite magic, but it sure feels like it when everything shows up on the new system.

Read on for the solution Dave came up with.

Comments closed