Embedding Maps in SQL Server Reporting Services Using ArcGIS Server

/in

The combination of Microsoft and Esri products is common across Federal information infrastructures and, as a result, we find ourselves integrating these tools often. SQL Server Reporting Services (SSRS) is still a widely-used solution for generating dynamic reports in many government settings. Invariably, some users want to use their Esri tools to embed maps into their reports. This post discusses a simple approach to using ArcGIS for Server to dynamically provide maps in SSRS reports based on report parameters.

Many of our customers use SQL Server to manage their geodatabases, using either the native geometry or geography types for their storage mechanism. This gives us the ability to use native spatial SQL functions to query the parameters necessary to embed maps in our reports. For the purposes of this post, I bypassed the geodatabase and simply loaded a shapefile as a SQL Server table using Morten Nielsen’s SQL Server Spatial Tools (http://sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx). If you are using ArcGIS Server, you probably have your data in a geodatabase but, for this process, the geodatabase is a bystander and not needed for this discussion. The results of the data load can be seen in SQL Server Management Studio:

My goal is to have my report dynamically retrieve a map of an individual country, based upon that country’s extent. This data set will provide me the extent parameters but it will require some spatial SQL to do so. I created a view to get the minimum and maximum latitude (Y) and longitude (X) values for each country polygon:

CREATE VIEW [dbo].[vwCountryEnvelopes]
AS
SELECT
shape.STEnvelope().STPointN(1 ).STX AS MINX,
shape.STEnvelope().STPointN(1 ).STY AS MINY,
shape.STEnvelope().STPointN(3 ).STX AS MAXX,
shape.STEnvelope().STPointN(3 ).STY AS MAXY,
FIPS_CNTRY
FROM
dbo.WORLD_COUNTRIES00
GO

For the sake of simplicity, I created a second view that joined this view back to the original table in order to get all of the information I needed into one SSRS data set:

CREATE VIEW [dbo].[vwWorldCountriesWithEnvelopes]
AS
SELECT
dbo.WORLD_COUNTRIES00.*,
dbo.vwCountryEnvelopes.MINX,
dbo.vwCountryEnvelopes.MINY,
dbo.vwCountryEnvelopes.MAXX,
dbo.vwCountryEnvelopes.MAXY
FROM
dbo.WORLD_COUNTRIES00 INNER JOIN
dbo.vwCountryEnvelopes ON dbo.WORLD_COUNTRIES00.FIPS_CNTRY = dbo.vwCountryEnvelopes.FIPS_CNTRY
GO

With this database foundation in place, I began designing my report in Business Intelligence Development Studio (BIDS). After starting a new report design, the first thing I needed to do was defined my report’s input parameter. In this case, I chose the two-character FIPS country code:

Next, I defined my data set, which just queries my view, passing in the FIPS code parameter:

I then began the visual design of my report. All of the textual data is placed using standard SSRS procedures and tools.

As can be seen from the picture above, I intend to use a picture box to display my maps. BIDS has a map control for use with SSRS but this control is designed specifically to work with Bing maps and underlying SQL Server data. While this is not a bad approach, it does not capitalize on in-place ArcGIS investments.

To populate the maps, I will use the MINX, MINY, MAXX, MAXY values from my query results to format a URL to pass to the ArcGIS Server REST API. For this post, I am calling to Esri’s sample services but, in practice, I would most likely be calling to a local ArcGIS Server instance. To set up the URL, I right-click the picture box in BIDS and choose “Image Properties” to open this dialog:

I set the image source to “External” and then clicked the “fx” button next to “Use this image” to set the following expression:

="http://services.arcgisonline.com/ArcGIS/rest/services/World_Street_Map/MapServer/export?bbox=" " FIRST(Fields!MINX.Value, "Country") " "%2C" " FIRST(Fields!MINY.Value, "Country") " "%2C" " FIRST(Fields!MAXX.Value, "Country") " "%2C" " FIRST(Fields!MAXY.Value, "Country") " ""bboxSR=4326"layers="layerdefs="size=500%2C500"imageSR="format=png24"transparent=false"dpi="time="layerTimeOptions="f=image"

Here, you can see that I am concatenating a URL string to include the bounds that were returned by my query. If the syntax looks a little strange, that is because SSRS uses the old Visual Basic/VBA syntax. You can see that I have hard-coded my image size so that all maps will be square and that the images will always be PNG24. Also, my bounds values are in WGS84 so I made sure I set the bboxSR parameter to 4326. You can obviously manipulate any of the REST API parameters as you see fit.

With this set up, I am ready to preview my report. I chose “GR” as my FIPS code my report will show Greece, complete with the familiar Esri street map:

This post was written by:
Bill Dollins

Senior Vice President

For more information on this post, Zekiah’s geospatial integration capabilities, or to explore working with Zekiah, feel free to email us at contact@zekiah.com.