Using Visio and SSRS Map Reports for Store Layouts etc.

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:

http://prologika.com/CS/blogs/blog/archive/2009/08/30/heat-maps-as-reports.aspx http://www.codeproject.com/KB/recipes/treemaps.aspx

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:

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:

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:

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.

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:

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.