ANJAN KUMAR DAS: Using Map in SSRS Reports-Part1

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:

Choose color theme and data visualization

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: