SQL Server 2008 R2 has really nice features in Reporting Services where you can work with maps and for example plot markers on a map to get a geographical overview of whatever you like. There are many tutorials out there how to get started on this, but one of the things that I haven’t found much information about is about the following:
I had the situation where I wanted to show customers per country on a map. So I created a report with a parameter @CountryCode, where the user was able to select the country and all the customers where plotted on a map. The query looked like this:
SELECT CustomerId, Address, City, Country, GeographyCol
WHERE GeographyCol IS NOT NULL
AND Country = @CountryCode
The end result looked like this:
It works like expected, but one slight thing came back from the users that they would like to see as well. For example on the map above, all the customers were plotted on there but as you can see, a big part (or at least the biggest populated part) of Canada is visible on the map as well, but since I filtered the query on the United States only, no data was plotted for Canada.
To solve this issue, I changed the report like this:
– First of all, I got rid of the filter that limits the customer data for the country. This is actually the major disadvantage of this method, that regardless on which country or zoom level you are viewing this report, all data have to be extracted when the report is loaded.
– I added two parameters to select the continent and country. You are completely flexible in how to do this, but to avoid having one big parameter with a list of countries, I created a cascading parameter for the continent and country. The country selection parameter is only used for which country the report will be zoomed in to.
– Coordinates in a country table. In a separate country table, a prerequisite is to have the country coordinates stored. I have used the coordinates from this site (S-latitudes and W-longitudes have to be stored as a negative number).
– I added a few parameters:
CountryLat (type internal): This will be automatically filled through the selected country
CountryLng (type internal): This will be automatically filled through the selected country
ZoomLevel: Since every country is not the same size, you might want to be flexible in the zoom level of the report.
– Extra dataset: The query to fetch the countries needs to include the latitude and longitude. My query looks like this:
SELECT Latitude, Longitude
WHERE (CountryCode IN (@Country))
– In the default values for the CountryLat and CountryLng parameters, link to the dataset created in the previous point.
So a little recap now… at this point, I have three extra parameters and an extra dataset to fill these parameters. I have chosen to make the ZoomLevel parameter a regular parameter since I would like the users to select the zoom level themselves.
The next step is to set some values in the map object of the report. Right-click somewhere on the map and select “Viewport Properties”. Make sure you select the same values as the screenshot below:
For the minimum and maximum values, select the expression button and use the following expressions:
=Parameters!CountryLng.Value – Parameters!ZoomLevel.Value
=Parameters!CountryLng.Value + Parameters!ZoomLevel.Value
=Parameters!CountryLat.Value – Parameters!ZoomLevel.Value
=Parameters!CountryLat.Value + Parameters!ZoomLevel.Value
Basically what you’re setting here is that you define your boundaries of how you view the map. Each value is one corner of the rectangle that you will display and the zoom level determines how many degrees from your center point is visible on the map.
The second important setting that you have to set is in the tab “Center and Zoom”. Set these values according to the screenshot below:
Click OK and run the report again (don’t forget to remove the @CountryCode parameter from your original query!). The report should be extended with all the customers around the US as well. The North-East region in my example looks like this now:
So this method is a great way to not limit the markers on a map to a specific country, but keep in mind that every time this report is executed, all data is retrieved and not filtered to the country that you select.