SQL Server 2008R2 SSRS
In this post I will show how to implement drill down into a more detailed view for a SQL Server 2K8 R2 Reporting Services (SSRS) map.
STEP 1. Acquiring map data and importing the data into a SQL Server database
For this example, I’m going to use a map of Germany. The map can be downloaded from http://www.diva-gis.org/gData
From my download I got a ZIP-file containing the ESRI shape files needed for import. For our needs it is important that we get different levels of granularity:
|DEU_adm0.*||Borders of Germany|
|DEU_adm1.*||Map at German state level|
STEP 2. Loading the map into a SQL Server database
I’m using Shape2sql here. The tool can be downloaded from http://www.sharpgis.net/page/sql-server-2008-spatial-tools.aspx. In order to actually load the data files into a database, we first have to create this database. In my case, it’s located on my localhost machine with the name GIS (just an empty database).
The following screenshot shows the loading of the DEU_adm3.shp file. In my case I had to deactivate “Create Spatial Index” as I got an error with this one.
After loading DEU_adm1.shp, DEU_adm2.shp and DEU_adm3.shp you will find three new database tables in your database:
We can now use SQL Server Management Studio or SQL Spatial Query Visualizer (contained in the download for Shape2sql above) to visualize our maps. I used the latter one to create the map screenshots shown above.
STEP 3. Creating the report
Now we can actually start building our report. I created a new Report Server project using Business Intelligence Development Studio. In this report project I created a shared data source to connect to my GIS database.
Then we can finally start with the report. I created a new report and added the shared data source to my report. Here are the steps:
Next step is to add a dataset for our map data. Here are the steps:
For the query, copy and paste the following SQL query into the Query field:
with mapdata as (
select 3 as MapLevel, ID_1,NAME_1,ID_2,NAME_2,ID_3,NAME_3,geom, NAME_3 As Lbl from dbo.DEU_adm3
select 2 as MapLevel, ID_1,NAME_1,ID_2,NAME_2,null,null,geom, NAME_2 from dbo.DEU_adm2
select 1 as MapLevel, ID_1,NAME_1,null,null,null,null,geom, NAME_1 from dbo.DEU_adm1
select * from mapdata
(ID_1=@LocID1 OR @LocID1 Is NULL)
and (ID_2=@LocID2 OR @LocID2 Is NULL)
and (ID_3=@LocID3 OR @LocID3 Is NULL)
Note, that this query actually defines a union of all different layers of detail into one result set. If you like you can also look at the query results in Management Studio (of course you have to remove the where condition and I would recommend to order by id_1, id_2, id_3):
Here you can see, how the detail levels (field MapLevel) are adding more information.
Let’s go back to our report. After adding the query you will notice that the parameters of the query are automatically added to the report as shown below:
For our report, we hide all parameters by setting the visibility option to hidden. Also, we allow NULL-values for each parameter. Here are the details:
For the MapLevel parameter (and only for this one), we’re going to define an initial detail level of 1:
Now we’re ready to add the map to the report. From the toolbox simply drag’n’drop a map on your report:
After adding the map we have to decide for a data source. Of course we’re using the SQL Server here:
Next, we need to choose from our datasets. Since we only have defined one, it’s an easy choice:
I got an error message here saying that the data could not be loaded. We ignore this error here. In the next screen leave the defaults and just press “Next”.
Now for the map type, choose the basic map and continue:
Finally you can decide for a style (which I leave up to you) and you’re done. You should now see a map (not with our data) on your report. You can still make some changes with the map (move it around, add a title, remove the legend).
When using “Preview” you should already be able to see a map like this:
STEP 4. Adding Drill-Down
If you click into the map, you should be able to see an additional layer menu to the right of the map. From this menu open the context menu for the first and only layer and click “Polygon Properties…”:
On the General Tab we define the [Lbl] field of our query as the label for our map data:
On the action tab, we define the actual drill-down. In this case, we’re going to link our report with itself (just choose “Go To Report” and select the name of the current report from the drop-down list). Next we have to map all the parameters. Click the “Add” button four times and add the following mappings:
LocID1 –> [First(ID_1)]
LocID2 –> [First(ID_2)]
LocID3 –> [First(ID_3)]
MapLevel –> =iif(Parameters!MapLevel.Value>=3,3,Parameters!MapLevel.Value+1)
By doing so we’re going to link our report parameters to the selected area on the map (id columns of the different elements). Also, we increment the MapLevel with each click to get to the next detailed level as shown in the expression for the MapLevel (use the expression editor to compose the above expression or simply copy it into the editor).
That’s all we need to do in order to create the report. We can now test it in the Visual Studio environment or deploy it to a report server. This is how the report looks in the report server if you first click on “Nordrhein-Westfalen” and then on “Düsseldorf”. Simply use the browser’s back-button to go back one step.
Of course you can also use some data aware shading of your map, but for the current post, I’ll leave it as it is.