Introduction

In SQL 2008 R2, 2012, The Reporting Services map tool provides powerful features for geographically visualizing business information and data. While designing and developing a report project with map tool, it may require showing business data over a small area on map, where the text is difficult to present. A callout text box outside the area would help to enhance the visual effect of presenting data as shown in Figure 1.

Figure 1. Callout boxes on a Map Report.

Adding a callout text box in map layer can be a challenge, tedious work. It requires determine the coordinates of callout box and center, and align the vertexes correctly for generating polygon used in map spatial data. This article will show you how to add callout boxes on map in reporting services projects in a few simple steps.

Database Settings

The SQL Server Spatial Tools and SharpGIS Shape2Sql are used for creating database and processing spatial data. The US state map data shapefile used in this project is tl_2011_us_state.zip.

It takes four steps complete database settings.

1. Unzip the SQL Server Spatial Tools package, run script “Register.sql” to install SQL Server Spatial Tools. This will get all support functions and methods for preparing map spatial data. You need modify the “Register.sql” at line:

CREATE ASSEMBLY SQLSpatialTools FROM 'C:\SqlSpatialTools\SQLSpatialTools.dll'

to reflect your SQLSpatialTools.dll correct file path.

2. Run Shape2Sql.exe to load US state map shapefile data into database. Ensure the highlighted Geometry Name attribute “Geom” consistent with the one used in script “SpatialData.sql”. The configurations for loading shapefile data is shown as Figure 2.

Figure 2. Configurations of loading shapefile data.

3. Execute this command:

ALTER TABLE tl_2011_us_state ADD Geog GEOGRAPHY

to add a new column Geog of GEOGRAPHY type. This will be used for generating map spatial data.

4. Run the script “SpatialData.sql” provided in project package to complete map spatial data processing. Make sure to change the script to adapt to your Database.

A good reference for the map spatial data processing is the article “Create CNN-style map in Reporting Services” by Peichung Shih. It details about how to install SQL Server Spatial Tool, load spatial data from shape file to SQL database, and prepare the spatial data ready for using in reporting services.

Create MapCalloutBox Function

A user defined scalar function dbo.MapCalloutBox will be created with the script called “MapCalloutBox.sql”. The function prototype is,

CREATE FUNCTION dbo.MapCalloutBox(
 @centerX FLOAT,
 @centerY FLOAT,
 @startptX FLOAT,
 @startptY FLOAT,
 @width FLOAT,
 @height FLOAT,
 @orientation CHAR(2))
RETURNS GEOGRAPHY
WITH EXECUTE AS CALLER

Where, parameters @centerX and @centerY define the center vertex, red dot (@centerX, @centerY) shown in Figure 3, of callout box pointing to, @startptX and @startptY define the start vertex, red dot (@startptX, @startptY) shown in Figure 3, of callout box, @width (in longitude) and @height (in latitude) define the callout box width and height, and @orientation defines the direction of callout box toward. The valid values of @orientation are ‘NE’, ‘NW’, ‘SE’, ‘SW’, ‘WN’, ‘WS’, ‘EN’, and ‘ES’, i.e. ‘SE’ represents south side east direction, which are shown in Figure 3.

Figure 3. Parameter @orientation naming convention and valid values for MapCalloutBox function.

Function MapCalloutBox eventually wraps up the GEOGRAPHY::STGeomFromText(@wktpts, 4326) function. It simplifies the work for generating callout box polygon with well-defined 7 parameters. Inside the function, the well-known text @wktpts for callout box polygon is created according to the user defined center and start vertexes coordinates, box width and height, as well as the point direction. Therefore, user does not need to determine, compute the polygon vertexes coordinates, and verify their sequence and orientation.

To test MapCalloutBox function, run “TestCalloutBoxes.sql” script provided to verify function working properly.

Add Callout Boxes to Map

It is fairly straightforward to add callout boxes to map with calling function MapCalloutBox. A SQL Script “InsertMapCalloutBoxes.sql” demonstrates how easy it is to add arbitrary callout boxes to map. The code is,

USE [MapDemo]
GO
IF EXISTS(
  SELECT *
  FROM [tempdb].sys.tables
  WHERE Name LIKE '#CalloutBox%')
 DROP TABLE #CalloutBox
