Hilmar Buchta

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

image

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
DEU_adm2.* administrative regions
DEU_adm3.* most detailed

 

image image image image
DEU_adm0 DEU_adm1 DEU_adm2 DEU_adm3

 

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.

image

After loading DEU_adm1.shp, DEU_adm2.shp and DEU_adm3.shp you will find three new database tables in your database:

image

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:

image

image

Next step is to add a dataset for our map data. Here are the steps:

image

image

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
union all
select 2 as MapLevel, ID_1,NAME_1,ID_2,NAME_2,null,null,geom, NAME_2 from dbo.DEU_adm2
union all
select 1 as MapLevel, ID_1,NAME_1,null,null,null,null,geom, NAME_1 from dbo.DEU_adm1
)
select * from mapdata
where
    (ID_1=@LocID1 OR @LocID1 Is NULL)
and (ID_2=@LocID2 OR @LocID2 Is NULL)
and (ID_3=@LocID3 OR @LocID3 Is NULL)
and MapLevel=@MapLevel

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):

image

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:

image

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:

image

For the MapLevel parameter (and only for this one), we’re going to define an initial detail level of 1:

image

Now we’re ready to add the map to the report. From the toolbox simply drag’n’drop a map on your report:

image

After adding the map we have to decide for a data source. Of course we’re using the SQL Server here:

image

Next, we need to choose from our datasets. Since we only have defined one, it’s an easy choice:

image

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:

image

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:

image

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…”:

image

On the General Tab we define the [Lbl] field of our query as the label for our map data:

image

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)

image

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.

image

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.