Pages

Thursday, March 1, 2012

Adding Maps to SSRS Map Gallery

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.

Add Maps to SSRS 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.

1 Filtered query for Europe

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.

2 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.

3 Source for spatial data 

4) Choose the existing dataset in the next screen and click on next.

4 Choose dataset

5) In the next screen, remember to check the tick box for Embed Map Data in this Report option.

5 Embed map data in report

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.

6 Report design

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.

7 Add report to map gallery

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.

8 View map in map gallery

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.

11 comments:

  1. Hi Jason,

    Thanks for the post.My client had problem where he want me to show USA Map. But some place names mentioned in the database doesn't map to the SSRS USA Map. Also on click of a state, he wants the state to blow up and shows the details.
    Can you please suggest me a solution for this?
    Thanks
    Pallavi
    pallavi13jain@gmail.com

    ReplyDelete
  2. Hi Pallavi,
    Some options are
    - You could try changing the names in the database to match the map
    - If the data in the shapefile is embedded in the report, you can click on the Polygon Layer, and in the Polygon Layer property panel, go to Spatial Data-->Polygons and edit the collection. There you will get the names of each Polygon and you can change it accordingly
    - You could import your map data also in the database and then it is just a question of editing the table fields.

    As for the drill down feature, refer my blog below -
    http://road-blogs.blogspot.com/2010/12/drill-down-in-ssrs-map-reports.html

    Let me know if you need further help

    ReplyDelete
    Replies
    1. Thanks Jason. I will try the solutions suggested

      Delete
  3. Hi Jason.

    Thanks for the tip about the Codeplex project. Great article as usual.

    Ásgeir Gunnarsson

    ReplyDelete
  4. Hi Jason,

    I just now came to know that you have implemented treemaps purely with SQL Server in SSRS. Iam eagerly waiting for ur post. Have to showcase the same in my project.
    Regards,
    Pallavi

    ReplyDelete
    Replies
    1. @Pallavi: Can you try the method here - http://road-blogs.blogspot.com/2012/03/heat-maps-for-ssrs-using-map-control.html ? If it doesn't work, let me know and we can discuss it further.

      Delete
  5. Awesome Article Jason !!! You rock !!!

    ReplyDelete
  6. Very good job Jason.

    It's exacyly that I'm looking for.

    Can you share with me your SQL "World" table.

    Thank you very much in advance

    Ramon

    ReplyDelete
  7. hi Jason,
    Good job, its very clear and i am able to made a report with some sample data and its fine
    i made a report base don my requirement i made it using shp file and rdl file as well, its finally displaying blacka nd white report only, not displaying color and tool tips, can you please help me solving this..

    Thanks in Advance..sri

    ReplyDelete
  8. how to add maps layer to toolbox development studio 2008 (SSRS)

    ReplyDelete