SELECT  'RI' AS StateUSPS, 1 AS Ordinal, -71.5 AS Cx, 41.5 AS Cy, -67 AS Lon, 42.5 AS Lat, 'WS' AS Ori INTO #CalloutBox
INSERT  #CalloutBox SELECT 'CT', 2, -72.7, 41.6, -68, 41, 'WN'
INSERT  #CalloutBox SELECT 'NJ', 3, -74.5, 40.2, -72, 37.5, 'WN'
INSERT  #CalloutBox SELECT 'DE', 4, -75.4, 39, -72, 34, 'WN'
INSERT  #CalloutBox SELECT 'FL', 5, -82, 29.5, -78, 30.5, 'WS'
INSERT  #CalloutBox SELECT 'VI', 6, -70.9, 22.8, -71, 26, 'SE'
DECLARE @iCount INT
SET @iCount = 1
WHILE @iCount < 7
 BEGIN
  INSERT  dbo.[StateMap]
  SELECT  S.StateFP,
         S.StateUSPS,
         S.StateName,
         dbo.MapCalloutBox(c.Cx, c.Cy, c.Lon, c.Lat, 6, 2, c.Ori) AS Geog,
         3 AS MapSetId
  FROM    dbo.[StateMap] S
   INNER JOIN #CalloutBox c 
     ON c.Ordinal = @iCount
     AND c.StateUSPS = S.StateUSPS
  SET @iCount = @iCount + 1
 END
SELECT * FROM StateMap

A sample map with adding callout box is shown as Figure 4.

Figure 4. A Sample map with callout boxes.

It would be worth to mention that SSMS Spatial Result panel provides a great tool for visualizing the data graphically during the design and development stage. One can easily determine, verify the spatial data, and estimate the coordinates of interested points.

Use of Map Callout Boxes in Reporting Services

Start Visual Studio 2008/2010, create a new Business Intelligence –> Reporting Services –> Report Server project. Add a new report from “Add New” Item under the “Solution Explorer” panel, Reports folder.

On Report Data panel, click Data Source folder, add new data sources for spatial map and business data. Click DataSets folder, add new datasets for both SQL spatial and business data. A brief outline of design view of the sample report project is shown as in Figure 5.

Figure 5. Design view of a sample report project for callout boxes demo.

In the sample report, the datasets are defined with the SQL queries:

1.  the main map dataset, DataSetMainMap:

SELECT S.StateUSPS, S.StateName, Geog
 FROM dbo.[StateMap] S
 WHERE S.MapSetID = 1 ;

2. callout boxes dataset, DataSetCalloutBoxes:

SELECT StateUSPS, StateName, Geog
 FROM StateMap
 WHERE MapSetID in (3);

3. business dataset, DataSetBusiness:

SELECT Sum(s.TotalDue) AS Amount, st.Name AS StateName 
 FROM Sales.SalesOrderHeader s
  JOIN Sales.SalesOrderDetail sd 
    ON sd.SalesOrderID = s.SalesOrderID
  JOIN Person.Address ad 
    ON ad.AddressID = s.BillToAddressID
  JOIN Person.StateProvince st 
    ON st.StateProvinceID = ad.StateProvinceID
 GROUP BY st.Name
 ORDER BY Amount;

Where the business data used is from AdventureWorks2012 sample database accompanied with SQL 2012.

On the Report Design view, insert a map into report. On the “New Map Layer” dialog window, choose “Use SQL Server Spatial query”, click next, and select the main map dataset as defined above. Choose “Color Analytical Map” on “Choose map visualization” dialog window, then define the analytical dataset by selecting DataSetBusiness. Follow the wizard to proceed through the rest of the steps, and click OK to complete the process.

Insert a second map layer for callout boxes. Repeat the steps as adding main map layer, but select DataSetCalloutBoxes when selecting map dataset. Working in this way, it would get more flexible control over usage of callout boxes. You can customize the callout boxes layer to act as on demand without interfering main map layer properties settings.

To hide out unwanted blank callout boxes, set the Border Line color property on Map Polygon Properties to “No Color”, and Label Visibility with condition, “=Round(Fields!Amount.Value/1000000) > 0”.

A custom code is used to set Label text of Map Polygon Properties on main map layer as shown in Figure 6 and 7. In the provided sample report, the code used is,

Public Function SetLabel(ByVal amount As Decimal, ByVal s As String) As String
    Dim str As String
    str = Trim(UCase(s))
    If str.Contains("FLORIDA") Or str.Contains("CONNECTICUT") Then
        Return ""
    Else
        Return amount & vbCrLf & s
    End If
End Function

Figure 6. Add custom code used to set Label text of Map Polygon Properties on main map layer.

Figure 7. Setting expression for Label text of Map Polygon Properties on main map layer.

A typical sample map report is shown as Figure 8. One can easily see that callout boxes significantly improve and enhance the visual effects of a map report in a flexible way.

Figure 8. A Sample Map Report with Callout Boxes.

Conclusion

Callout boxes can be easily added to map report with SQL Server Spatial tools. In SQL 2008 R2 and SQL 2012, using SQL Server spatial query, geospatial ESRI shapefile, and SQL Server Spatial tools, The callout boxes can significantly improve and enhance the map visualization, therefore provide more useful information for business intelligence reporting services. A sample report project and scripts source code files package is provided.