Introduction
SQL Server 2008 R2 brought several new features into the SSRS (SQL Server Reporting Services) arena. In the data visualization category, we now have three additional ways to display and visualize/analyze data in the reports:
- Sparkline and data bars – Sparkline and data bars are normally used inside tables and matrices to analyze the trend and series and compare them with each other.
- Indicators – If you are aware of KPIs, the concept of indicators is not new to you. As the name implies, indicators have icons to represent trends (up, down or flat), progress state, conditions.
- Maps – It allows you to create maps or maps layers to let you visualize data against a geographic background.
In this article, I am going to demonstrate how you can create map reports to analyze your data against a geographical background and then in the next article I will be talking about creating a map report with drill down functionality.
Understanding Map Reports
There are two concepts that we need to first understand, Map report item and Map layer. Map report item is a new report item in SSRS R2 for map reports whereas Map layer basically displays the geographical background or map element based on spatial information, either from the Map Gallery (inbuilt map reports with map elements), from a SQL Server query returning spatial information or ESRI (Environmental Systems Research Institute, Inc.) shapefiles. A map report can be layered where each layer will display a layer of geographical background.
The types of map layers that can be added to a map report are as follows:
- Polygon – represents geographic areas such as countries, states, or cities, etc.
- Line – represents paths and routes
- Point – represent locations such as stores, cities, or place
- Tile – represents Microsoft Bing maps tiles in map report background
When you add a map report item to the report, the first map layer element is added by default, based on spatial data (although the wizard lets you choose the type of map layer to be part of map) and later on, if needed, additional map layers can be added using the New Map Layer Wizard or Add Map Layer option.
The wizard lets you choose the type of map layer
Creating Map Reports
If you want to analyze your data against a geographical background, the first thing that you need to do is to get the geographical data or spatial information. There are three sources:
- Map Gallery – There are some in built reports with map elements, which you can use as source for spatial information but this is limited to USA states only as of now.
- ESRI shapefiles – Environmental Systems Research Institute, Inc. provides shapefiles, which can be used as source for geographical data or spatial information. These shapefiles are freely available here. In this article, I will be using the shapefiles only for demonstration. I have downloaded the ESRI shapefiles for countries spatial information that I will be using in the demonstration ahead.
- SQL Server Spatial Data – If you have geographical data or spatial information already stored in SQL Server, you can use it directly. In the next article, I will demonstrate how you can export ESRI shapefiles to SQL Server and then use it from SQL Server.
To add a map to your report, drag the Map report item from the Toolbox to the designer area:
Drag the map report item from the Toolbox to the designer area
When you drag a Map report item from the Toolbox to the designer area, a wizard will be launched to specify the map layer detail for the map. On the first page of the New Map Layer wizard, you need to specify the source for the spatial/geographical data that contains set of coordinates that define the map areas. As discussed above, there are three options here, the first Map Gallery is very limited so I will choose the ESRI Shapefile option for this demonstration, and in the next article on map report with drilldown I will explore the third option of getting spatial data from SQL Server query:
Choose a source of spatial data
On the next screen of the wizard, we need to specify the map viewing options, such as map resolution (for example high resolution brings the high quality but is heavy weight in terms of performance), adding a Microsoft Bing Map Layer to the map, etc., as shown below:
Choose spatial data and map view options
On the next page of the wizard, you can specify the map visualization option from the available options (it varies from the type of map layer you chose though and you will have a different screen if you choose a map layer other than Polygon):
Choose map visualization
On the next page of the wizard, you can specify the color theme and data visualization option as shown below. There are already some inbuilt themes, which you can choose or customize your map later as and when needed:
Now if you have already data source and data set added to your report, the wizard will ask you to specify the source for the analytical data. In my case I hadn’t already, created a data source and data set, so I created it now. Then, to specify the analytical data source for the map layer I need to select the map; from the Map Layer window I need to select the map layer for which I need to specify the layer/analytical data and click on the tiny icon on the right most side as shown below. Then I need to click on Layer Data from the menu bar:
Click on Layer Data from the menu bar
The Layer Data (Map Layer Properties) dialog box appears with different pages. The General page will display the spatial data source information with columns and let you change it if you want to:
Change layer and spatial data source options
Click on the Analytical Data page as shown below and specify the source for the data that you want to visualize in the map. Select the data set and add the mapping/relationship of the columns from the spatial data set with the analytical data as shown below:
Select fields to match spatial and analytical data
I want to analyze data from AdventureWorks2008R2 and used the following query:
SELECT SP.CountryRegionCode AS CountryRegionCode, SUM(SOH.SubTotal) Amount FROM Sales.SalesOrderHeader SOH INNER JOIN Person.Address A ON SOH.BillToAddressID = A.AddressID INNER JOIN Person.StateProvince SP ON A.StateProvinceID = SP.StateProvinceID INNER JOIN Sales.SalesTerritory ST ON SP.CountryRegionCode = ST.CountryRegionCode
GROUP BY SP.CountryRegionCode
Query results
That’s all, now you can preview the report; you can also change some properties like label and tooltip, as shown below, to make it look more intuitive:
World wide sales map report
During the preview, I was getting a message that the “number of map point elements exceeds the maximum limit for the map. The remaining points do not appear in the map” and map was not being rendered properly. I increased the values for MaximumTotalPointCount and the MaximumSpatialElementCount property slightly and it worked; refer to this link or this link for more details on the resolution.
You can customize the appearance of the map by changing the Map Layer properties. Go again to the Map Layer property dialog box and check the “Use bubble size to visualize data” to analyze the data by varying bubble size centered on areas:
Check the “Use bubble size to visualize data” option
After changing the above property, you will see different bubbles of varying size representing the analytical data value area wise; a bigger bubble represents a bigger value and a smaller bubble represents a smaller value as shown below:
World wide sales map report
You can even change the color combination of the area by using the predefined combination or manually. To use standard color combination, again go to the Map Layer properties, check “Use polygon colors to visualize data” and then select the already available color combination from the combo-box as shown below:
Change color theme
This is how it will look like after changing the color combination to “Dark-Light” as shown below:
World wide sales map report in Dark-Light
Conclusion
In this article I talked about new data visualization features in SSRS 2008 R2. I talked in detail about map report, how to create a map report based on spatial information from ESRI shapefiles and how to customize it to make it look better/intuitive. In the next article, I am going to talk about creating a map report based on spatial information from SQL Server and creating a map drilldown report.
Resources
What’s New (Reporting Services) in SQL Server 2008 R2
Maps (Report Builder 3.0 and SSRS)
Troubleshooting Reports: Map Reports (Report Builder 3.0 and SSRS)
