Monday, December 21, 2009

How to drilldown on a reporting server map report in SQL 2008 R2?

In my previous blogpost I described the maps as one of the nice new features of SQL 2008 R2 Reporting Server. These maps can easily be added to your reports if you have a dataset with geography data. In my blogpost: How to enrich your account data with latitude, longitude and geography data? you can add geography data to your dataset. Adding a map in your report for your dataset with geopgraphy data is simple to do. However drilldown or zoom functionality is more difficult to use. This blogpost will explain how you can create maps with drilldown functionality.

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.

  1. Create the stored procedure in your database.
  2. Create a new report
  3. Create a new dataset and select the stored procedure: LocContinentCountryStateCityMap

  4. Report parameters are automatically added to the reports based on the content of the stored procedure: LocContinentCountryStateCityMap.

    Configure all 4 parameters as Hidden and allow NULL values.
    Configure Default value (NULL) for all 4 parameters.

  5. Drag the Map report item to your report. The new map layer wizard is started.

  6. Select SQL Server spatial data.
  7. The wizard will display all datasets with spatial data in it. In this example the resultset of the stored procedure.

  8. Select this dataset and press Next.
  9. 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.
  10. Mark Add a Bing Maps Layer and select a Tile Type: Road.
    Select Layer Type: Point

  11. Press Next and select the Basic Marker Map.
  12. Press Finish.
  13. Now, we have a map with the spatial data and are ready to configure the drilldown functionality.
  14. 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.
  15. 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.
    Enjoy it.


Anonymous said...

Nice dispatch and this fill someone in on helped me alot in my college assignement. Say thank you you on your information.

Clint Huijbers said...

Finally a good example and a smart one too.

Qasim Khan said...

In the tables below you'll see a list of missing indexes if any. For your convenience, Order An Assignment I've also included the tables' existing indexes along with the index type for MySQL <5.5 and some other notes.

Anonymous said...

Maps are no longer limited to the geographical guidance only as now you can even check the materials used in the building of a project through reporting map; this is amazing! I’m considered as one of the Best Assignment Writer as I provide assignment assistance to students which is why I try to keep myself as updated as possible by reading such posts.

joe_chef90 said...

Maps may now also be used to check the materials used in a project's construction through reporting; this is wonderful! Maps are no longer just for geographical navigation! I try to stay as current as I can by reading such postings because I'm one of the buy coursework online and I help students with their assignments.

Ariel Wilson said...

A lot of new apps and tools have been introduced as a replacement for this map server since 2008. In fact; nobody would have believed me in the year 2008 if I said I took dissertation assistance UK-based services from an online platform and managed to get excellent grades with it. However; now it’s all quite common.

james said...

Great Blog! I have go through all the content of this blog.This is really helpful and informative for me.We offer an online gul ahmed shalwar kameez for men at an affordable and reasonable price.

Markcasiol01 said...

Best post, anyone looking to apply for a home loan, I can provide with information and resources to help you through the process. Let's work together to achieve your goals!

Camila Martin said...

I am Emily Griffin working in a renowned organization. With quicken share screen our Quicken Help team may offer you the precise solution for the issues you are coming across. We cannot control your PC but we can just guide you to the ideal place on your screen. You can reach us through the phone number available on our website.We will be very happy to assist you.

Unknown said...

I was very happy that I have seen the things lightly, but life is giving me more chances. once I was working on a sales representative but fail to give 60% above sales but now I am working with International Air Cargo Services and now I am very happy really.