Jeff Pries

Business Intelligence, SQL Server, and other assorted IT miscellany

Category: SSRS

Creating an SSRS Report Using Natural Earth Geospatial Data (A Shapefile Alternative)

In my previous article, I covered creating geospatial SQL Server tables using the freely available Natural Earth resources.  Natural Earth is an extensive public domain map dataset available at 1:10m, 1:50m, and 1:110 million scales in  vector and raster data formats which can be used as an alternative to ESRI Shapefiles for geospatial data.   In this article, we’ll be creating a simple SQL Server Reporting Services report which utilizes this spatial data in lieu of the more commonly used shapefiles to plot data on a custom map.

To get started, we’ll first need to create some assets.  Follow the steps in the previous article to setup the Natural Earth tables.  Next, we’ll create some views to simplify queries, then we’ll create a report using these assets as well as some sample data.

Continue reading

Getting Started with Natural Earth — A SQL Server Shapefile Alternative (Geospatial Resource)

SQL Server Reporting Services (SSRS) has excellent geospatial support for displaying data on a map.  Maps are typically created using ESRI Shapefiles (.shp files).  These Shapefiles are typically created with complex GIS software and made available for download (sometimes free and sometimes not) to be used.  Additionally, SSRS has an excellent default set of Shapefiles built in for the US which can show the country, states, and individual counties.

Example of SSRS Shape File showing Georgia and its 159 counties.

But what about when you need more flexibility in your geographic display?  Some examples of this may be wanting to display something that you can’t find a shape file for (maybe all the states and provinces in North America) or maybe you want to dynamically draw the geography based on some property of the dataset.  Geospatial data queries to the rescue!  Using SQL Server’s native geospatial support, a geospatial query can be created to return something as simple as a point or rectangle, or complex as the geography of an entire country and all of its rivers.

Getting all of the latitude and longitude coordinates to create a useful geospatial query could potentially be an enormous amount of work.  Fortunately, that work has already been done in a freely available resource, thanks to Natural Earth and Laurent Dupuis.  SQL Server 2012 or greater is recommended for this process.

Example of a geospatial query, shown in the SSMS results pane, based on the imported Natural Earth data.

Continue reading

Troubleshooting Error 404, Error 400, or “Invalid Request” or “Bad Connection” in a New SSRS Installation

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.  The below steps are done on the machine running the SSRS web portal:

Continue reading

Windows 10 Upgrade – Part 5: SSRS Report Preview Error in SSDT-BI

Windows 10Having just recently successfully installed the current release of SSDT-BI (the current version as of this writing is based on SQL Server 2014 32-bit and the Visual Studio 2013 Integrated Shell), it was time to finally open up some SSRS (SQL Server Reporting Services) projects and get to work.

For anyone that use used the report Preview button within SSDT-BI, you know getting it to work can be a bit dicey.  Microsoft has changed the internal behavior of what happens behind the scenes when the preview button is pressed, and that has created a number of problems.

No surprise, the first time I pressed the preview button, I was greeted with this:

Report Preview error in SSDT-BI

Report Preview error in SSDT-BI

Well, that’s not good.  I should preface this by saying that I did two significant things to ultimately resolve this issue.  It’s possible that only the second one was needed, but I’m going to list them both (as well as a workaround) just in case it’s helpful.

Continue reading

Windows 10 Upgrade – Part 4: Cannot Install SSDT-BI (Install Error)

Windows 10With all of my driver, aesthetic, and networking issues worked out in previous parts, it was time to get down to business.  After all, I’m going to have to do some work from this laptop at some point, right?  And in the Microsoft Business Intelligence world, that means I’m going to need SSDT-BI (SQL Server Data Tools – Business Intelligence).  As of this writing, the current version of SSDT-BI is still based on SQL Server 2014 32-bit with the Visual Studio 2013 Integrated shell.  It’s expected at some point, with the release of 2016, there’ll be a new and improved version more tightly integrated into Visual Studio 2015, but for now, if you want to edit SSRS projects, this is the way to go.

I started out by downloading the currently release of SSDT-BI, which is 32-bit only (regardless if you’re using a 64-bit machine).

I encountered my first minor hurdle during the initial install.  Since SSDT-BI is based on SQL Server 2014 32-bit, it doesn’t play nice with SQL Server 2014 64-bit.  I wasn’t thinking clearly and when presented with the option to “Perform a new installation” or “Add features to an existing instance,” I first tried to add features and the installer yelled at me for having an incompatible installation already in place (SQL Server 2014 64-bit).  So the correct answer is Perform a new installation, which will put these 32-bit components side-by-side with any existing 64-bit components.

After selecting the right installation type and checking the box for the SQL Server Data Tools – Business Intelligence for Visual Studio 2013 shared feature to install, I thought I was on easy street.  Boy was I wrong, the fun was just beginning.  While the installer was running, it eventually failed with this error:

SSDT-BI Install Error - VS Shell Installation has failed with exit code -2147205120

SSDT-BI Install Error – VS Shell Installation has failed with exit code -2147205120

That’s not good.  I immediately tried all the basic IT things before going deeper.  I rebooted and retried the installation, this time running as administrator.  Same error.  Time to go deeper.

Following the error dialog is more detail regarding the error.  To any developer that writes an installer that includes the option to view detailed log files….THANK YOU.

SQL Server Data Tools - Business Intelligence for Visual Studio 2013 installation completed with failures

SQL Server Data Tools – Business Intelligence for Visual Studio 2013 installation completed with failures

Continue reading

Geocaching Analytics – Part 5: GCInsight Visualizations with SSRS

Geocaching Analytics – Part 5: GCInsight Visualizations with SSRS

Continuing our journey of analyzing Geocache data with various visualization tools, we arrive at SQL Server Reporting Services (SSRS).  SSRS is a server-based SQL Server component used for generating static (and generally printer friendly) reports.

Aside from its cost (free), one of the greatest benefits of SSRS is the ease of use in getting started with report creation.

This post won’t cover the installation and configuration of the server-based components of SSRS (there are plenty of resources out there on the Internet and the process varies based on your version and edition of SQL Server).  At its core though, it is made up of the following components:

  • A SQL Server Reporting Database which stores information used by SSRS, including connection information and reports.  This database is initially created by the installation and is managed by the product.
  • The Report Server and Report Manager services, which are the core service behind Reporting Services as well as a web server (running either natively or inside of SharePoint) which allows for the access and administration of reports and report components.
  • Design Tools such as Report Builder and SQL Server Data Tools (Visual Studio)
SSRS conceptual diagram.  Source:  http://www.accelebrate.com/library/tutorials/ssrs-2008

Going into the inner workings of SSRS and report development can go down the rabbit hole of complexity very quickly, so we’ll stop there.  A quick summary is that report design tools create report files which are stored inside of reporting services and can then be accessed via a web browser.  As we’re interested in geocaching visualizations, we’ll be focusing on the content created within the report design tools.

Continue reading

My New Favorite Chart — Overlapping Bar Charts

My New Favorite Chart — Overlapping Bar Charts

Back in April, I had the pleasure of attending Jason Thomas’s (B | T) Advanced Charting Techniques in SSRS at SQL Saturday #220 in Alpharetta, GA.

During this session, I was introduced to my new favorite SSRS visualization — the Overlapping Bar Chart.

The overlapping bar chart is really just a standard bar chart with a range chart in the background.  The range chart in the background represents the sum of the bars in the foreground.  This allows you to see very quickly the total amount for a category as well as a quick breakdown of the parts that make up that total.

To create an overlapping bar chart, you will first need some data.  The data should have a category (such as dates or products) and then a couple of quantity columns and finally a total column which sums the quantity column.

Continue reading

SSRS Usage Reporting — with SSRS!

SSRS Usage Reporting — with SSRS!

A task I’ve recently needed to perform was to migrate our company SSRS portal from a SQL Server 2008 R2 Native Mode portal to a shiny new SQL Server 2012 Sharepoint Integrated Mode portal.

Additionally, during this process, I had a desire to clean up the organization of the reports and potentially retire some of the older reports which were no longer used.  The issue that I immediately encountered, however, was a lack of readily accessible information about which reports were frequently (or infrequently) used, by whom, and how often.

With a little bit of research on the topic, I found that SSRS does indeed track report usage metrics within the ReportServer database, however, there aren’t any built-in mechanisms to easily review that data.

My research first took me to a Microsoft Technet posting with the following T-SQL to collect some basic usage statistics from the ReportServer database:


SELECT 
Cat.[Path], 
Cat.Name, 
Ex.[Format], 
Ex.Parameters, 
Ex.UserName, 
Ex.TimeStart, 
CONVERT(nvarchar(10), 
Ex.TimeStart, 101) AS rundate,  
1 AS [Counter] 
FROM ExecutionLog AS Ex INNER JOIN [Catalog] AS Cat ON (Ex.ReportID = Cat.ItemID)
ORDER BY Ex.TimeStart DESC


This was a great indicator of the possibilities for reporting on SSRS usage.  A little further research turned me onto the following Wrox article by Jim Minatel with some additional sample queries and some great ideas for what I’d like to include in the SSRS Usage Report I was creating.

Within the ReportServer database (both for SSRS Native Mode and SSRS SharePoint Integrated Mode), the most interesting tables when it comes to SSRS usage statistics are:

  • ExecutionLog – The most useful table, this is a log of all SSRS component executions as well as the status of the execution.
  • Catalog – Equally useful, this is a full listing of all SSRS assets (report RDL files, data sources, datasets, and even Power View reports) which are needed to identify in a friendly manner the names of the items in the ExecutionLog.
  • Users – A listing of all users with their unique IDs and authentication types.  This table proved to be the least useful, as usernames are stored in a readable form in the above tables.
Performing T-SQL queries to join at least two of these tables allowed for me to create a base detail query which would provide the foundation for data shown in my SSRS Usage Report:

