Hilmar Buchta

SQL Server 2008 R2

My last posts about Reporting Services in SQL Server 2008R2 covered the new support for maps and how to import and work with ESRI shape data.

However, those new features of Reporting Services are not limited to geographical map data (countries, regions etc.) but can be used to visualize almost any kind of polygonal data. For example, there are very interesting posts showing how to actually draw a heat map using SSRS’s map technology. If you’re interested, I suggest taking a look at one of these posts:

 

Being able to show different kind of geometrical data also makes it interesting to use this feature for store layouts. Basically my idea was this:

  • Use Microsoft Visio as an editor for the store layout
  • Export all sheets of the Visio file to a data base table
  • Use Reporting Services to visualize the visio shape information and link some parts of the graphic to an analytical data set for shading

 

For my example, I created a layout of a park house. Each slot is to be colored depending on the number of parking hours they were occupied during a selected period. Here is the final SSRS report:

image

And here is the corresponding Visio file I made for this purpose:

image

The main reasons for me to explore this approach instead of simply using Visio services (included in Sharepoint) are:

  1. Purely data driven approach – all layout data is stored as spatial data in a SQL Server table
  2. All layouts (for example several stores with different layers) could by managed in a single Visio file (or a few Visio files)
  3. The interactivity (for example link action to another drawing) and the mapping to the data can be highly automated.
  4. It is very easy to create a report that checks, if shapes are missing (data available, but no corresponding shape in the geometry table).
  5. Suitable for users without access to Sharepoint or without the Sharepoint Enterprise CAL

 

The approach described here is suitable for

  • Any kind of store layouts (for example: mapping from product sales to the area in the store)
  • Custom geographical maps (it’s quite easy to load a bitmap into Visio and then create some polygons on top of it to get the proper shapes)
  • Visualization of technical systems or processes
  • Strategy maps
  • Etc.

 

During the development I had some issue to solve, so this post is to describe my general approach. At first, I was quite surprised to find how difficult it is to get the shape data out of Visio. You could export the Visio file to DXF format and use one of the converters from DXF to ESRI you will find in the web. But this seems like a complicated process to me. So for my example, I wrote a Visio macro to actually perform the export. My destination table looks like this:

Field name Description Sample Value
sheetid Number of the sheet tab in the Visio file 1
sheetname Name of the sheet in the Visio file Level1
layer Corresponding layer for the map (see below) 1
shapeid Number of the shape (internal id by Visio) 47
shapename Name of the shape. For the interactive layer I used this field as the link to the analytical data set. For example, 01_01.112 stands for park house 1, level 1, slot 112 01_01.112
shapetext Text of the shape 112
data1 extra Visio data (field 1)
Used as a link to the next layer of the park house
Level2
data2 extra Visio data (field 2)  
data3 extra Visio data (field 3)  
fillcolor Background color for the shape #ff0000
linecolor Line color for the border of the shape #000000
linewidth Line width for the border of the shape 0.75
linestyle Line style Solid
fontname Font name Calibri
fontsize Font size 12
fontcolor Color for the text #000000
geom Data of type geometry  

 

In order to display the map on an SSRS report, the map tool requires to set up a layer. For each layer you are relatively free to display shapes. However, there are some restrictions:

  • Each layer must contain either polygon or line or point shapes. No mixture is allowed
  • Each layer may be set up to acquire its shading color either from an analytical data set or from expressions (that can link to existing data of course)

 

In order to set up the layers for my map, I used the following layout:

Layer Description
0 Bottom layer (Background) containing only polygons. No interactive data elements. All shape properties (like background color for example) are taken from the data set described above
1 Interactive layer. This layer also contains only polygons. The layer is configured using an analytical data set to perform the shading of the elements
2 Non-interactive layer of lines. As described above, the lines in the drawing must not be mixed with the polygons from layer 0. However, putting those lines on top of the interactive layer 1 makes it possible to show for example regional borders on top of sales areas (interactive coloring) that are not restricted to a single region.
3 Non-interactive layer of points (I didn’t use this in my example)

 

In Visio I’m using the Layer functionality to mark all the interactive elements (for layer 1), so this is how my drawing looks like in Visio when hiding this layer:

image

But how about the round shapes? How are they converted to a polygon. Although I was concerned a little, Visio does this job pretty well. In the following extract of the code, patchcount is a variable to loop through each path contained in the shape (oShape) and adblXYPoints is simply an array of numeric values. The 0.01 gives the precision Visio uses to transform the curves into polygons.

oShape.Paths(pathcount).Points 0.01, adblXYPoints

For layer 0, all shape properties are linked to the underlying data set. For example, this is how the font tab is configured:

image

You can clearly see that field bindings or expressions are used to configure the properties. For example, the expression for the font size looks like this:

=Fields!fontsize.Value & "pt"

The distribution of the spatial data to the layers is done by using one single data set together with the filters in the layer’s data properties. The the background layer 0, the filter is defined as follows:

image

However, I had some problems with the filter when using the analytical data, so I created a separate data set for the interactive layer.

Finally, the export from Visio to my geometry table works fine although not all of Visio’s graphical features can be rendered by a Reporting Services map. I’m still surprised how good the rendering actually looks and how easy it is, to link it with some analytical data to get a nice visualization.

  • Share/Bookmark