Drill down in SSRS Map Reports

I have been always interested in the potential of map reports. So naturally, when I found out a site which had quite a number of shapefiles for free, I decided to blog something just for the fun of working with map reports. Being around in the forums, I had seen a lot of posts asking how to drill down or zoom to the next level in map reports and I always had to explain the answer in words. I decided to take this opportunity to blog down the answer with some pictures. To implement this, I downloaded the shapefiles of India and followed the following steps :- 1) For the demo, I have used the administrative level 1 (States) and administrative level 2 (Districts) of India. The 2 above mentioned shapefiles are imported into the database (to see how to import shapefiles to tables, refer to Import Shapefiles into SQL Server and Aggregate Spatial Data (Geometry)) as the size of the shapefiles was around 2 MB and that would slow down the report. 2) Make a basic map report for the States with the source of spatial data as a SQL Server spatial query. The above report shows colouring by sales for each state. The dataset used for this report is shown below SELECT NAME_1 AS State, Sales, geom AS Geo
FROM IND_adm1 The above query returns all the states for India. 3) Make the second report which shows a similar report for the next level. The dataset used for the query is given below SELECT NAME_2 AS District, Sales, geom AS Geo, NAME_1 AS State
FROM IND_adm2
WHERE (NAME_1 = @PAR_State) The important part to note here is the WHERE condition. This query returns all the districts for the state which is equal to the report parameter. Since this query is used as the source of the map, the map report will automatically show just the selected state. 4) Make a report parameter PAR_State in the second report and give a default value of any state, for eg, Kerala. Check whether the report is working by previewing it. 5) Go back to the first report and specify an action to the second report. For this, right click on the map and select Polygon Properties. Then go to the Action tab and pass the State field to the PAR_State report parameter as shown below 6) Click on OK and deploy both the reports. Now you should be able to click on the states and drill down to the districts as shown below. Now this might not be the only way to achieve drill down in map reports but I hope this gives you an idea on how to further play around with it and get your results. Meanwhile, for those extra attentive guys who must be wondering how I managed to change the background images for each state, I have uploaded a different image for each state in the database (No, I am not getting paid by the Indian Tourism Board for the extra effort ) and used it based on the state (refer SSRS reports using database images). Have fun and festive greetings in advance…