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
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
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
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
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
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
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.
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
Next create a dataset and use the parameter that we created (above) as shown below.
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
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:
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
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
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
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
In the Map Polygon Properties dialog box, select the Sum(Amount) in the Tooltip combo-box as shown below.
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...
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.
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
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
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
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
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.