Continue reading

SQL Saturday Roundup: #192 – Tampa, FL

This past weekend, I had the pleasure of attending SQL Saturday #192 in Tampa, FL, put on by the Tampa Bay SQL Server User Group.This being the first SQL Saturday of the year for my southeastern region, I was pretty excited to pop in the car and make the trek on down to Tampa.
Originally, I hadn’t planned on attending the event — Blissfully unaware, I’d already booked myself for the Richmond, Virginia SQL Saturday the following weekend.  Fortunately, though, I happened upon a tweet mentioning the great pre-con sessions being offered and I was sold.About a year prior, I’d been really getting into reporting services and had been Googling for local classes for some additional in person SSRS training.  I didn’t find much, but I did find a class being advertised as something called a “pre-con” preceding something called a “SQL Saturday” being given by somebody named Jessica Moss.  Sadly, this was about a week before the class so it was already very sold out with a full wait list.  “Maybe next time!” I said to myself, and filed away the idea of a SQL Saturday in my head for the future.  Now, a year and 5 SQL Saturdays for me later, it appeared that a second chance for a SSRS class with Jessica Moss had appeared.  Back-to-back SQL Saturdays in opposite directions from Atlanta for me then!

Friday morning, I attended Jessica Moss’s (B | T) “Taking the Next Steps with Reporting Services” pre-con session.  A pre-conference session is a full day class on a single topic given by a single presenter.  They’re typically given the day before a SQL Saturday and typically fairly small class sizes.  Additionally, they’re incredibly affordable.  We had a very small class size for the pre-con, which was great — it was very easy to learn, ask questions, and chit-chat.  Jessica did a great job of covering a large array of SSRS topics, many of which I was familiar with and many that I hadn’t yet worked with.  For anybody serious about SSRS, I highly recommend it!

Saturday morning kicked off the main SQL Saturday event.  This year, for the first time, the event was held at Hillsborough Community College in Ybor City.  Generally speaking, this was a great venue and provided far more space than the previous venue at K-Force.  My only complaints were that parking was a little bit confusing if you weren’t in the main lot (not too bad) and the event was actually spread out between two non-adjacent buildings — which made finding the second building a bit confusing.  Other than a little confusion though, the venue was very nice and provided plenty of room in each session I attended.

Rather than simply dropping off raffle tickets at vendor booths, the SQL Saturday Tampa crew had a sort of bingo card /scavenger hunt to fill out.  Each square on the card represented a vendor or person and it was necessary to find people to check off all the boxes to be turned in for a raffle at the end.  This was the first time I’d seen this idea used at a SQL Saturday event and I felt it did a very good job of getting me to walk up to and talk to people I otherwise may not have.

Throughout the day, I attended all six sessions and was very pleased with all my selections.  I felt all of the material was interesting and top notch and all the speakers did a great job.  A couple of sessions really stood out to me in particular:

  • A Big Data Primer” by Stacia Misner (B | T) — I’m constantly hearing about Big Data, but not in an industry that has any use or real exposure to it.  Stacia did a great job giving an overview of the industry, technology, and terminology involved.  I left the session having a much better understanding of the concept of Big Data and a working explanation of how Big Data differs from regular? data.
  • Way Too Much Fun with Reporting Services” by Stacia Misner (B | T) — Another great session by Stacia!  In a nutshell, Stacia has put together a Words with Friends online game using SSRS as the engine.  While the game itself has its flaws (I think anyone would agree that SSRS is probably not the best tool for the job of creating a game) it was incredibly eye opening seeing SSRS used as a platform that actually changed data rather than just showing a read only view of data.
  • Exploring Your Data with Power View” by Jessica Moss (B | T) — Power View is a technology big on my want list that most likely I’ll explore on this blog.  But that’s a story for another day.  Jessica gave a great overview of Power View relying heavily on Microsoft’s online Sales Demos to show us a variety of the Power View functionality.  I’m definitely looking forward to doing some implementation with this!
  • Becoming a Data Professional – Taking it to the Next Level” by Buck Woody (B | T) — for my last session of the day, I visited Buck Woody for an overview on the current state of data professionals.  Hands down, Buck is one of the most interesting speakers I’ve ever listened to.  He has a great mix of humor and fact that creates a very compelling presentation.  It didn’t hurt that all of his information was great, either!
With the conclusion of the day’s session, we gathered for the traditional closing words and raffle.  There was a great turnout even until the end and everybody was very appreciative of the hard work in putting on the event.  Additionally, I won a copy of “Visualizing Data with Microsoft Power View” by Brian Larson to add to my reading list.  A great event all around and thanks to everyone for your hard work in organizing it!
On my way out of Tampa the following morning, I decided to hit up a couple of local geocaches in the area.  For anybody who hasn’t, I highly recommend you hit up some of the caches by CacheMedics — they’re world class!

© 2017 Jeff Pries

Theme by Anders NorenUp ↑