"Cartographic Adjustment" of Spatial Data for SQL Server Reporting Services, Part 5

The Report Builder, a component of Reporting Services, described in this post will be available as a separate download when the upcoming CTP2 release of SQL Server 2008 Release 2 (R2) is made available to the public, currently scheduled for August 2009.  

This was to be the last post in this series but it's too interesting to stop here.  As of this writing, I'm planning one additional post in this series which will expand on the new mapping capabilities of SQL Server Reporting Services.  Please note that these new capabilities will be introduced in CTP2 of the upcoming SQL Server 2008, R2 release. Today's post will introduce the new SQL Server Report Builder and its new mapping capabilities. Here is the workflow presented so far:

  1. Locate data source and download (Part 1)
  2. Load the data into SQL Server (Part 1)
  3. Remove unwanted features, simplify Alaska and Hawaii spatial features (Part 2)
  4. "Reposition" Alaska and Hawaii cartographically (Part 3)
  5. Locate and load State-based population data (Part 4)
  6. Use the data in the new SQL Server Report Builder
  7. More SQL Server Report Builder

So that you don't have to immediately scroll to the end of this post to see the final result, here is a reduced scale version of the final map report, noting, of course, the repositioned Alaska and Hawaii features ;-):

image

For those of you interested in the Report Builder workflow to create this map, please keep reading.

When the new Report Builder is first opened, a design canvas is presented, allowing you to select on of three report wizards: Table, Chart or Map.  Since we have just finished adjusting a set of spatial data, the Map wizard looks like the ticket:

image

When Map is selected, you will be presented with a wizard which will take your through the creation of your first map report.

The first wizard menu presents you with a selection of spatial data sources. For this post we are going to use SQL Server spatial data:

image

The next step is to create a new data source connection:

image

Selecting "New..." brings up the next screen:

image

We have the option to create a new data source name but for this exercise, we won't change the default name.  Selecting the "Build..." button displays the Connection Properties menu:

image

Here we will define our database server instance, authenticate the connection and connect to a database:

image

Hitting "OK" brings up the next panel.  Since this looks good, we'll hit "OK":

image

This looks good, so we'll hit "Next >":

image

The query designer is now presented.  We'll select "Edit As Text":

image

Here is the basic query we intend to use:

SELECT STATEFP, GEOM FROM US_STATES_INSET

This initial query selected columns STATEFP and GEOM with the goal of using the State FIPS Codes in the STATEFP column as the join column with the US_STATES_POPULATION table.  The join column in the US_STATES_POPULATION table is the STATES column, also containing FIPS Codes for the States. A quick review of the two join columns revealed a basic incompatibility despite the fact that they were both defined as character columns:

STATEFP STATES NAME
01 1 Alabama
02 2 Alaska
04 4 Arizona
05 5 Arkansas
06 6 California
08 8 Colorado
09 9 Connecticut

to make the two join columns compatible, we can cast the character columns to integer.  Here is the rewrite for the current query:

SELECT CAST(STATEFP AS INT) AS STATEFP, GEOM FROM US_STATES_INSET

We will use this query in the query designer.  Hitting "!" button will execute this query, with the results displayed in the bottom window, below:

image

Selecting the "Next >" button brings up the following menu:

image

This all looks good, so we will select "Next >" here also and bring up the visualization menu, where we will choose "Color Analytical Map":

image

Choosing "Next >" brings up the "Choose the analytical dataset" menu.  Our analytical data will come from the US_STATES_POPULATION table, se we nee to select the "Create a dataset" radio button:

image

Choosing "Next >" brings up the menu panel which allows us to choose a connection to the new dataset.  Since the US_STATES_POPULATION table is in the same database as the spatial table (US_STATES_INSET), we can use the existing connection, DataSource1:

image

Choosing "Next >" bring us to the query designer screen for the analytical data:

image

As with the query designer for the spatial data, we well choose "Edit As Text" and use the following query:

SELECT CAST(STATE AS INT) AS STATE, POPESTIMATE2008 FROM US_STATES_POPULATION

Our intent is to join the two tables and display the 2008 population estimates (POPESTIMATE2008 column) for each state on the resulting map.  As will recall, we are casting the State FIPS Code to integer to guarantee join compatibility on the join columns.

 image

After selecting "Next >", we are offered the opportunity to specify the join columns:

image

Checking the "Key" box allows us to specify the analytical fields in the US_STATES_POPULATION table query we just specified - in this case Map Binding Field: STATEFP, Analytical Field: STATE:

image

Select "Next >" to bring up the color theme and data visualization menu.  Here we select the data field (analytical field) which will be symbolized by different polygon fill colors based on, in this case, population:

image

Selecting "Finish >>" brings up the design canvas.  In the version of the Report Builder that is being used for this post, there is a bug which prevents the map display from rendering the underlying spatial data correctly.  In order to remedy this, we need to set the coordinate system for the map display (It currently defaults to the geography data type. Since the GEOM column in the US_STATES_INSET table is of type geometry, the design-time display is not correct):

image

To set the correct coordinate system, left-mouse-click on the design canvas to select and then right-click with the mouse to bring up the contextual menu.  What we are looking for are the "Viewport Properties..."

image

Select the "Viewport Properties..." allows us to set the Map coordinate system to Planar (X,Y).  In future versions of the Report Builder, the coordinate system will be automatically detected.

image

Selecting "OK" completes the task and the design canvas correctly illustrates the United States inset map.  Note that the map is not yet rendered and the design canvas map display is simply a placeholder.

image

In the design canvas, we can adjust the map a bit, moving the color scale bar to the right and adding a title.  When done, we can render the final map by choosing  "Run" from the main menu ribbon of the Report Builder:

image

When rendering is completed, the following report is produced:

image

In the next post, Part 6, we will explore some additional capabilities of the Report Builder to finish off the series.

Technorati Tags: SQL Server 2008,SQL Server 2008 R2,CTP-2,CTP2,Reporting Services,Report Builder,SSRS,Map,Spatial,SQL Server Reporting Services