I was going through some of my old documents and found this article on map reports which I had written for a SSRS book (which didn’t go through because of some time related issues). I thought it might be useful to those who are looking to explore map reports in SSRS. So here it goes:-
Map reports have been a part of SQL Server Reporting Services from SSRS 2008 R2. However, they are usually a grey box for developers and hence, not been used to their full potential in most of the organizations. This chapter will introduce you to the basic concepts involved in map reports and then take you through some of the common scenarios involved while building map reports.
1) Geometry and Geography: These are the two spatial data types that are available in SQL Server 2008 and above. Geometry imagines that the world is a flat rectangle while Geography takes the curvature of the spherical earth into account. Depending on your application, you can choose between the two data types for your spatial purposes. Geography data types would be more precise over long distances as the earth is actually spherical, but it would require more overhead in setting up the spatial data as additional parameters have to be provided to account for the roundness of the earth. Geometry on the other hand is easier to setup but might not give you the precision over long distances.
2) Source for Map Reports: SSRS 2008 R2 and above allows two main map sources – ESRI Shapefiles and SQL spatial. Shapefiles are a popular geospatial vector data format which can be used in a lot of geographic information softwares. A shapefile will consist of at least 3 mandatory files (of extension .shp, .shx and .dbf) to store core data and might have some other files for improving performance. Apart from shapefiles, the spatial data can be stored in SQL Server database using the data types geography or geometry and used as a source for SSRS map reports. SSRS also has a predefined set of maps in the Map Gallery, which is essentially spatial data embedded in reports and this can also be used as a source for your map reports.
3) Layer Types in Map Reports: There can be 4 types of layers in SSRS Map reports namely Polygon, Line, Point and Bing Map. Polygon layers are used for displaying outlines of areas like administrative boundaries for countries, states, etc. Line layers are used for displaying the lines for routes, roads, paths, etc. Point layers are usually used for displaying the markers for point locations. The Bing Map layer adds a background of Bing Map tiles that correspond to the spatial data available in the map viewport.
To recreate the scenarios explained in this chapter, you would require the following:-
1. SQL Server 2012. You can download an evaluation copy from http://tinyurl.com/SQL2012Eval
2. AdventureWorksDW2012 Database. You can download it from http://tinyurl.com/Adw2012SqlDB
3. AdventureWorks Multidimensional Models for SQL Server 2012. You can download it from http://tinyurl.com/Adw2012CubeDB
4. Some sample shapefiles for the United Kingdom, which can be downloaded from http://tinyurl.com/ShapefileUK
1. Start by creating a new report project using SQL Server Data Tools (SSDT).
2. Design a new shared data source based on the Microsoft SQL Server provider that connects to the sample AdventureWorksDW2012 database and name it DS_SQL.
3. Design a new shared data source based on the Microsoft SQL Server Analysis Services provider that connects to the sample AdventureWorks Multidimensional database and name it DS_SSAS.
I) Creating Map Reports from Shapefiles
This section will demonstrate how to create a map report from a shapefile and visualize it with the measures present in your relational or multidimensional database.
1. Add a new report to the project and name it ‘1 Report from Shapefiles’
2. Create a new data source which points to the shared data source, DS_SQL
3. Design a dataset called DST_Analytical_UK_C and populate it with the SQL query below
SELECT 'England' AS Country, 500 AS Sales
SELECT 'N Ireland' AS Country, 300 AS Sales
SELECT 'Scotland' AS Country, 350 AS Sales
SELECT 'Wales' AS Country, 200 AS Sales
4. Drag and drop a map report item from the toolbox to open the Map Wizard. In the first screen, select ESRI Shapefile as the source of spatial data. Browse to the folder which has the saved maps for the sample UK shapefiles and select GBR_adm1.shp. Click on Next.
5. You should be able to view the map and change its resolution or crop it in this screen. You can also add a Bing Maps layer in this screen if needed. Click on Next.
6. Choose the Map Visualization in this screen. Since we have chosen a polygon shapefile, we get the options for Basic Map (which just displays the map), Color Analytical Map (which visualizes the data by varying color) and Bubble Map (which visualizes the data by bubbles of varying sizes). The last two options would require a dataset for getting the measure information to visualize data. Choose the Color Analytical option for now and click on Next.
7. In the next screen, select the analytical dataset DST_Analytical_UK_C that we had created in Step 3.
8. In this screen, we will have to specify the match fields in the spatial and analytical data. Select the checkbox for NAME_1 in the spatial dataset field and select Country in the dropdown for analytical dataset field. You can see the selected fields getting highlighted in the window panes below. Click on Next.
9. Choose an appropriate theme and select [sum(sales)] from the dropdown list of the Field to visualize option. Choose the Color Rule as Red-Yellow-Green. You can choose to display the labels also if you want. Click on Finish.
10. Now you can preview the report and see your first map report.
FAQs regarding Map Reports from Shapefiles
1) Why is that some of my map regions are not getting visualized and are being displayed in white color?
This is a clear indicator that the names in the match fields for the spatial and analytical data are not the same. An example is present in our current example where DST_Analytical_UK_C has a field value of ‘N Ireland’ while the shapefile has a field value of ‘Northern Ireland’. You can change the value to Northern Ireland in the analytical query but this might not be a valid scenario in most of the cases where the data comes from the Data Warehouse and we would not want to tamper with the Data Warehouse. In such cases, we can change the field value of the shapefile by going to the design mode for the report, right clicking on the polygon region (in this example, Northern Ireland) in the map report item and selecting Embedded Polygon Properties. Now the NAME_1 field can be changed from ‘Northern Ireland’ to ‘N Ireland’ in the popup window and all the regions should be visualized now.
2) Why is it that I am getting a system exception when I try to deploy some of my map reports?
This happens normally when you are working with shapefiles in excess of 4MB. By default, the maximum size of a rdl report which can be deployed is 4MB. This can be easily changed by replacing <httpRuntime executionTimeout = "9000" /> with <httpRuntime executionTimeout = "9000" maxRequestLength="1000000" /> in the Web.config file which by default is found in C:\Program Files\Microsoft SQL Server\MSRS11.SQL2012\Reporting Services\ReportServer. The reporting services might need to be restarted for the change to be reflected.
3) OK, I have succeeded in deploying my report to the ReportServer. However the report takes a long time to render. Why is that?
This should come as no wonder if you are working with large shapefiles. Most of the shapefiles that are available online exceed 8MBs and it would be prudent to trim them down the size by changing the resolution in the map wizard. For example, I was able to trim down the size for the current report using this method from 8.5MB to less than a MB without much difference to the map outlines.
Another alternative would be to upload the Shapefiles to the ReportServer and then use them as the source for your map reports as is shown in this link http://tinyurl.com/Jason-UploadSHP. This could help in decreasing the rendering time as well as making the shapefiles available for other reports.
4) Why is it that I see only the maps for USA in the Map Gallery? How can I add my own maps there?
Currently, only the maps for USA are there in the Map Gallery. However, you can add your own maps by following the instructions in this link http://tinyurl.com/Jason-AddMapToGallery
5) Where do I find some sample shapefiles to start exploring map reports?
There are plenty of sites which offer free shapefiles for at least the country level administrative boundaries. One of them is http://diva-gis.org. For more detailed level shapefiles (like zip codes for Netherlands), you might need to buy it from some vendors or make your own shapefile using tools like ArcGIS.
II) Creating Map Reports from SQL Spatial Data
This section will demonstrate how to create a map report from spatial data stored in SQL Server and visualize it with the measures present in your database.
1. Create a new table called dbo.GBR_adm2 in the database which corresponds to the shapefile GBR_adm2 in the sample. (Read the FAQs below on how to import spatial data to the database).
2. Add a new report to the project and name it ‘2 Report from SQL Spatial’
3. Create a new data source which points to the shared data source, DS_SQL
4. Design a dataset called DST_Spatial_UK_D and populate it with the SQL query below
SELECT NAME_1, NAME_2, geom
Note that the table dbo.GBR_adm2 contains the equivalent spatial data of the GBR_adm2 shapefile in our samples.
5. Design a dataset called DST_Analytical_UK_D and populate it with the SQL query below
SELECT NAME_1, NAME_2, LEN(NAME_2) AS Sales
6. Drag and drop a map report item from the toolbox to open the Map Wizard. In the first screen, select SQL Server Spatial Query as the source of spatial data. Click on Next.
7. Choose DST_Spatial_UK_D as the spatial dataset in the screen and click on Next.
8. You should be able to view the map and change its resolution or crop it in this screen. You can also add a Bing Maps layer in this screen if needed. Click on Next.
9. For the purpose of exploring a new map visualization, select Bubble Map and click on Next.
10. Choose DST_Analytical_UK_D as the analytical dataset and click on Next.
11. Select NAME_2 as the matching field for both spatial and analytical datasets and click on Next.
12. Select the checkbox for Use bubble sizes to visualize data and choose the Data Field as [Sum(Sales)]. Click on Finish.
13. Now you can preview the report and see the result.
III) Parameterizing Map Reports
Most of the times, it is necessary to add report parameters to filter out the data so that we can focus better on a particular region. This section will provide a walkthrough on how to add parameters to a map report
1. Open the 2 Report from SQL Spatial report and add a new dataset, DST_RP_Country with the query given below.
SELECT DISTINCT NAME_1
2. Add a new report parameter, RP_Country with available values from the DST_RP_Country dataset.
3. Modify the query of the DST_Spatial_UK_D dataset as shown below
SELECT NAME_1, NAME_2, geom
WHERE (NAME_1 = @RP_Country)
4. Modify the query of the DST_Analytical_UK_D dataset as shown below
SELECT NAME_1, NAME_2, LEN(NAME_2) AS Sales
WHERE (NAME_1 = @RP_Country)
5. It would be good idea to add a Bing Map layer to the background so that we can understand the location better. For this, click on the polygon layer to display the Map Layers panel on the right hand side. Then click on the Add Layer icon and select the Tile Layer option from it.
6. Click on Preview and you can see that the map changes with the selected parameter value.
IV) Implementing Drill Down in Map Reports
This section will provide details on how you can implement the drill down feature in map reports. For the same, we will be making use of the two reports that we have already created – 1 Report from Shapefiles as the parent report and 2 Report from SQL Spatial as the child report.
1. Open the report – 1 Report from Shapefiles in design mode and click on map report item to bring the Map Layers panel on the right hand side.
2. Right click on the polygon layer and select the Polygon Properties.
3. Select the Action tab on the property popup window, and select the Go to Report radio button.
4. Specify the 2 Report from SQL Spatial report as the child report and map the RP_Country parameter with the Country field as shown in the image below. Then click on OK.
5. Once this is done, you can preview the report and see that the second report gets displayed with the appropriate filtered data on clicking a particular country in the first report.
V) Aggregating Spatial Data in Map Reports
Just like we aggregate our normal relational data, there might be scenarios where we would like to aggregate spatial data and display it in a report. An example would be to aggregate custom sales regions of an organization at a country level. This example would take you through such a similar implementation.
1. Create a new table dbo.GBR_adm1 with spatial data which corresponds to the shapefile GBR_adm1 in the samples.
2. Add a new column called Sales_Region to the table dbo.GBR_adm1 and give a value of North to Scotland and Ireland, and South to England and Wales.
3. Create a new report 3 Aggregate Spatial Data and add a dataset called DST_Spatial_AggUK with the query below
SELECT SalesRegion, geography::UnionAggregate(geom) AS geom
GROUP BY SalesRegion
Note that geography::UnionAggregate and geometry::UnionAggregate are two new spatial functions introduced in SQL Server 2012 to aggregate geography and geometry data respectively.
4. Now make a Basic Map report with the above dataset and you can see the result below when you preview the report
The same effect can also be done by aggregating the spatial data in the reports instead of in the query.
1. Create a dataset called DST_Spatial_UK with the query below
SELECT SalesRegion, geom
2. Create a Basic Map report with this dataset as the source.
3. Click on the map viewport to bring the Map Layers panel on the right. Click on the polygon layer, and then press F4 to bring the property panel.
4. Modify the VectorData property as =Union(Fields!geom.Value)
5. Click on the Group property and add the SalesRegion field as shown in the image below. Click on OK.
6. Preview the report now and you should see the map of UK aggregated by SalesRegion as in the example above.
FAQs regarding Map Reports from SQL Spatial Data
1) All of my map reports are visualized in a single color even when I have not chosen the Basic Map option. Why is that?
SSRS has some color distribution options which govern the way colors are visualized in your map reports. By default, it is kept to Optimal option which create ranges that automatically adjust distribution to create balanced sub ranges. You might want to change it to EqualInterval option (which creates ranges that divide the data into equal range intervals), EqualDistribution option (which creates ranges that divide that data so that each range has an equal number of items) or Custom option (where you can specify your own number of ranges to control the distribution of values). This can be done by right clicking on the map layer and selecting Layer Color Rule option.
2) How do I import my shapefiles to SQL Server?
Currently, SQL Server does not have a built-in option to import shapefile data to SQL Server. One of the most popular ways to import shapefile data to SQL Server is by using the free tool Shape2SQL. This has a single screen, easy to use GUI and can be downloaded from http://tinyurl.com/Shape2Sql.
There are a few bugs with this tool and from my personal experience, in case you are not able to see the imported table in the database, it might be worthwhile to create the table structure beforehand and then run the software again
3) How do I add my own custom locations as a point layer in map reports?
There are many sites like http://tinyurl.com/FindLatnLong which will return the latitude and longitude if you enter your address. This can be converted to spatial data using the function STPointFromText(). An example is shown below
1. Create a sample table
CREATE TABLE [dbo].[MyHangouts](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Location] [varchar](255) NULL,
[Latitude] [float] NULL,
[Longitude] [float] NULL,
[PostCode] [varchar](20) NULL,
[GeoL] [geography] NULL
) ON [PRIMARY]
2. Insert some data with the latitude and longitude
INSERT INTO [dbo].[MyHangouts] ( [Location], [PostCode], [Latitude], [Longitude] )
VALUES ('Home','NC 28210',35.1537875,-80.8502022),
('The EpiCentre','NC 28202-2538',35.225324,-80.842187),
3. Update the spatial data column using the STPointFromText function
SET [GeoL] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
.4) Can I create my own custom shapes in the map reports?
Yes, you can create your own custom shapes in the map reports as long as you are able to provide the location points for it. You can use online image mapping tools like http://tinyurl.com/ImageToMaps to get the point locations and then use the STPolyFromText() to convert it into spatial data. For eg, I have used a spatial query to get the result below
The query is available in http://tinyurl.com/SpatialLogo if you want to have a further look.
SSRS spatial features take up the charting possibilities to a whole new level. In addition to the normal geo-spatial reporting capabilities, we can also use the spatial features to basically create any shape or chart that we require and visualize it with data. All we need are the locations of the shape as well as a little bit of creativity.