With SQL Server 2008 R2 CTP 2 being available, I have been playing around with one of the new features – Report Builder 3.0. One of the new features in this reporting services version is the ability to create Map based reports. Out of the box we will only ship with the map of USA and each of its states. The reasons for this are not at all technical, however to do with potential legal issues – which I wont be going to depth in this post.
However it is very easy to get past this and bring in your countries map inside of RB 3.0. Following are the steps I used to bring in a map of Australia:
1) Download an freely available ESRI shape file for your country or region you want to map. A great resource for this is on the Dundas site itself, located at - http://www.dundas.com/Products/Map/RS/Resources/index.aspx
Click on the region you need a map for, in this case for Australia select Asia and Oceania and then select Australia. This takes you to another page - http://www.vdstech.com/map_data.htm. On this page are freely available shape files, the Australia shape file is at the bottom of the page. Select the australia.zip file.
2) Extract the zip and in it will be australia.shp file
3) Open Report Builder 3.0, and select the Map Wizard on the design surface
4) On the Choose a source of spatial data screen, select the ESRI shapefile radio button, and click on Browse. Navigate to the location of the australia.shp file extracted in Step 2, and click Open.
A preview of the Australia map will appear after clicking Open, then click Next.
5) Configure the zoom and pan of the map as per your requirement and click Next
6) Select Color Analytical Map, click Next
7) Select or add a new data source to your data using the Wizard, click Next (this step is the same as previous versions of Report Builder so I will not be showing it here).
In this case I am connecting to a simple table that contains some sales data for Australia by states:
8) Design the query either using the designer or writing some TSQL. In this case I used the designer as follows:
9) Select the mapping fields between the data in the Map and from the database table. In this case the ADMIN_NAME is the names of the state which are represented in the Name column.
Note: A trick to find out which spatial dataset is useful, select an arbitrary one and map to an field in your table, select next. In the next screen you have the capability of placing the labels for each of these fields on the map and you can scroll through these to find out what the values are.
10) Select a Theme, in this case I changed mine from Ocean to Mahogany, just to bring some brighter colours. Next select the field to visualise, in this case I am interested in the Sales Amount so I selected this field. Select the colour scheme and option choose to display fields, and here you can toggle between the different spatial data fields.
11) Click Run to preview the report
Now with such rich visualisation of data, we can quickly note that our highest sales were in Victoria and Tasmania in this particular case.
Have fun visualising your data in similar ways and empowering your users to make quick decisions. Also with map objects you can enable drill through to other map based reports or other standard reports as well to get down to the granular data.