Interactive PowerPivot Dashboard (custom map visualization)

I’ve found this great example of an interactive PowerPivot worksheet by Robert Mundigl about the beer prices at Munich’s Oktoberfest.


How it works?
Actually it’s about the same technic as Kay Unkroth used earlier in one of my previous posts.
Well…here’s how Robert did it!:


Have a look at Robert Mundigl‘s blogpost and his tutorial: Click here.

The example is also downloadable via this URL.

Join us at LinkedIn!
Don’t forget to join the SQL Server 2014 group on LinkedIn and stay updated!:

ESRI shapefiles

Download your ESRI shapefiles here!


The data are available as shapefile, ESRI geodatabase, RData, and Google Earth kmz format. Shapefiles can be used for most mapping and “GIS” software (you can download a free program such as Q-GIS or DIVA-GIS. The RData files can be used in R with the ‘sp’ package loaded.
You can download country level files (recommended) or files covering the entire world

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!
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!