Creating a Heat Map/Color Analytical Map in SQL Reporting Services

In this walkthrough we’ll be creating a heat map/color analytical map in reporting services to display which states in the US have the highest amount of sales. In this example I’ll be using the AdventureWorksDW2012 database for sample data. The AdventureWorks sample database can be downloaded from CodePlex at http://msftdbprodsamples.codeplex.com/releases/view/55330. Simply download the AdventureWorksDW2012_Data.mdf and attach it to your SQL Server instance if you’d like to recreate this example in your own environment. In this example we’ll complete the following steps using report builder.

  1. Create a Data Source that points to our Adventure Works database
  2. Create a Dataset to pull our state and sales example data
  3. Create a map that visually displays which states have the highest sales
  4. Format map key to show currency

Create a Data Source that points to our Adventure Works database

The first thing we need to do in our report is create a data source to tell our report where we’re getting data from. With report builder open, complete the following steps to create a data source for your report:

  1. In the “Report Data” window, right click on the Data Sources folder and then click “Add Data Source”
  2. Name the Data Source “AdventureWorks”
  3. Select “Use a connection embedded in my report”
  4. Select “Microsoft SQL Server” for the connection type
  5. Click the “Build…” button on the Data Source Properties window
  6. Enter the name of your SQL Server or named SQL instance in the “Server name” textbox
  7. Select the database you wish to connect to (in our case the AdventureWorksDW2012 DB)
  8. Click “OK” to return to the “Data Source Properties” page
  9. Click “Test Connection” to ensure your connection is properly setup. If the connection does not succeed, you may need to configure the credentials that your report uses to connect to the database. Please see my blog post here about how to configure credentials
  10. Click “OK” on the “Data Source Properties” window to finish creating the data source

Create a Dataset to pull our state and sales example data

Next we want to create the query/dataset that will get the data for our map. To create the dataset follow these steps:

  1. In the “Report Data” window, right click on the Datasets folder and then click “Add Dataset”
  2. Name the dataset “SalesByState” without any spaces
  3. Select “Use a dataset embedded in my report”
  4. For “Data source” select “AdventureWorks” from the available dropdown
  5. For the “Query type” select “Text”
  6. Paste the following SQL into the query window:

    SELECT DimGeography.StateProvinceName, SUM(FactInternetSales.SalesAmount) AS Sum_SalesAmount FROM DimCustomer INNER JOIN FactInternetSales ON DimCustomer.CustomerKey = FactInternetSales.CustomerKey INNER JOIN DimGeography ON DimCustomer.GeographyKey = DimGeography.GeographyKey WHERE DimGeography.CountryRegionCode = N'US' GROUP BY DimGeography.StateProvinceName

  7. Click “OK” on the “Dataset Properties” window to finish creating the dataset

    clip_image001

When finished you should see your new “SalesByState” dataset in the Report Data window. When expanded the dataset should show that it has two columns named “StateProvinceName” and “Sum_SalesAmount” respectively as shown below.

clip_image002

Create a map that visually displays which states have the highest sales

