With the release of SQL Server 2008 R2, Microsoft provided the capability of generating Reporting Services solutions that provided more than just tablix data. It provides the option of showing static maps to the report viewer. The control provides a wizard that makes it very easy to consume data from sources such as ESRI Shapes or spatial queries against SQL Server. What seems to be missing is the ability to access spatial type data from other sources such as Excel, text or other sources. I was challenged to make this happen. After several attempts and a number of roadblocks, I have discovered a way to make this work.
Through the rest of this post, I will show you how to create a report that maps the locations of employees out of the Human Resources (HR) schema within an Oracle database. This would be easy to extend to other data sources as well. Let me clarify that the data I will be accessing is not specific spatial data types unique to different sources. It is the data that is typically stored in a number of databases and spreadsheets, the Latitude and Longitude of a location.
Create a Reporting Services Project
Begin by opening Business Intelligence Developer Studio 2008/Visual Studio 2008. Create a new Business Intelligence project using the “Report Server Project” template. This will leave you with an empty Solution Explorer shown in Figure 1.
Right click on the Reports Folder and add a new report by choosing ‘Add -> New Item -> Report’ and give the report a name.
Define a Data Source and Data Set
Next add a Data Source in the Report Data window, by right clicking on Data Sources then ‘Add Data Source’. Within the Data Source Properties window, name the data source, choose the provider and give it an appropriate connection string. Since I’m going against an Oracle database and want to ensure my connection, I’m providing the connection string from the tnsnames.ora file. See Figure 2 for my setup.
Choose the ‘Edit’ button to provide credentials. Since I’ve unlocked the HR schema and I want the Employee database, I provide user name and password of ‘hr’ and ‘hr’. I’ll also have it save my password as seen in Figure 3. Then I finish by testing my connection.
Now to create the base dataset, right click in the ‘Report Data’ window on Datasets and choose to ‘Add Dataset’. Within the window for Dataset Properties, name the dataset, let it be embedded in the report and choose the Data Source we created earlier ‘OracleSource’. Finally provide the query. In this query, select the first name, last name, email, latitude, longitude from the Employees table. (Note: I had to enhance the schema of Employee to include two new fields of Number type with precision and scale set for 10 and 8 respectively. Rather than update all 107 records, I only added location information to the first ten records for Employee_ID 100 -110.) Figure 4 shows the window.
Here are the results of the query in Figure 5.
This will generate a Report Data window like Figure 6:
So far, this is nothing special from generating a report geared toward a table. However, we have to do something different in order to get to generating a SQL Server Spatial field type. In order to create this field, we have to add external library references to the report. Right click the work area of the report and choose ‘Report Properties’. Under the References tab, add two .Net assemblies, System.Data and Microsoft.SqlServer.Types. Do this by clicking Add and then choose the ellipse on the right for each one. Here are the results of your work in Figure 7.
After creating the references, right click on the EmployeeInformation dataset and create a new Calculated Field.
Within the Dataset Properties window, under the Fields tab, add an extra field with the name ‘GeoLocation’, click the expression button and type: =Microsoft.SqlServer.Types.SqlGeography.Point(Fields!LATITUDE.Value,Fields!LONGITUDE.Value, 4326)
The expression provides an elliptical projection with an expectation of points populated by the datasets Latitude and Longitude fields. The ‘4326’ is a projection reference for maps. I’ll leave it to you to research if this is right for you. Now, you have added a calculated field to your dataset.
Generate Visual Elements of the Report
Let’s begin by adding a table to the report by right clicking the design surface and adding a table to the top left of the report. Next drag out the FIRST_NAME, LAST_NAME, and EMAIL fields to the table. Next insert a Map control and place it on the top right of the report. A wizard will then begin. Ignore this wizard by canceling it. This works great if you are adding SQL Server Spatial data, but that is not what we are attempting. The design should look like Figure 8.
A map typically consists of a number of layers. These layers can be tiled images, data points, lines, and/or polygons. We are going to add two: Bing Maps tile and a series of data points from our dataset. Double click any area of the map to present the Map Layers window. Choose the second icon in the toolbar to add a Tile layer. This will add a Bing map of the world to your control. Add another layer to your map, this time a Point Layer as shown in Figure 9.
In the properties window change the DataSetName to EmployeeInformation like in Figure 10.
Go back to the Map Layers window and choose the selector (down error) for the Point Layer and select Data Layer. From the list of options in the Map Point Layer Properties, choose the ‘Spatial field in an analytical data region’ and close this window. Over in the properties window, expand the Spatial Data property to expose the VectorData field. From the dropdown choose the expression option to input the code: =Fields!GeoLocation.Value.
Now your report could be presented right now, but let’s make it look a little sharper. Click on the selector again for the Point Layer and choose ‘Point Properties’. Select the EMAIL field for the Tooltip and change the Marker type to a triangle. See Figure 11 for the settings.
From the selector for the Point Layer, choose the Point Color Rules option. To make the locations easy to see, I set the option to visualize data by using color ranges and I set the color options all to red (play with this layer on your own for wild colors).
I’ve made all of the changes I want and I’m ready to preview the report. You may get some different results but it should be similar to Figure 12.
That’s it! You now have a static report ready to be printed or exported to another format.
Let me know if you have any questions or perhaps you have some other ideas how to use the map control against non-SQL Server resources.
Thanks to Keith Tran who brought this request to me.