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:
And here is the corresponding Visio file I made for this purpose:
The main reasons for me to explore this approach instead of simply using Visio services (included in Sharepoint) are:
- Purely data driven approach – all layout data is stored as spatial data in a SQL Server table
- All layouts (for example several stores with different layers) could by managed in a single Visio file (or a few Visio files)
- The interactivity (for example link action to another drawing) and the mapping to the data can be highly automated.
- It is very easy to create a report that checks, if shapes are missing (data available, but no corresponding shape in the geometry table).
- 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
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
|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|
|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:
|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:
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:
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:
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.