Note: We are using AdventureWorks2008R2 DB in this step by step lab as it contains Resellers Addresses as Spatial Data.
1. Start Microsoft SQL Server Business Intelligence Development Studio from Start | All Programs | Microsoft SQL Server 2008 R2.
2. Create a new Report Server Project.
3. Create a new report named Resellers Location.
Note: Maps cannot be created using the Report Wizard;
5. In the New Map Layer wizard, select SQL Server spatial query and click Next as displayed in Figure
Note: The Sample Geographic data about resellers is available in the AdventureWorks2008R2 SQL Server database.
6. In the Choose a dataset with SQL Server spatial data step, select the Add a new dataset with SQL Server spatial data radio button and click Next.
7. In the Choose a connection to a SQL Server spatial data source step, create a data source connection to your spatial data. To do this, click New to open the Data Source Properties dialog and follow these steps:
a. Set the data source’s name to AdventureWorks2008R2.
b. Keep the Embedded Connection and Microsoft SQL Server (in Type combo box) default selections.
c. Click Edit to open the Connections Properties dialog. Set the server name to “YourServer”” server alias and select AdventureWorks2008R2 from the Select or Enter a Database Name dropdown list. Optionally click Test Connection, and finally click OK.
The resulting Data Source Properties dialog should appear. Click OK to go back to the New Map Layer wizard and click Next.
8. In the Design a Query step, type a T-SQL statement which returns a list of resellers along with their location. To do this, click Edit as Text and paste the T-SQL statement below into the query window. Click the run button () and note that the SpatialLocation column from the Person.Address table is a field of type Geography which stores the longitude and latitude of each reseller’s location. Click Next.
Select s.BusinessEntityID, s.Name, a.SpatialLocation
From Sales.store s
Inner Join Person.BusinessEntityAddress bea
On s.BusinessEntityID = bea.BusinessEntityID
Inner Join Person.AddressType at
On bea.AddressTypeID = at.AddressTypeID and at.Name = ‘Main Office’
Inner Join Person.Address a
On bea.AddressID = a.AddressID
Inner Join person.StateProvince sp
On a.StateProvinceID = sp.StateProvinceID
Inner Join sales.SalesTerritory st
On sp.TerritoryID = st.TerritoryID
Inner Join Person.CountryRegion cr
On st.CountryRegionCode = cr.CountryRegionCode
Where cr.name = ‘United States’
9. In the Choose spatial data and map view options step, make sure that SpatialLocation is specified as the Spatial Field, and that the Layer Type is set to Point. Click Next.
10. In the Choose Map Visualization step, keep the Basic Marker Map default selection and click Next.
11. In the Choose color theme and data visualization step, select the Corporate and Circle options from the Theme and Marker combo boxes respectively. Then click Finish to close the New Map Layer wizard.
12. Rename the Title of the map that appears on the Design surface to US Resellers. To do this, double-click on Map Title and type US Resellers.
Playing a little more with alignment and properties you get something like the one below:
And further adding up a Polygon layer through the MAP LAYER WIZARD to the Map we can finally show up the report like this… (this is not discussed in the article probably it might be the part of future posts)
So a new way to present your spatial data on the Report !!