Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Reporting Services Analytical Marker Maps Including Bing Maps


By:   |   Last Updated: 2015-04-21   |   Comments (5)   |   Related Tips: > Reporting Services Maps

Problem

What can I do with Analytical Marker Maps in SQL Server Reporting Services? How do you implement these types of maps? What visualization options are available with the maps?

Solution

Maps are a wonderful addition to your chart and visualization toolbox for SQL Server Reporting Services (SSRS); they provide rich visualization at the various levels of location all while giving you the ability to add layers on top of predefined maps, shape files or even Bing Maps. In particular we can actually add shape analytic maps onto the map layers to create visualization details which describe a statistical data point related to a localized map value. We can add many layers on top of a base map, however care must be taken to be sure the map does not get "over populated with details" which can reduce clarity and cause confusion. Furthermore, the marker component of our map layer can actually represent the data value either by adjusting the size of the bubble or shape, adjusting the color of the bubble or shape, or by changing the marker design itself to signify the various values. This last item probably sounds confusing, but it just means that a different symbol gets assigned a different range of values, so for instance, a circle could mean 101 to 200, a star could signify 201-300, a diamond could mean 301-400, and so one. Finally, a map could contain different layers with different data to display for each layer. For instance, we could add a base layer that displays all the counties in a particular state in the United States and adjust the background color of the county based on the median income of the county. Then we could add a "bubble or shape" layer, that showed the spending per capita. Bing Maps bring a further layer of visualizations that is not always available in the included maps and shape files.

We will use the Adventure Works databases as the basis for our SSRS Analytical Marker Maps. The 2014 versions of the regular and data warehouse databases, along with the SSAS cube database backups are available on Codeplex: https://msftdbprodsamples.codeplex.com/releases/view/125550. Once you download and install the SQL Server databases, we will subsequently use SQL Server Data Tools for Business Intelligence (SSDT-BI) for Visual Studio 2013 to develop reports. You can download SSDT-BI from: http://www.microsoft.com/en-us/download/details.aspx?id=42313.

Utilizing SSRS Analytical Marker Maps

Using a bubble or analytic map is great way to show multiple data layers or data points all on a single map. For our example we are going to utilize a dataset which list the population of each state in the United States. Furthermore, the dataset contains the spatial data point which represents latitude and longitude of the population center of each state. An example of the dataset is displayed below. Thus our first steps are to start SSDT-BI, create a new SSRS project, and then create a new report.

spatial data set

Next, drag the Map Object from the toolbox onto the report grid.

drag map

Once we drop the Map object onto the report grid, the New Map Layer wizard opens. As shown next, we first are going to create a USA by State layer to drop our data onto. Thus, we first need to select the Map gallery option. Second, the USA by State gallery is selected.

new map layer

Notice in the previous screen print, the state of Alaska is so large that the continental US is somewhat compressed. For our data, we are excluding Alaska, Hawaii, and Puerto Rico, so we crop the map to show just the continental US, as illustrated below and then click next to move to the next screen.

crop map

For this example, we select basic map (we will add the marker analytics as a second layer); this current layer is used to just display the outline map for us to use for our additional layers.

new map layer visualization

As shown next, we finish the wizard, by selecting a Theme, Ocean for this example. Next we uncheck the Single color map check box to make it easier to see the outline of each state. Last, we click finish.

map color

Finally, we finish up with a few visual housekeeping items, such as adding a title, moving the legend to the bottom of the map, and resizing the object on our grid. You are probably thinking, wow, we added a map to a report, so what?

basic map layer

Out next steps actually add the analytic data points to the map to really show off its power. Thus in the map layer area, we click on the New Map Layer wizard button as shown next.

new map layer button

This button opens a wizard similar to the previous map wizard; however in this instance we are going to select the SQL Server Spatial Query as shown below. Spatial data is generally Geography or Geometry data types.

spatial data query

Next, we select our dataset with the spatial data for the population by state.

spatial data select

On the next screen we select the field which contains the spatial data and also the Layer type, Point in this example. These selections are shown in the first screen print below. In the second screen print, you see that we zoom in on the map to show just the continental United States.

