Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Posts Tagged ‘maps’

SSRS R2 Map – What Happened to Michigan?

Posted by denglishbi on October 16, 2010

I am not a geography or map expert and that is most likely why I did not pickup on this when I did my previous postings on the new Map report item in Reporting Services 2008 R2, but yesterday I noticed that Lake Superior was not being represented on the map


Does something look a bit odd?  The rest of the states look pretty good.  So why does this not look like what I am used to seeing?


So I thought this was a bug with the map of the entire USA.  I go down to the individual states and this is the shape for Michigan


Hmmm… What is that?  I thought Michigan looked like the following


If you look at Bing and Goolge maps you will see the outline polygon shapes and it does align with what is setup with the map in SSRS





And you will notice that the shapes of Minnesota and Wisconsin are a tad bit different as well and this is also represented in the SSRS map.

I go ahead and pull down the spatial data for the states (for the steps on how to do this check out Sean Boon’s (Blog | Twitter) response in the forums here – How do I do this? R2 Map Feature) and create a map using the spatial data and here is what we get


Seems really odd to me.  It does not appear to be a bug because it is used this way.  Really confusing to me though and not sure what the reasoning is for this representation.  There is a connect bug for this, but as I stated it seems like it is correct – SSRS 2008 R2 USA By State – Michigan has incorrect shape.  So what do you think?  Anyone know why this is?

I did find this little blurb here and maybe this is the answer, not sure:

In addition to a common database structure, the conterminous U.S. state databases (the contiguous 48 states) have been fitted to a set of standard state boundaries so that, when states are merged, they match without slivers or overlap. No attempt has been made to reconcile differences in mapping across state boundaries.

Just kind of odd to me, but I guess this is what we have to work with.  The shapefiles for  Alaska and Hawaii are accurate – would be odd if they were not and just got merged like the rest of the 48.

UPDATE (10/17/2010): I went ahead and loaded the spatial data for Michigan for the counties and zip 5 data and I ended up with these results.  One actually provides the results we would expect to see

County representation:


Zip 5 representation:


Interesting.  State and county polygon data do not provide the layout we would expect, but referencing the zip codes does provide a familiar view of Michigan.

Posted in Reporting Services | Tagged: , | 1 Comment »

Reporting Services Bing Map – Unable to connect to the remote server

Posted by denglishbi on August 1, 2010

A new feature that is now available in Reporting Services 2008 R2 is the Map report item.  Along with this report item is the ability to display Bing maps.  This is a very neat feature and a very interesting way to visually display information to end-users leveraging their data.  Here is an example that Robert Bruckner put together last year that he made available – RS Maps with Spatial Data and Bing Maps

Adv Works Cust LA Map 

Once you have this created within BIDS or Report Builder 3.0 you are going to want to share this with your end-users, so you will need to deploy this to the server.  Once you deploy it to the server you test it out and you end up with this (not in all cases, but this could potentially happen in your environment)

Adv Works Bing Map Error

Bing Map error: “Unable to connect to the remote server

Hmmm, what is going on?  The report seems to be running, but the map is not being displayed.  After taking a look around it turned out that a proxy was being used, so some additional configurations were needed on the Report Server in order for the Bing map integration to work – Proxy Settings for Map Controls with Bing Maps.

So I went into the Report Server web.config file, which is located in the ReportServer install folder – see image below for the path) and added the proxy information (NOTE: always make sure you make a backup copy of a config file before making a change and utilize a basic text editor like notepad, not an RTF editor like wordpad).

SSRS web.config file

         <defaultProxy enabled=”true” >
             <proxy bypassonlocal=”True” proxyaddress=”http://myproxy:port”  />

Once I had this information added (you will need to replace the ‘myproxy:port: information with the valid information for your environment) I went back and refreshed the report and we were back in business.  No reboot or restarting of the service needed, the Bing map was able to be displayed and everything was back to normal.

Posted in Reporting Services | Tagged: , | 2 Comments »

Mapping data with Report Builder 3.0 (SSRS 2008 R2) Case Sensitive

Posted by denglishbi on December 9, 2009

