Reporting Services 2008 R2: Geospatial Visualization – Part II

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.

Continues…

Leave a comment

Your email address will not be published.