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.
11. Double-click the Map Item to see the Map Layer pane. You can see one Polygon Layer named PolygonLayer1 is in Map Layer. In order to show locations of customers, we need geospatial data related to customers’ location. The data we need exists with the analytical dataset we just created. Now we add it to the map. The easiest way of adding this is adding another layer with the wizard. Click on the first icon in the Map Layer which is New Layer Wizard. Note that you can do the same with the second icon which is Add Layer. The only difference between these two is, the second icon does not start a wizard and you have to set the dataset to the layer manually. If you have clicked the first icon, the wizard has opened and, to continue, you need to provide a data source for spatial data. Select the third radio button which is SQL Server spatial query and click on Next.
12. Since we have already added the dataset we need, select the first radio button which is Choose an existing dataset with SQL Server spatial data in this report and select the SalesByCustomer dataset. Your screen should be as below:
Click Next to continue.
13. The window you see now is Choose spatial data and map view options. En sure that SpatialLocation is selected in the Spatial field drop-down and that Point is selected in the Layer type drop-down. Check the Embed map data in this report checkbox and leave the Add a Bing Map layer checkbox unchecked. Click Next.
14. The next screen is for setting the visualization of the data. Remember, we are going to show the ranks of customer within markers, hence the best selection is the Analytical Marker Map. Note that you can also use Bubble Map .
Click Next to continue with the wizard.
15. The next window is for selecting or adding an analytical dataset to the layer. Since the added dataset, SalesByCustomer, contains analytical data as well, we can use this. Note that if you had selected Basic Marker Map in the previous window, you would not get this window. This window appears only for the Bubble Map and the Analytical Marker Map. If you need to load analytical data with a different dataset, you will need to make sure that the new dataset has a matching field which can be used for matching records in the spatial dataset.
Select SalesByCustomer and click Next.
16. The next window is for setting the relationship between the spatial dataset and the analytical dataset. Use BusinessEntityID to match datasets.
Click Next to continue.
17. Leave all checkboxes unchecked in the Choose color them and data visualization window. Select Generic for Theme. Click Finish to complete the wizard. Here is our report now.
18. Now to add marker types to the report. Since we have 5 ranks for customers, we need 5 marker types for showing them. On PointLayer1, click the down-arrow (
) and select the Marker Type Rule… item. This opens the Map Marker Type Rule Properties window.
19. Select the second radio button which is Visualize data by using markers. When this is selected, the Data filed drop-down is shown. Select #Rank as the data field. The list below the drop-down contains the markers for data, in our case, for ranks. Set the first 5 markers as Start, Diamond, Triangle, Rectangle, and Circle. Delete the other markers.
20. Now click on the Legend tab. Remember, we have a legend in our map report, which is has not yet been used. We can use it for showing the meaning of markers. Make sure Legend1 is selected in the Show in this legend drop-down. Change the Legend text to Rank – #TOVALUE{N0}. Note that #FROMVALUE and #TOVALUE are decided by Map Marker Type Rule based on the number of distinct values in the Data field we selected. Since we have only 5 values and we have added 5 markers , the legend will be shown as Rank – 1, Rank – 2, …. and Rank – 3.
Click OK to save the settings. If you preview the report you should see that the customer’s locations are shown with selected markers.
21. The next step is adding colors to markers. As a generic rule, we show the best in green and the worst in red. Go to the designer and double-click the Map Item to see Map Layers. Click the down-arrow () on PolygonLayer1 and select Point Color Rule… item. Once the Map Color Rules Properties window is open, select the third radio button – Visualize data by using color ranges. Since we are going to apply color rules on Rank, select #Rank as Data filed. Set the start color as green, middle color as Yellow, and end color as red.
Go to the Legend and make sure that no legend is selected. If you need you can have it but having another legend for a common rule will not be useful. Click OK to save the settings.
22. Let’s make few more modifications. Double-click the Map Title and change it to Sales by Customers – US.
23. Right-click on the Legend and get its properties. Leave the show legend outside the viewpoint checkbox unchecked . If required, change the Position. Select Wide table from the Legend layout drop-down, to widen the legend layout. Click OK to save the settings. Now we can change the title for the legend . Double-click on it and change it to Customer Rank.
24. It would be optimal to have tooltips for markers, showing the name of the customer and total amount. In order to add tooltips, click the down-arrow () on the PolygonLayer1 and select Point Properties. Set the below expression to Tooltip drop-down box.
=Fields!Name.Value & ” – ” & format(Fields!TotalAmount.Value, “C0”)
Click OK to save the settings.
25. Now preview the report it should look as below:
26. One more thing to be done is to complete both reports. We have to set an action in the first report to open this report. Open the first report which is SalesBy Country in the designer and double-click the Map Item. On PolygonLayer1, click the down-arrow () and select the Polygon Properties… item. Open the Action tab in the properties window and select the Go to report radio button. Open the expression window for the Specify a report drop-down and add the below expression.
=IIF(Fields!CountryRegionCode.Value = “US”, “SalesByCustomer”, Nothing)
Note that we have enabled drill-throughs only for the US. We can enable this for other countries if we have created reports for them too. Again, if we need to use one report for showing data for all countries, it is also possible by having one report that loads all data including spatial data at the runtime.
Click OK to save the settings.
That’s it! Both reports are now implemented. There are lots of additional properties and settings which can be set with the Map Item, explore and use them. If you have any doubt, need clarification, or suggestions, please add a comment.
Windows Management and Scripting 