This is the continuation of my previous article: Reporting Services 2008 R2: Geospatial Visualization – Part I. Part I discussed the main characteristics related to the Map Report Item, Data sources for spatial data, how to create a map report using shapefiles, and finally add analytical data to it. Our goal in the part is to create two reports as below:
In Part I we created the first report please refer the that article and have the SalesByCountry report in the project, but note that you can create the second report without having the first one. The second report shows the locations of customers and the rank given to them.
The report we are going to create shows how customers are geographically spread in the country. In addition it shows them with markers based on the rank given to them using analytical data. Note that you need AdventureWorks2008R2 database for creating this report. If you do not have it you can download it here.
1. Open Microsoft Business Intelligence Development Studio and open the Report Project we created in the previous article. If you need to create a fresh project, create a new project.
2. Add a new report. Do not use the Report Wizard because it does not support creating maps, instead use Report. Name the report SalesByCustomers.rdl.
3. Open the toolbar and double click on the Map report item. This adds a Map Report Item to the report and opens New Map Layer wizard. The first page of the wizard is for setting the source for the spatial data (please refer to Part I for information regarding sources for spatial data). We created the first report using shapefiles. Now, let’s use the Map Gallery to create the report. Select the first radio button. Under Map Gallery Tree, select USA by State Inset. Your screen would be like below:
Click Next to continue.
4. Leave defaults in Choose spatial data and map view options and click Next.
5. Make sure that Basic Map is selected in Choose map visualization. Note that the other two options require analytical data to be added in the wizard. Click Next.
6. In the Choose color theme and data visualization page, uncheck the Single color map checkbox. If required, change the Theme, or else continue with the Generic theme. Click Finish to close the wizard.
7. As in the first report, delete Distance scale and Color scale. Delete Alaska and Hawaii from the map as well. You can delete these by selecting (just click on them) and hitting the DELETE key. Since we have not set any data to the legend, it will not appear when the report is previewed, hence leave it in the report. Below is the report when it is previewed.
8. Now to add analytical data. Go back to the Designer surface and open the Report Data window.
9. Click the New button in the Report Data and click the Dataset item to create a new dataset. Name the dataset SalesByCustomer and select the Use a dataset embedded in my report radio button. Click the New button next to the Data source drop-down to create a new data source.
10. Create the connection to AdventureWorks2008R2 database and name the data source AdventureWorks2008R2. Copy and paste below query into the Query text area.
SELECT
s.BusinessEntityID
, s.Name
, a.SpatialLocation
, t1.TotalAmount
, T1.Rank
FROM
Sales.Store AS s
INNER JOIN Person.BusinessEntityAddress AS bea
ON s.BusinessEntityID = bea.BusinessEntityID
INNER JOIN Person.AddressType AS at
ON bea.AddressTypeID = at.AddressTypeID
AND at.Name = ‘Main Office’
INNER JOIN Person.Address AS a
ON bea.AddressID = a.AddressID
INNER JOIN Person.StateProvince AS sp
ON a.StateProvinceID = sp.StateProvinceID
INNER JOIN Sales.SalesTerritory AS st
ON sp.TerritoryID = st.TerritoryID
INNER JOIN Person.CountryRegion AS cr
ON st.CountryRegionCode = cr.CountryRegionCode
INNER JOIN (
SELECT
c.StoreID BusinessEntityID
,SUM(s.TotalDue) TotalAmount
,NTILE(5) OVER(ORDER BY SUM(s.TotalDue) DESC) [Rank]
FROM Sales.SalesOrderHeader s
INNER JOIN Sales.Customer c
ON c.CustomerID = s.CustomerID
WHERE c.StoreID IS NOT NULL
GROUP BY c.StoreID
HAVING SUM(s.TotalDue) > 100000
) AS t1
ON t1.BusinessEntityID = s.BusinessEntityID
WHERE
cr.Name = ‘United States’
This query returns customers and their sales amount. The rank is generated using NTILE and set 5 ranks. In order to reduce the number of records, only sales amounts that are greater than 100,000 are taken. The record set is further filtered to ‘United States’. In addition to that, locations of customers are added from Person.Address table. Note that SpatialLocation is stored as a geography type data. When the query is run, you should see a resultset like below.
Click OK to save the dataset.