Working with Spatial Data Part III – Reporting Spatial data using Reports Builder 3.0

Development of any analytical solution normally ends with a form of  report, whether it is a normal tabular report or a sophisticated interactive dashboard.   In the first part of our BI-Satellite project, we devised a way to geocode location based spatial data using SSIS and Bing Maps. Thereafter in the next part we plotted points on the geocoded data and stored them in a SQL Server 2008 R2 database. We have already collected the data we require, but this data is still not in a form useful enough for end users to analyze or report to any senior management body. Spatial data in a geocoded format can be best viewed on a geographical background i.e. on a map. As the data is based on location information, representing it on a geographical map makes it logical and appealing to the end user. In our case, our data is based on the location of some of the world’s most famous buildings in text format, which we have geocoded as well. Using this geocoded data, we will examine how we can develop a report on a geographical background using SSRS 2008 R2 and Report Builder 3.0. Environment Setup: Before we start our exercise, we need to have the software stack ready for developing reports, as below: 1) The example which we will work through in this article has been developed and tested using SQL Server Reporting Services 2008 R2 in native mode installation. 2) The report development detailed in this article requires Reports Builder 3.0 Nov CTP, which can be downloaded here. 3) We will start the development of the reports design with the assumption that, the Address table discussed in Part-I and Part-II of this article series has already been populated with geocoded data. Report Design and Development: Design: Briefly, the intended design of the report is to represent geocoded data on a geographical map. This report will  be simple and contain just map and data points . Development: The entire report development will be facilitated using Reports Builder 3.0. In addition, the new Bings Maps layer will be used for plotting geocoded data on the maps layer. You might encounter issues if you have a proxy that blocks access to Bing Maps web services, still it will not be a show stopper for report development, the only effect would be that you won’t have a Bing maps background or layers on your report. Implementation: To develop the report, follow the steps mentioned below: 1) Open Reports Builder 3.0, create a new blank report. 2) In order to fetch our data from the table, we need to create a Data Source. Create a new Data source that points to your database where you have the “Address” table and name it “DS”. 3) After creating this, create a new dataset pointing to the “Address” table and name it “BingMapsDS as shown in the below screenshot. 4) From the Insert menu, select Map item from the ribbon and select “Map Wizard”. The first page of the wizard requires a selection of the source of spatial data. As we have our data in SQL Server which we will be fetching by dataset, select the option  “SQL Server Spatial Query” as shown in the below screenshot. Keep in mind that when this option is selected, your dataset should be having at least one spatial data type field (i.e. field of a geometry or geography data type).  5) This page requires the selection of a Dataset, which is the result of our selection on the first page of the wizard. Select the existing dataset which we created in Step 2, as shown in the below screenshot. Move to the next page of the wizard after selecting this. 6) The next page of the wizard is the most important page, and options should be selected carefully. The spatial field selection has intelligently selected the only spatial field in our dataset. As the spatial object we have in this field is of type “Point”, select the Layer type as Point. Below the map preview, you can see three options. If you embed map data in the report, the report data will always be available in the report and it will not be fetched from the database. It will act like a static report.  The most important option is the “Add a Bing Maps Layer” and Tile type. Select this option as we want to add this layer to our map and select Tile type as “Hybrid”. You can play around with any of the three tile types available to get a feel of difference between them. Select appropriate options and move to the next page of the the wizard. 7) Select “Basic Marker Map” as we are just interested in plotting the positions of locations from Address table, and move to the next page. 8) There are several options regarding the presentation of locations on the map which are obvious in their impact. The most important option on this page is the Data field. The value / text from the field you select for this property / option will be displayed on the map below the marker that you select to point the location. We do not want AddressID to be displayed, so select AddressLine1 field for this property. Select rest of the options and move to the next page of the wizard.