Creating Map Drill Down Reports in SSRS – SQL Server 2008 R2

Introduction

SQL Server 2008 R2 introduced several new features in 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.
  • Indicator reports – If you are aware of KPIs, the concept of indicators will not be new to you. As the name implies, indicators have icons to represents trends (up, down or flat), progress state, conditions.
  • Map reports – Map reports allow you to create maps or maps layers to let you visualize data against a geographic background.

 

In my last article, I talked about creating map report based on ESRI shapefile. In this article I will be talking about creating a map report with drill down functionality, so basically I will be creating two reports as follows:

  • Main report based on SQL Server Spatial data for showing countries wide sales
  • Drill down map report based on ESRI shapfile for showing statewise sales for the selected country

The idea behind this article is to demonstrate how you can use a combination of sources of spatial data and create a map drill down report. Also I will explain how you can import ESRI shapefile to SQL Server as a spatial data type and use it in a map report.

Importing ESRI Shapefiles in SQL Server

The first thing that we have to do is to download ESRI shapefiles. You can download countries ESRI shapefile from here and US state ESRI shapefile from here.

Once you have downloaded these ESRI shapefiles, you can directly use it as a spatial data source or you can import it in SQL Server and use it with/within the query. For this example, I am going to import the countries spatial data to SQL Server as a spatial data type and use it in the main map report. For the map drill down report I will be using ESRI shapefile directly (just to demonstrate usage of multiple sources of spatial data).

Though you can write your own tool for importing spatial data from ESRI file to SQL Server, why re-invent the wheel if there is already one available. Morten Nielsen has created a really nice tool for this purpose that can download from here. The User Interface of “Shapefile Uploader for SQL Server 2008” (Shape2Sql.exe) is shown below. You need to select the ESRI shapefile, specify the database connection and the name of the table in which spatial data will be imported, along with some other options.

Shapefile Uploader for SQL Server 2008
Shapefile Uploader for SQL Server 2008

As I said before, for this demonstration I will be importing the countries data from ESRI shapefile to SQL Server and will be using it from there. Once imported you can query these data like this:

Query the data
Query the data

Creating a Map Report from SQL Server Spatial Data Type

For creating a map report, we will be dealing with two sets of data; spatial data and analytical data.  You can create two different data sets for these two sets of data or you can create only one data set with these two sets of data joined together; in that case you need to use OUTER JOIN so that you get all the spatial data irrespective of matching analytical data. This is the query which I have used to get both sets of data from SQL Server:

SELECT ISO2 AS CountryRegionCode,   geom, ISNULL(Amount, 0) AMOUNT FROM
(
      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
)   SpatialData
RIGHT OUTER JOIN   countries C ON C.ISO2   = SpatialData.CountryRegionCode

Outer Join Results
Outer Join Results

Once you are done with creating the data set as described above, the next step is to create a map; this time, since I have spatial data in SQL Server I will choose “SQL Server spatial query” option on the wizard screen as shown below. (For step by step guide on creating map report, please refer to my last article, “Creating map report in SSRS – SQL Server 2008 R2“. Here I will skip some of steps so that I can concentrate more on the topic of the article).

Choose a Source of Spatial Data
Choose a Source of Spatial Data

On the next screen of the wizard, you can choose the spatial data field and Map layer type (more about layer type can be found in my earlier article). Here you can choose to embed the map data in the report. which will make the report bulky but will be faster, and can add a Bing map layer as shown below:

Choose the Spatial Field & Layer Type
Choose the Spatial Field & Layer Type

Creating a Map Drill Down Report from ESRI Shapefile

Once you are done with creating the main map report, next is to create the drill down report. For that, first create a parameter (Country) whose value should be passed from the main report and this report will display the sales report for the states of the selected/passed country value.

Add Parameter
Add Parameter

Specify the name of the parameter and set the visibility property to Hidden. Let the data type be the default value of Text:

Report Parameter Properties
Report Parameter Properties

Next create a dataset and use the parameter that we created (above) as shown below.

Choose a Data Source & Create a Query
Choose a Data Source & Create a Query

Go to the Parameters tab and click on the Add button to add a parameter as shown below.

Choose Query Parameter Values
Choose Query Parameter Values

This is the query that I have used to get state-wide sales information from AdventureWorks2008R2 database:

SELECT   SP.StateProvinceCode,   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
WHERE   ST.CountryRegionCode =   @Country --Value for this parameter will come from   main report
GROUP BY SP.StateProvinceCode
 

For US state this is the result:

Query Results
Query Results

Once you are done with creating the data set, you need to create a map. This time you need to provide the source for the spatial data as ESRI shapefile; as shown below, select the appropriate shapefile.

Select the Appropriate Shapefile
Select the Appropriate Shapefile

On the next screen of the wizard, you need to provide the joining/matching column from both (spatial and analytical) data sets. As you can notice in the image below, I chose STATE_ABBR column from the spatial dataset and StateProvinceCode from the analytical dataset.

Create a Relationship
Create a Relationship

On the next screen of the wizard, you need to specify the theme, field to visualize (in this case summation of sales amount), label to be displayed on the map and any color rule that you want to specify.

Choose Color Theme & Data Visualization
Choose Color Theme & Data Visualization

As I want to see the total sale for the state as a tooltip, I need to make some changes. Select the map, select the map layer, right click on it and click on Polygon Properties as shown below.

Statewise Sales Report
Statewise Sales Report

In the Map Polygon Properties dialog box, select the Sum(Amount) in the Tooltip combo-box as shown below.

Change Polygon Labels & Tooltip Options
Change Polygon Labels & Tooltip Options

The joining/matching columns should match exactly (no leading or trailing spaces). In my case it didn’t match, so I had to use the Trim function to remove leading and trailing spaces; to do that right click on the map layer and click on the Layer Data menu bar as shown below.

Click on Layer Data...
Click on Layer Data…

On the Map Layer Properties dialog box go to the Analytical Data page and use the Trim function as shown below for removing leading and trailing spaces.

Trim Function
Trim Function

That’s all with the drill down report; now you have to go back to main map report and change it to enable for drill down. To do that select the map, select the map layer, right click on this and then click on Polygon Properties menu bar as shown below.

Click on Polygon Properties Menu Bar
Click on Polygon Properties Menu Bar

On the Map Polygon Properties dialog box, click on the Action tab, choose the “Go to report” under Enable as an action, specify the name of the drilldown report and add a parameter to be passed (country) to the drilldown report from the main report as shown below.

Change Action Options
Change Action Options

Preview the Map Drill Down Reports

Once you are done with the above development and configuration, you are now ready to test the map report with drilldown functionality. Preview the main map report, hover the mouse over US (for this demonstration I have taken only US states for simplicity, though it works well for other countries as well) and you will see the total sales for the US as a tooltip. Notice the icon turns into a tiny hand with index finger pointing; clicking on this will take you to the drilldown report as shown next:

World Wide Sales Map Report
World Wide Sales Map Report

Clicking on the country in the main map report will take you to the drilldown report to show the states of the selected country. Again hovering over any state will display the sales amount for the state as a tooltip as shown below.

Statewise Sales Report
Statewise Sales Report

Conclusion

In this article I talked about creating a map report with drill down functionality. The idea behind this article was to demonstrate how you can use a combination of sources of spatial data and create map drill down reports. Also I explained how you can import ESRI shapefile to SQL Server as a spatial data type and use it in a map 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

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles