Month: September 2013

How to make Map Reports in SSRS

How to make Map Reports in SSRS

September 29, 2013

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.

Basic Concepts

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.

Prerequisites To recreate the scenarios explained in this chapter, you would require the following:-

1. SQL Server 2012. You can download an evaluation copy from

2. AdventureWorksDW2012 Database. You can download it from

3. AdventureWorks Multidimensional Models for SQL Server 2012. You can download it from

4. Some sample shapefiles for the United Kingdom, which can be downloaded from

Initial Setup

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 FilesMicrosoft SQL ServerMSRS11.SQL2012Reporting ServicesReportServer. 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 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

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


FROM dbo.GBR_adm2

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


FROM dbo.GBR_adm2

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.



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



WHERE (NAME_1 = @RP_Country)

4. Modify the query of the DST_Analytical_UK_D dataset as shown below


FROM dbo.GBR_adm2

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


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


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

(‘Office’,’NC 28211′,35.1493742,-80.8272008),

(‘PetSmart’,’NC 28217′,35.1385661,-80.8764557),

(‘The EpiCentre’,’NC 28202-2538′,35.225324,-80.842187),

(‘Library’,’NC 28211′,35.1513557,-80.8225257)

3. Update the spatial data column using the STPointFromText function

UPDATE [MyHangouts]

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 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 if you want to have a further look. Conclusion 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.

Posted by SQLJason, 9 comments
Power Map Preview – Exploring the NEW features!

Power Map Preview – Exploring the NEW features!

September 11, 2013

Woot! Microsoft just announced the pre-release availability of the refreshed builds for Power Map with some really cool new features and boy, I must say that I am PUMPED! For folks that know me, I have been doing quite a number of presentations on Power Map (and geospatial analytics in general) and there were a couple of features that I really really wished for in Power Map. Well, looks like the Power Map team heard my requests and I feel this release is a big step forward.

Power Map preview for Excel - new features

First of all, here are some useful links (including the download link for the refreshed bits):-

Now let’s see some of the new features that has come up since my last post on Power Map.

I) Recognizing geographic data and Plotting them on launching Power Map On launching Power Map, the columns with geographic data are automatically recognized and plotted. For example, I had some data of the population of North Carolina by zip code as shown below

sample data

Now when I launched Power Map, this is what I saw

recognizes geo data and plots them

The best part is that the geographic columns seemed to be recognized correctly even if the they are named something other than City / State / Zip Code, etc. For eg, I renamed the City to CVDS and still was able to see it correctly mapped to City.

works with different column names also

And anyways, you still have the option to change it from the drop down. All in all, great feature and saves me a couple of unnecessary clicks.

II) Support for Flat Map

I know, I know, all of you would be thinking – what’s so special about this? After all, what made Power Map unique was that it had the ability to do 3-D geospatial analysis. Well, that is pretty cool but there are certain times when only a flat map would do. For eg, when you are looking at a worldwide data like population by countries, you want to see both sides of the globe. Earlier in Power Map, it used to look like shown below:-

3D map - power map

We could see only one half of the globe and had to rotate to see the other half. Now we have this icon to convert the 3D map to a flat map (and back to 3D if required!)

Flat map - Power Map

This is REALLY cool! Open-mouthed smile

III) Support for Regions Visualization (Chloropeth Mapping)

Now this is the most awesome enhancement according to me – support for chloropeth mapping or shading a geographical area (and I had actually requested as a Connect suggestion during the beta release of GeoFlow). Let me show you how the visualization looks for the population data for North Carolina

Region Visualization - Power Map

Best part is that it is damn right simple – just change the visualization from Column to Region (and yes, that also means that we have a fourth visualization type now – apart from Column, Bubble and HeatMap)

new visualization type in Power Map - Region

The Region visualization is only present at a Country / Region, State / Province, County or Zip / Postal Code level (which means City is not available) and is available across all countries (not just the USA). If you try to use a geographical level which is not one of the above, you will receive the following error and the chart type will be changed to Column.

Power Map - Region charts work with Country/Region, State/Province, County, or Zip/Postal Code data

I LOVE this feature and more than that, adore the way how simple it is for users to geocode at that level (no need to bring in complex shapefiles to geocode county/zip code level data like in SSRS!). I can already see a lot of use for it.

IV) Change Color for Data Series

Now this is another simple but very useful new feature – the ability to change colors for data series. You can see this new option in the Layer options.

change color for data series

You can also see that the display options of the Themes button has changed and now the colors also show up.


V) Exporting Tours as Videos

A question that consistently makes the rounds when I present on Power Map is – “How do I “unlock” the tours from the Excel workbook? Is it possible to share it through any other medium?” Well, the answer has been No until now. The current build allows you to export your tours as a video and now this can be shared across all your channels. For creating videos, there is a new Create Video button on the ribbon and then you can select the video quality also.


