{"id":1437,"date":"2012-11-13T00:00:00","date_gmt":"2012-11-13T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/report-building-3-0-adding-maps-to-your-reports\/"},"modified":"2021-05-17T18:33:19","modified_gmt":"2021-05-17T18:33:19","slug":"report-building-3-0-adding-maps-to-your-reports","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/report-building-3-0-adding-maps-to-your-reports\/","title":{"rendered":"Report Building 3.0: Adding Maps to Your Reports"},"content":{"rendered":"
\n

In the first three articles of the Report Builder 3.0 series (article 1<\/a> | article 2<\/a> | article 3<\/a>), you learned how to add tables and charts to a report and configure their properties. In this article, you’ll learn how to add a map, one of the most interesting visualizations available in Report Builder.<\/p>\n

A map is made up of one or more layers that display spatial and analytical data. The spatial data is what you’d normally think of as the map itself, such as a country’s outline or the outline of the states or provinces within that country. The analytical data provides meaning to the spatial data. For example, you might have a map whose spatial data provides an outline of Canada and its provinces and whose analytical data breaks down the population demographics for each province.<\/p>\n

In this article, we’ll create a map of the United States that includes the locations and sales totals for sales representatives in the AdventureWorks bicycle company (Microsoft’s fictitious company used to provide sample SQL Server data). If you want to create this map on your own system, you’ll need to create a Report Builder report and add a data source and dataset to the report that retrieve AdventureWorks data.<\/p>\n

On my system, I created a data source that connects to the AdventureWorks2012<\/strong> database on a local instance of SQL Server 2012. I named the data source AdventureWorks<\/strong>. I then created a dataset that uses the AdventureWorks<\/strong> data source to retrieve the necessary sales data. I named the dataset SalesData<\/strong>. Finally, I configured the dataset with the following T-SQL query:<\/p>\n

SELECT\r\n\u00a0\u00a0p.FirstName,\r\n\u00a0\u00a0p.LastName,\r\n\u00a0\u00a0p.City,\r\n\u00a0\u00a0RTRIM(sp.StateProvinceCode) AS StateCode,\r\n\u00a0\u00a0p.SalesLastYear,\r\n\u00a0\u00a0a.SpatialLocation\r\nFROM\r\n\u00a0\u00a0Sales.vSalesPerson p\r\n\u00a0\u00a0INNER JOIN Person.BusinessEntityAddress ea\r\n\u00a0\u00a0\u00a0\u00a0ON p.BusinessEntityID = ea.BusinessEntityID\r\n\u00a0\u00a0INNER JOIN Person.Address a\r\n\u00a0\u00a0\u00a0\u00a0ON ea.AddressID = a.AddressID\r\n\u00a0\u00a0INNER JOIN Person.StateProvince sp\r\n\u00a0\u00a0\u00a0\u00a0ON a.StateProvinceID = sp.StateProvinceID\r\nWHERE\r\nCountryRegionName = 'United States'\r\nAND SalesLastYear > 0; <\/pre>\n
The SELECT<\/strong> statement retrieves sales and location data for each sales representative. Notice that I use the RTRIM<\/strong> function to remove trailing spaces from the state codes. We’ll be using the state codes to map our analytical data to spatial data, which has its own state codes associated with it. The codes must match exactly. How we use the data will become clearer as we work though the exercise.<\/p>\n
After you’ve set up your environment, you’re ready to add the map. As mentioned above, a map is made up of one or more layers. Each layer is configured as one of the following types:<\/p>\n