spatial data point

spatial data point resize map

As shown next, we now select the Analytical Marker Map option.

analytic type

We again select the Population as the basis for our Analytical markers, as illustrated below.

select analytic dataset

Finally, as noted below, we select a Theme for our layer, define the color rule (color of the marker), select the field that will be analyzed (population), and finally define what marker will be used.

market color

Now by reviewing the below screen prints, we see a pretty informative map. First each "wedge" simply shows us where the geographical center of the population resides. Second, the color of the marker signifies the range where the state population falls within.

marker color map

One item that often comes up when dealing with layers is that the marker level will "fall behind" the background map, the 48 continental states layer in our example. In those cases we may need to bring our Analytical marker layer to the front most position. We complete this task by clicking on the blue down arrow to the right of the layer name. Next select Layout > Bring to Front. The order the layers appear in the list notates the order of the layering from top to bottom.

bring layer to front

Let us use the size of the marker instead of the color of the marker to define the population size. We can make this change by again selecting the blue down arrow next to the Analytic Point layer and then choosing the Layer Wizard.

layer wizard

We then scroll through the screens until we get to the Choose color theme and data visualization window on the wizard. Next we select Use marker size to visualize data, again select population for the Data field, and finally select a Marker. As shown below, we are using a Star for the marker now.

market size select

The marker size version of the map is shown below. I really like this version as it makes the population sizes stand out.

market size map

Our last variation uses different marker symbols to define the population range. For this selection, as illustrated below, we only select the data field to use, population in our example.

market type

Our final version of the map is shown below; notice how each symbol defines a different population range.

marker type map

We can enhance our map by actually adding a Bing Map layer to underlay the Point and 48 states map ( polygon layer ) layers.

Bing layer

The Bing Map adds some perspective for the map along with some labels. You will also notice that the color of the star marker was changed to red; the color can be changed by going to the Point Properties and then going to Fill window to select a color.

bing map

Conclusion

Analytical marker and bubble maps provide rich mapping functionality to display not only a map, but also related data values. When dealing with maps, care must be taken to understand what mapping and data values are selected for what layer. For this tip, we described adding an analytic marker layers on top of a base map of the United States; however, bubble markers provide similar functionality. For analytical markers, we can display the data either by the marker color, the marker size, or the marker symbol. However in order to use a marker, the spatial mapping type must be selected in the layer wizard. Once the spatial type is selected, we can define the spatial data either as a point, a polygon, or a line and then define what data field should be used as the basis for the marker. Finally, a Bing map under layer can easily be added to a map to provide additional perspective for the analytical markers.

Next Steps


Last Updated: 2015-04-21


get scripts

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, March 17, 2016 - 9:57:36 AM - Scott Murray Back To Top

Sounds like that does not work... that was my only thought on changing the marker, but it must not be doable to customize each marker. Sorry


Wednesday, March 16, 2016 - 6:39:37 PM - Anil Back To Top

Thanks Scott for the quick response.
Yes, I do already have the markers (images) stored in the database table for each data point. I have tried few different things but not able to use those images to visualize datapoints in the map. I am using marker type as Image (image source *Database* and using the image field). But doing so would use one image for all the data points in the map. I believe it is using the first image from the database table and believe me I am not telling it to find the first image anywhere.  Can you point me to the direction of how I can do this ?

Thanks for your time and assistance.

 


Wednesday, March 16, 2016 - 5:52:33 PM - scott murray Back To Top

 

Anil... that becomes more difficult if you want a customer marker for each data point; in that case you need either load the markers into the DB or use some sort of reference to the image stored on a webserver with a link to that that file.


Wednesday, March 16, 2016 - 5:18:40 PM - Anil Back To Top

Its very helpful. Thanks for the post.
I have a question though, I have unique images for each spatial point stored in the database. How can I use those images to represent each spatial point on the map ? Any help or ideas would be really appreciated.

Thanks a lot.

 


Saturday, April 25, 2015 - 11:02:51 AM - Sanjay Soni Back To Top

 

Yery helpful....And VerY nice


Learn more about SQL Server tools