To demonstrate the feature, I have exported a tour from one of my recent presentations. This tour is around 5 mins long and investigates the myth – Are Refugees flooding Industrialized countries? It took quite some time for me to export the tour as a video even in the 360p mode, but that is understandable I guess.

Power Map Tour exported as a video


Overall, I feel this is a build with some really awesome features. Apart from the new features I described, there are also some UI changes but these changes are simple enough to understand, so I am not going over them. There are still some really basic functionalities that are desired like filtering your data and drilling down on the geographic levels (and I found out at least one bug which I will be filing on Connect soon), but we should not forget that this is not the final version. So I am happy and I would encourage everyone to try out the new build.

Posted by SQLJason, 11 comments
Designing Bullet Graphs in SSRS – Part 3

Designing Bullet Graphs in SSRS – Part 3

September 9, 2013
The concepts in this 3 part blog series are heavily borrowed from the book Information Dashboard Design, authored by the data visualization guru – Stephen Few and is going to concentrate on the following topics:-

I) How to make Bullet Graphs in SSRS
II) Deciding the Color
III) Multiple points of comparison
IV) Alternatives to bar
V) Differentiating measures that need to be read differently
VI) Positive and negative values
VII) Values within a distribution
VIII) Future Projections Designing Bullet Graphs in SSRS - Part 3 In my previous post (Designing Bullet Graphs in SSRS – Part 2), I showed how to make alternatives to bar, how to differentiate measures and how to show positive and negative values in bullet graphs developed in SSRS. This post will touch upon the final 2 topics. VII) Values within a distribution Sometimes the value displayed in a bullet graph needs to be compared to the distribution of an entire set of values. Lets say, a teacher needs to see how individual students performed with respect to the class average. Values within a Distribution - Bullet Graphs For that, I have made a sample dataset shown below sample dataset results L1, L2, L3 and L4 are limits of the three ranges, Score is the score for that student, Avg is the average for that Subject in the class. Now let’s see how to make bullet charts for the data shown above. 1) Drag and drop a table in the report layout. Put Subject field in the Rows and then put a bullet chart in the data field. Select Score as the LinearPointer1 and Avg as LinearPointer2. basic bullet graph 2) Add 3 ranges as shown in step 5 of How to make Bullet Graphs in SSRSDesigning Bullet Graphs in SSRS – Part 1. The upper and lower limits should be L1 & L2, L2 & L3 and L3 & L4 respectively for the three ranges. Add ranges 3) Change the Scale properties to a minimum of 40 and maximum of 100 (we are hardcoding it now but ideally you should use expressions). Also change the Number format to #,00 instead of percentage. Scale properties 4) Change the bar to a cross mark as per the technique shown in Alternatives to BarDesigning Bullet Charts in SSRS – Part 2. Now the result should be similar to the image shown below bullet graphs with the empty spaces 5) Add 2 more ranges so that the empty spaces (for eg, 40 – 60 for English) can be filled. For that, right click on the scale and then select the Add Range option. Add ranges to cover empty spaces 6) Select the Start range as 40 and End range as L1. Set the Placement relative to scale option to Cross. Also, change the Fill color to No color. Range properties Repeat the same for the second range but set the Start range as L4 and End range as 100. Now, with a bit of the formatting techniques described in the previous posts, you should get the desired result. VIII) Future Projection With the normal bullet chart, it is easy to compare a measure with another measure, say Sales against Target. However, there are scenarios where you would want to track a measure against a future target, say YTD Sales against a yearly target. In such scenarios, it would be better to project the measure and then display it in a bullet chart as shown below Future Projection - Bullet graph In the above bullet chart, you can see that the Sales is 65, Target is 245 and Projected is 270. But more importantly, you get an idea that the current sales is on track to meet the target. For creating this type of bullet chart, I am using the following sample dataset sample dataset result Follow the steps below to achieve the same:- 1) Make a bullet chart with Projected as Pointer1 and Target as Pointer2. Change the color for the LinearPointer1 to #608fc7 (or whatever you prefer). Adding projection and target Also, set the scale properties and ranges appropriately. I used 0 to L1, L1 to L2 and L2 to L3 as the Start Range and End Range for the three ranges. 2) Now right click on the scale and add a third pointer. Add new pointer 3) Click on the properties for LinearPointer3 and select the value as Actual. Also change the color appropriately. Ensure that he Type and Width properties are set as shown in the image below (so that the marker is a bar instead of the default rectangle). LinearPointer3 properties Now we should be able to see the desired graph on previewing the report. With this, we have come to the end of the three blog series on bullet charts. Hopefully, this will come in handy when you are faced with a design related questions while developing bullet charts in SSRS.

Posted by SQLJason, 1 comment