Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted March 12, 2012

Creating a Map Report in SSRS - SQL Server 2008 R2

By Arshad Ali

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
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
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
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
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
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
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
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
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
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
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
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
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
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
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)

See all articles by Arshad Ali



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM