Creating your own SSRS map using Visio

SQL Server  2008 R2

In my last post I mentioned creating a map by yourself as one possible use case for the Visio-To-SQL-Server geo data export. In this post I will give you some details how this can be done.

First, we need some kind of a map. For this post I’m using a map of Europe which I found as a PNG file at Wikipedia: http://upload.wikimedia.org/wikipedia/commons/6/64/Europe_capitals_map_de.png

The image is then inserted into a blank Visio file. You should make it as large as possible (use landscape orientation). Then, we’re ready to begin. We’re using the freeform tool to follow the coast lines. In my case I’m creating a map for UK and Ireland. Note that you can interrupt the process at any time and continue using the freeform tool later. Also, don’t bother too much with the details by now.

Finally, the curve needs to be closed. Visio shows this by actually filling the area using the default fill color:

If you missed the start point, it’s not a problem as you can correct every single point of your curve and also add new control points using the pencil tool. Before doing so, you should add your recently created shape to a new Visio layer and set the transparency of the layer to an appropriate value (e.g. 20%) so you can see both, the sheet and the map image. Now you should work at a higher zoom level to detail out the border lines as shown below. Note that you can CTRL+click any control point to see and modify the curve control.

Your final result may look somewhat like this

Please note that we didn’t care about the country borders in this step (as you can see from Ireland) but just about the coastlines.

Next we’re going to model the border between UK and Ireland. Therefore we need to create some kind of tool shape in order to cut out the corresponding part. To make our work easier, we’re going to hide our GEO layer, so we’re only seeing the map now. We then draw the shape of the border line. Please note that the shape is extended to the sea side as we don’t want to risk that some parts of the underlying shape are not cut out.

The next steps are a little bit confusing if you perform them for the first time. Here is what we are going to do:

1. Make the hidden GEO layer visible again.
2. We can now see all of our drawn shapes including the tool shape. As the tool shape was create at a later time it is in front of our original shapes.
3. Mark the basic shape for Ireland first and then the tool shape while pressing CTRL. It is important to do it this way.
4. Copy both shapes to the clipboard for later use
5. Do a shape substract operation. Depending on your version of Excel it’s a menu entry at shape>operation>subtract or maybe you have to consult the help file
6. This results in Ireland having the topmost part cut out
7. Paste the two shapes from step 4 back into the sheet
8. Do a shape intersect operation
9. Move the resulting shape back into position (use the SHIFT key together with the arrow keys to position the shape properly)

The result should look somewhat like this:

Next we’re going to group all the shapes for UK (don’t forget the islands) together into one shape. We set the text for this shape to “United Kingdom”. Ireland consists of a single shape in my example so we just have to name it accordingly.

These shapes are already what we need for our lines layer in the SSRS map. The lines layer will only be visible as border lines and will be on top of our map. To make this happen, we mark both shapes and create a copy (copy+paste), then move the copy to the same location as the original shapes. While the copied shapes are still marked we add them to a new layer called LINES. We can then hide the LINES layer as it would only be confusing for our next steps.

Now, by using exactly the same tools as above, we’re going to create the sales regions North, Middle and South. If you have problems with the intersection and the subtract tools, you may need to un-group your shapes before using one of the combine-tools. In my example, the sales regions do not match the country borders. I therefore first combine the two shapes on the island of Ireland together to one shape. Next I’m cutting the whole map into three pieces. In the next screenshot I colored the pieces to make it more clear:

All shapes with the same color are grouped together into a single shape. We name the shape according to our sales regions: North, Middle and South. Now we’re almost finished. We now move all the shapes for our sales regions to a new layer called INTERACTIVE and remove all layers except INTERACTIVE and LINES. We can now set both layers to visible again. Maybe some shapes from the INTERACTIVE layer have to be pushed into the background but then the final result should look like this:

Note that the sales regions do not match the border in Ireland.

Using the same macro as described in my last blog post I’m exporting the Visio data into a SQL table called UK_Map. In this case we end up with two layers (no layer 0 as we don’t have any background polygons as the park house in my last post):

Layer 1 (interactive layer)	Layer 2 (line overlay)

In order to have the three different sales region colored by their sales we have to provide some sales data. In this case, I’m going the easy way and provide only the following three lines:

So this is how the final report looks like. Note that the country borders do not match the sales region but are still visible (lines layer).

Of course we could also add some major cities or more details but for today, I think we did enough.