The Reporting map control make use of Bing maps which automatically centers and zooms in to the locations of your dataset. To drilldown or zoom we need to start the same report with a smaller dataset. To create a smaller dataset I make use of the data awareness of the reporting map control. When I click on a map point the values of the map point will be used to make the dataset smaller. I created a stored procedure with some parameters. These parameters gets their values from the data awareness of the reporting map control. The stored procedure will drilldown from World, Continent, Country, State to City. I have a table with accounts. Every account has their Countrycode, Statecode and City. I used a Join to the continents table in which every country in the world is linked to a continent. Please download the stored procedure from this location.
- Create the stored procedure in your database.
- Create a new report
- Create a new dataset and select the stored procedure: LocContinentCountryStateCityMap
- Report parameters are automatically added to the reports based on the content of the stored procedure: LocContinentCountryStateCityMap.
Configure Default value (NULL) for all 4 parameters.
- Drag the Map report item to your report. The new map layer wizard is started.
- Select SQL Server spatial data.
- The wizard will display all datasets with spatial data in it. In this example the resultset of the stored procedure.
- Select this dataset and press Next.
- Now you get 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. Press OK.
- Mark Add a Bing Maps Layer and select a Tile Type: Road.
Select Layer Type: Point
- Press Next and select the Basic Marker Map.
- Press Finish.
- Now, we have a map with the spatial data and are ready to configure the drilldown functionality.
- Drilldown will make use of the data awareness of the reporting map control. Click on a map point and right click with your mouse. Select Point properties. If you get Viewport Properties, you have not select the map point.
- Select Action.
Go to report: Specify the report you are making. Add the 4 parameters to this report action.
This means the report will start the same report. However with other parameter values. These parameter values are added one by one after every drill down in the stored procedure.
Now the report is ready to run and drill down.