This week I received an email from a BI Developer in New York in regards to a posting I did earlier this year, Mapping data with SSRS 2008 R2, wondering why the map was not working with their dataset.  I took a look at their screenshots and based on what I saw I quickly realized that the Map Gallery option with the new Map report item is case sensitive.  Let’s take a quick look at what they were seeing with the latest November CTP release of Report Builder 3.0:

image image image
New startup screen in Report Builder 3.0 We will be using the Map gallery option I zoomed in on the map a bit and dragged it around a bit to reposition it
image image image
Select the visualization type Going to choose option to add a new dataset since I don’t have one setup already that can be shared (new option in SSRS 2008 R2) I will be using the AdventureWorks2008 database for this demo once again
image image image
I created a new version of the view vSalesPerson and used the UCASE function for the StateProvinceName data (you could use UPPER also) I map the fields STATENAME to StateProvinceName and you can see the difference now how the spatial data is Proper Case compared to our dataset which is all Upper Case Select the theme, field to visualize (value in the dataset), the color rule, and then click Finish
image image image
Run the report to preview it and ….  nothing has been mapped …  the relationship did not work because the mapping is case sensitive Go back to the dataset selection and use the original view Sales.vSalesPerson. Notice that the data is all Proper Case Run the report and guess what … we have a winner!


You can download the latest version of Report Builder 3.0 and the rest of the SQL Server 2008 R2 add-ons from the Microsoft® SQL Server® 2008 R2 November Community Technology Preview Feature Pack.


I have posted this in the SQL Server Connect as a bug, so we will see what Microsoft has to say – SSRS 2008 R2 Map Gallery Case Sensitive.  So for the time being make sure that your data matches what is expected in the spatial dataset…an exact match.  This would mean that your abbreviated formats should all be upper case.

UPDATE (12/11/2009): got an update on the Connect posting I did and the response from Microsoft is….are you ready….

"Thank you for reporting this issue. The matching on the binding fields is case sensitive by design. We do not have plans to change this."

so you have been warned.  Make sure that if you are using this mapping option that you have your data in alignment with what is expected.

Posted in Reporting Services | Tagged: , | 1 Comment »

Mapping data with SSRS 2008 R2

Posted by denglishbi on August 12, 2009

So I was testing out the new Map Report Item in SSRS 2008 R2 August CTP last night and this morning.  Still trying to get a feel for the new data visualization, but here are a couple of reports I put together using some data from the AdventureWorks2008 database.

image image

The first report is using the Map Gallery option and I am using the StateProvinceName field mapped to the STATENAME and displaying the 2003 Sales information.  The second one is using a SQL Server Spatial Query using the SpatialLocation information in the Address table for the Sales people which I added into the Sales.vSalesPerson view and it is using bubbles to display the 2003 Sales information on a Bing map with the Hybrid map option.  The nice thing about bubbles is that they provide you a way to evaluate two measures at once if you wanted – one by the size and the other by the color of the bubble.

Here are a few screenshots of the steps that I did to create these two reports:

image image  image
Select source for the spatial data Arrange the map and add a Bing map image reference if you want Select Visualization option
image image image
Match the spatial field to dataset field Select Theme, Field to visualize, and color rule Load the report (new loading visualization in SSRS 2008 R2)
image image image
Select source for the spatial data (going to utilize the spatial data in the SQL Server table) Select dataset for source of spatial data Choose spatial field layer type and map options
image image image
Picked the option to add Bing Map and changed type to Hybrid (options are Road, Ariel, or Hybrid) Select the visualization type Choose the analytical dataset
Specify the Theme and Bubble options (size or color) and the field(s) to visualize    

That is a quick run through of two of the spatial source options with the Map Report Item.  I will have to work with these a little more and come up with some more interesting layouts and visualizations using this new Report Item.

UPDATE (12/12/2009): If you are going to be using the Map Gallery option you should know that this is case sensitive and that your analytical data needs to match exactly to the geographical data – Mapping data with Report Builder 3.0 (SSRS 2008 R2) Case Sensitive.

Posted in Reporting Services | Tagged: , | 2 Comments »