Now that we’ve got our data in order, it time to build or map. In this example we’re going to use the map wizard to build our map for us, however if you’re familiar with ESRI shapefiles you can also build your own maps. To create a map using the map wizard follow these steps:

  1. In report builder click on the “Insert” tab at the top left of the screen
  2. In the “Data Visualizations” grouping select the dropdown arrow under “Map” and then select “Map Wizard”
  3. Select the “Map gallery” radio button if it’s not selected by default
  4. In the “Map Gallery” window select “USA by State Inset” and then click “Next”

    clip_image004
  5. The 2nd screen of the wizard has options for adjusting the positioning, zoom (both of which can be adjusted later). It also has options for adjusting the resolution and even adding a map Bing maps overlay onto your map. In our example we’re just going to accepts the defaults and click "Next".

    clip_image006
  6. On the 3rd screen of the wizard we get to choose the visualization type of our map. In our case we want each state within the map to change color based on the total sales number for the state. This can be accomplished with the color analytical map. Select ”Color Analytical Map” then click the “Next” button.

    clip_image008
  7. On the 4th screen of the wizard, ensure “Choose an existing dataset in this report or a shared dataset” is selected and then select the “SalesByState” data source and click “Next"

    clip_image010
  8. On the 5th screen of the wizard you need to map which data goes with which state shape. This is done by using the first section of the screen. The 2nd section of the screen (labeled “Spatial data”) shows examples of valid matching data, while the 3rd section (labeled “Analytical data”) contains all of the data currently in your dataset. To properly map the data do the following
    1. Place a check in the “Match Fields” column next to “STATENAME” (since we have the full state name in our data set)
    2. Select “StateProvinceName” from the “Analytical Dataset Fields” column for the same row.
    3. Click “Next”

      clip_image012
  9. On the 6th screen of the wizard, the “Field to visualize” dropdown determines what numerical value will drive the color of each state. In our case this will be the Sum_SalesAmount column from our dataset. if you click the dropdown for this field you will notice that there has been an additional “Sum” added to the column name. The wizard will always add this extra and re-aggregate the data by state for the map. This is a nice feature if you had a dataset where state wasn’t the lowest level of granularity in your dataset. This screen also contains options for changing the color ranges of your map as well as an option to display labels on each state that are sourced from your dataset. To finish our map complete the following steps:
    1. In the “Field to visualize” dropdown select “[Sum(Sum_SalesAmount)]”
    2. In the “Color rule” dropdown select “White-Blue”
    3. Click “Finish”

Once we click finish we should now be able to see our new map in the body of our report. Several of the items in the map can be individual selected to have their properties adjusted, including individual state shapes. If you click on the square that the state shapes are located in you should be able to adjust the zoom by using your mouse wheel, as well as adjust the position of the entire map by left clicking on an open space within the square and dragging. In the example below I’ve retitled the map by simply double clicking on the “Map Title” and “Title” textbox near the top and top left of the map and typing my desired title. I’ve also removed the 2 keys for near the bottom of the report to give me a better view of the map as a whole by left clicking on them and then pressing the delete key.

clip_image013

It’s pretty easy to see that our largest amount of sales are in California and Washington.

Format map key to show currency

Notice how the “Sales Amount in Dollars” isn’t showing up as currency. Formatting this area as currency is possible, but it’s definitely a little tricky to do if you don’t know where to go. I remember it taking me longer than I care to publically admit to figure out how to format this area the first time I played with maps. Not sure why though, when it’s as obvious as these simple steps:

  1. In Report Builder while in design mode click on the report item until the “Map Layers” window appears. (this usually takes 2 click if you had something else selected prior to clicking on the map)
  2. From the “Map Layers” window look for a creepy looking eyeball that’s just glaring at you like you have no right to even want your map key formatted as currency….let alone GET it formatted as currency. Click the little dropdown next to that creepy eye.
  3. From the resulting menu select “Polygon Color Rule”

    clip_image015
  4. From the resulting “Map Color Rules Properties” window select “Legend” from the available options on the left
  5. Locate the “Legend text” textbox which should contain something like “#FROMVALUE{N0} - #TOVALUE{N0}”. To change our formatting from numeric to currency we’ll need to change the {N0} parts of this to {C0}. The number after C determines the number of decimal places in the event you want to change that value as well while you’re in here. Ultimately we’ll want end up with something like this: “#FROMVALUE{C0} - #TOVALUE{C0}” (without the quotes)

    clip_image016
  6. Click “OK”

You should see your change immediately, even when in the design area. Now wasn’t that easy?

clip_image017

It should be noted that you can also customize the color distribution for different ranges or the number of ranges used and what their thresholds are from the same window. By clicking on the “Distribution” menu. You can also access many of the original settings we configured plus several additional ones by digging around in the options that appear under that creepy eye.

I think that about wraps it up for maps. I might do another post on maps later that shows how you can make individual map items clickable so you can navigate the user to a detail report for each state. But that’s a story for another time. Until then, happy reporting!

Leave a Reply

Your email address will not be published. Required fields are marked *


1 + five =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>