Creating a Map Report using Spatial Data in SQL Server 2008 R2

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.

image

3. Create a new report named Resellers Location.

Note: Maps cannot be created using the Report Wizard;

image

4. Add a Map item to the Resellers Locaion. To do this, drag a Map item (clip_image006) from the Toolbox to the Design surface. This will start the New Map Layer 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.

image

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.

image

The resulting Data Source Properties dialog should appear. Click OK to go back to the New Map Layer wizard and click Next.

image

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 (clip_image014) 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.

T-SQL

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’

image

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.

image

10. In the Choose Map Visualization step, keep the Basic Marker Map default selection and click Next.

image

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.

image

Playing a little more with alignment and properties you get something like the one below:

image

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)

image

So a new way to present your spatial data on the Report !!

Be Sociable, Share!