Drill down functionality in Maps (SSRS 2008 R2)

One of the nice new features in SSRS 2008 R2 is the Map Tool, it provides a Bing Maps visualization of your dataset (uses spatial data).
It’s not that difficult to setup, but a drill down functionality would be nice and it requires just one stored procedure!
sp_GetAllPersonsLocations
Note: The filter on State/Province will only be used for the US, since it’s a big country.

In this example I used the new AdventureWorks2008R2 database and the first query is a big one (on my laptop).

So far for the SP and its data, let’s begin with creating the report.
Start with creating a new report dataset:

Press OK, BIDS will automatically create four parameters corresponding to the SP.
Make sure the ‘All null value’ option is selected and the default values are NULLs.

Now reopen the dataset and modify the parameter-settings:

Drag a Map component onto the report.
Within the Map Wizard, select SQL Server spatial query and press Next. Select the existing dataset (in my case dsSpatialPersonsAddresses) and press Next.
You’ll receive an error ‘Unable to determine the spatial data type due to connection error’, this is correct because the stored procedure need some parameter values which are not known by the wizard. Ignore the error and press OK. Notice that ‘SpatialLocation’ has already been selected for Spatial Field. Set Layer type to ‘Point’ and select ‘Add a Bing Maps layer’. The result:

Press Next, select ‘Basic Marker Map’ and press Next again.
Now press Finish and view the result:

The final step is to setup the Map layer Point properties, in order to enable the drill down functionality.

Go to the Action tab and select the option ‘Go to report’. Now select the report your making and setup the parameter properties.

The same report will be started every time, but with different parameter values. These parameter values are added one by one after every drill down in the stored procedure. Publish the report and you’re done!

Advertisements

4 thoughts on “Drill down functionality in Maps (SSRS 2008 R2)

    • Hi!
      Sure no problem, just post my name and blog-url as the source 🙂

      Readers may want to check if there are any changes made to the scripts or additional comments provided by readers.

  1. Incredible! This blog looks just like my old
    one! It’s on a totally different subject but it has pretty much the same layout and design. Wonderful choice of colors!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s