I love working with map reports and I don’t miss a chance to fiddle around with it. In fact, I am such a big fan that I think there would be very few sessions (in English, of course!) involving SSRS and maps that I would have missed, both online and live. Also, this is one of those topics that can make me reply on the SSRS forum even when I am at my lazy best. So the other day, someone was asking for a map of Continental Europe that could be used for making his SSRS report. As usual, I tried to go to the site that I always turn when I need a shapefile – http://diva-gis.org. But this was one of those rare occasions where I couldn’t find what I wanted.But that is when I thought of an alternative – create a map of Europe from my database (which already had a world map), embed the spatial data in a report and then send him that report so that he could add it in his Map Gallery.
The source for the maps can either be a shapefile or spatial data. Though I am picking the spatial data as the source for this demonstration, the process of adding a map to the map gallery is going to be similar for both the type of sources. Follow the steps below on how you can add an existing map in your report to the Map Gallery:-
1) I had already got a table called dbo.World which has the spatial data at a country level for all the countries of the world. I ran a query to filter out only for Europe (minus Russia as it was taking up the entire map space, no offence meant) and got the following result.
2) Create a new report and name it Europe.rdl. Make a new data source and dataset after that. Use the same query that was used above as the dataset query.
3) Drag and drop the Map report item from the toolbar and select the SQL Server spatial query as the data source in the map wizard. Click on next.
4) Choose the existing dataset in the next screen and click on next.
5) In the next screen, remember to check the tick box for Embed Map Data in this Report option.
6) After that, keep on clicking next till you reach the Finish button. Click on Finish and you should get the result as shown below.
7) Now save the report and then copy the report rdl from it's source location to C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\MapGallery. Note that the location would be in Program Files and not Program Files (x86) if you are developing in a 32 bit machine.
8) Now you should be able to see the map of Europe in the map gallery when you create a new map in any other report on the same system.
Note that you will need to move the map to C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\ReportBuilder\RptBuilder_3\MapGallery if you need to use within ReportBuilder 3.
Now this rdl that has been created can be shared and that is what I intended to send to the person in the forum, but I found a project in Codeplex which already had a map for Europe - MapGallery of Reporting Services in SQL Server 2008 R2 and sent him a direct link to this. Have a look at the site and also contribute to the project if you have some shapefile which is not there already, so that others can also benefit from it.