Adding Map Callout Boxes in Reporting Services

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.

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:

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.

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.

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,

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,

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.

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.

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,

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.