Creating an ActiveReports-Map Report with Spatial Data Points

Problem Statement :

As discussed in a previous blog, ActiveReports now offers some cool features and Maps is one of them. In this blog, I will show you how can you plot points on the Map from the dataset.

Solution :

ActiveReports offers the capability of adding maps to your reports using spatial data. In this blog I'll show you how to accomplish this. If you have a table with addresses, zip codes, etc. we'll first need to find the latitude and longitude of each address. The corresponding latitude and longitude can be determined by using any geocoding website (eg : http:/www.gpsvisualizer.com/geocoder/) Once you get the latitude and longitude information you can import it into your database. An example of such a table can be previewed here : Once you have your coordinates we will need to create a new column with a geography data type that we will hold the value of geographical points for the respective Lat/Long points (ie. the spatial data info). The following code can be used to accomplish this. ALTER TABLE [dbo].["TableName"] ADD [SpatialData] GEOGRAPHY GO Next step is an important step wherein you will convert Latitude/Longitude (Lat/Long) to Geography Point and would insert the data into the SpatialData column. The script that you need to use is : UPDATE [dbo].["TableName"] SET [SpatialData] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')’, 4326) GO

Steps :

Now that we have all of our data in our table it's time to create our Map Report. 1. Open the ReportDesigner and create a new PageReport 2. Bind the report to the respective DataTable by setting it’s DataSource property 3. Create the DataSet and set the query as : Select * From 'TableName' 4. Drag and drop a Map control on the Report 5. Select the Map template as ‘From ESRI File’ and load the World Shape File 6. Right click at the bottom of the map and add PointLayer 7. A dialog will pop-up. Select the 'Dataset' option for the Spatial data source 8. Select the respective dataset from the Dataset dropdown list 9. Select the 'SpatialData' (name of the column having geographical points) option from the Field name dropdown list 10.Click the Preview tab to see the Report And the following is how your report will look: Note : ActiveReports does not support the Oracle spacial datatypes. Hence this blog targets only SQL Server databases.