Spatial / Map Reports

I have always been inspired by a particular quote from Arthur C Clarke – “The only way of finding the limits of the possible is by going beyond them into the impossible”. The more you think of it, the more you make sense out of it. If you decide in your mind that something is impossible, you will never even try to make it happen. Consider the example of the 10-second barrier in athletics. Rather than a physical barrier, it has always been more of a psychological barrier. How else can you explain the significantly high numbers of athletes that have broken the barrier in the last 2-3 years when compared to the 20 years between 1968 and 1988? Being mindful of that, I have always tried to be open when discussing requirements and always keep a secret list with me of the requirements that I have not been able to fulfil. One of my earliest list items was that of a squarified Heat Map (or Treemap as they are generally called) in SSRS. Even though there is no out of the box way to do it in SSRS, it was said to be possible by using the map controls and has been shown by Teo Lachev a long time ago. I had even progressed as far as making a T-SQL procedure which calculates the coordinates of the heat maps but it just wasn’t that elegant enough to be used in a production system. However, a recent blog by Richard Mintz on the Squarified Heat Maps has finally made me strike off this requirement from my list as completed.

To implement heat maps in your reports, follow the steps below:-

1) Download the HeatMap.dll file from here and save it in a location in your hard-drive, say C:HeatMap (You should see the Download option on the File Menu). This dll file is the compiled version of the code given in Richard’s blog.

2) Open SQL Server Management Studio and then execute the following code in the database that you want to create the assembly.

CREATE ASSEMBLY HeatMap from ‘c:HeatMapHeatMap.dll’ WITH PERMISSION_SET = SAFE

3) After that, execute the following code to register the stored procedure

CREATE PROCEDURE dbo.CreateHeatMap (@Width real, @Height real, @SqlStrng nvarchar(4000))
AS
EXTERNAL NAME HeatMap.StoredProcedures.TreeMapGeography In case you get an error saying that CLR is not enabled run the code below to enable CLR before creating the procedure again EXEC sp_configure ‘show advanced options’ , ‘1’;
go
reconfigure;
go
EXEC sp_configure ‘clr enabled’ , ‘1’
go
reconfigure;
— Turn advanced options back off
EXEC sp_configure ‘show advanced options’ , ‘0’;
go

4) Now create a new report and a new dataset. For this report, I am going to be using a SQL query from the AdventureWorks database which will give me the Subcategory Name and Order Quantity. Order quantity will be the measure which will determine the size of the rectangles within the heat map. Enter the code below for the dataset

exec dbo.CreateHeatMap 20, 25, ‘select  sum(FIS.OrderQuantity), EnglishProductSubcategoryName
from dbo.DimProductSubCategory SC
inner join DimProduct P
on SC.ProductSubcategoryKey=P.ProductSubcategoryKey
inner join dbo.FactResellerSales FIS
on FIS.ProductKey =P.ProductKey
group by EnglishProductSubcategoryName
order by  sum(FIS.OrderQuantity)  desc’

The first 2 parameters within the CreateMap procedure are used for setting the width and the height of the heat map and this can be customized as per your needs. The third parameter is basically the select statement which should return the measure used for determining the rectangle sizes as well as the names used for labelling the rectangles within the heat map. Ensure that the Text option has been selected for the query and it should look like shown below

5) Now make another dataset which will have the analytical data used for visualizing the rectangles with colours. For this demo, I am using a MDX query from the AdventureWorks cube which will return the Subcategory Names and Reseller Gross Profit.

6) Drag and drop a Map report item from the toolbox to the design layout and select SQL Server Spatial query as the data source. On the next screen, select the  spatial dataset which we had created in Step 4.

7) On the next screen, ensure that Geo is the spatial field and the Layer type is Polygon. Click next.

8) Choose Color Analytical Map  the map visualization and click next. Then choose the dataset that we created in step 5 as the analytical dataset.

9) Specify the match fields as Name and SubCategory as shown in the screenshot below and click on Next

10) In the next screen, choose the field to visualize as Reseller Gross Profit and click on Finish.

11) Now if the report is previewed, you can see a nice heat map which will have the dimension size based on the Order Quantity and the colours visualized based on the Gross Profit.

If are interested in map/spatial reports, you might want to check out some of my related posts here.

Update (11/03/2012) To make it culture insensitive, the code has been modified and a new version of the compiled code has been posted here.

I love working with map reports and I don’t miss a chance to fiddle around with it. In fact, I am such a big fan that I think there would be very few sessions (in English, of course!) involving SSRS and maps that I would have missed, both online and live. Also, this is one of those topics that can make me reply on the SSRS forum even when I am at my lazy best. So the other day, someone was asking for a map of Continental Europe that could be used for making his SSRS report. As usual, I tried to go to the site that I always turn when I need a shapefile – http://diva-gis.org. But this was one of those rare occasions where I couldn’t find what I wanted.But that is when I thought of an alternative – create a map of Europe from my database (which already had a world map), embed the spatial data in a report and then send him that report so that he could add it in his Map Gallery.

The source for the maps can either be a shapefile or spatial data. Though I am picking the spatial data as the source for this demonstration, the process of adding a map to the map gallery is going to be similar for both the type of sources. Follow the steps below on how you can add an existing map in your report to the Map Gallery:-

1) I had already got a table called dbo.World which has the spatial data at a country level for all the countries of the world. I ran a query to filter out only for Europe (minus Russia as it was taking up the entire map space, no offence meant) and got the following result.

2) Create a new report and name it Europe.rdl. Make a new data source and dataset after that. Use the same query that was used above as the dataset query.

3) Drag and drop the Map report item from the toolbar and select the SQL Server spatial query as the data source in the map wizard. Click on next.

4) Choose the existing dataset in the next screen and click on next.

5) In the next screen, remember to check the tick box for Embed Map Data in this Report option.

6) After that, keep on clicking next till you reach the Finish button. Click on Finish and you should get the result as shown below.

7) Now save the report and then copy the report rdl from it’s source location to C:Program Files (x86)Microsoft Visual Studio 9.0Common7IDEPrivateAssembliesMapGallery. Note that the location would be in Program Files and not Program Files (x86) if you are developing in a 32 bit machine.

8) Now you should be able to see the map of Europe in the map gallery when you create a new map in any other report on the same system.

Note that you will need to move the map to C:Program FilesMicrosoft SQL ServerMSRS10_50.MSSQLSERVERReporting ServicesReportServerReportBuilderRptBuilder_3MapGallery if you need to use within ReportBuilder 3. Now this rdl that has been created can be shared and that is what I intended to send to the person in the forum, but I found a project in Codeplex which already had a map for Europe – MapGallery of Reporting Services in SQL Server 2008 R2 and sent him a direct link to this. Have a look at the site and also contribute to the project if you have some shapefile which is not there already, so that others can also benefit from it.

I don’t know about you guys, but I am really petrified of public speaking. So I always try to read about it and it really gives me some comfort to know that I am not alone. In fact, if your fear of public speaking is between mild anxiety and complete terror, you are said to be well within the normal range. I would not be exaggerating if I said that some studies show that there are people who rate their fear of public speaking as more severe than fear of death. Last year was when I finally decided to embrace my fear and I managed to speak at a local user group event in London and also at SQLBits (trust me, it wasn’t that hard as I thought it would be). And since now is the time everyone makes resolutions, I was trying to take some hint from 50 New Year’s Resolutions for Public Speakers and watching my presentation video from the last SQLBits. Seeing it, I realized I had missed mentioning an important point regarding map reports and hence decided to blog about it before I get too lazy.

People who are familiar with map report development would already be aware of the size issues when dealing with shapefiles (If you are a complete newbie on map reports, I would strongly recommend you to go through my presentation video). Most of the shapefiles are in MBs and since this data is embedded in the report RDLs, the size of the RDL also goes into MBs. The problem as well as the solution are demonstrated below:-

1) Make a simple report from a shapefile by selecting the Basic Map option in the Map Wizard. I haven’t linked the shapefile to a dataset for simplicity purpose.

2) Now you can verify that the shapefile data has been embedded in the report by right clicking on the report in the solution explorer, and selecting the view code option.

You can see that the territory names are there in the rdl code.

3) Now deploy the report to the report server and then, download the rdl to check the size.

You can see that the rdl size is 3.11 MB. The shapefile that I had used for this report was 2.4 MB.

4) Now to solve this issue, we will have to upload the shapefile (.shp and .dbf files) to the report server. For this, navigate to the folder in Report Manager and click on upload. Then browse to the shp and dbf files and click ok.

5) Once this is done, go back to the report in BIDS and click on the map twice to bring the map layers panel on the right. Right click on the polygon layer and select Layer data option.

6) Now change the option from Data Embedded in Report to Link to ESRI Shapefile. Now give the location of the files in your report server (In this case, Blog ReportsCOL_adm1.shp). Note that the location has to start with a slash followed by the folder names within the report manager and then the file name of the shp file.

7) Once this is done, you will not be able to view the results in BIDS. You can view the report code to confirm that the map data is not embedded in the rdl now. Just deploy the report after that and preview it in report manager to ensure that it is working. 8) Now, download the rdl from the report server and you can see that the size has been considerably reduced, from over 3 MB to 12.2 KB.

It would actually be a good practice to store your shapefiles in the report server at the end of your development. This way, the shapefiles can be reused by multiple reports if needed. Also, you would not need to open BIDS in case an updated version of the shapefile becomes available later. This technique can also be used for report images though I see little benefit from it as usually the images are only a few KBs. As for performance, I quickly checked a couple of times and the rendering time was a little less using this technique. Maybe I will do a detailed performance test and post a blog on it later. Till then, adieus amigos!

Nowadays, the project that I am working on is keeping me quite busy and all the documentation work is driving me nuts (you wouldn’t believe me if I said I hate documentation ). But somehow, I do find time to respond to the steady flow of queries that keep coming  to me. And today, when one of my colleagues asked me how to implement bubble heatmap in SSRS map reports, I couldn’t resist the temptation to try it out at home and blog it down. For demonstrating the solution, I have downloaded a map of Africa along with some statistics on AIDS. Follow the steps below to implement the solution:- 1) Import the shapefile of Africa into the database (Read the first part of Import Shapefiles into SQL Server and Aggregate Spatial Data (Geometry) for more details). This is not a necessary step, but I find it easier to deal with spatial data in a table rather than reading from the shapefile at runtime or embedding it within the report. 2) For the demonstration purpose, I have created two datasets in BIDS – one which will give the statistics on AIDS and the other one which will return the spatial data from the database. 3) Now drag and drop a Map item from the toolbox and select the source of spatial data as SQL Server spatial query. Click Next. 4) Choose the dataset which has the spatial data and click on next. 5) In the next screen, the spatial field should automatically be detected and we can click on next straightaway. 6) Choose Color Analytical Map as the visualization technique and click next. 7) Choose the analytical dataset containing the statistics in the screen that comes and then click on next. 8) Specify the common field in the spatial and analytical dataset which would be used for linking the map and the statistics. In this case, it is the Country field. Click on next. 9) In the next screen, choose any of the option as we will soon be disabling the visualization in the next step. Click on Finish. 10) Click twice on the map and press F4 to bring the MapPolygonLayer properties. Now disable polygon color rule as shown in the image below 11) Right click on the map and select the Show Center Points option 12) Now, on right clicking the map again, the Center Point properties should be enabled and we can change the Center Point Color rule and Center Point Size rule. 13) After the above step and bit more messing up with the aesthetics, we end up with the final end result as shown below I am writing this post well past my bed time and so if I have missed out any vital steps, feel free to ask them in the comments section. I should be more than happy to detail them out for you. Time to catch a few winks now… Update (15/03/2011) I had forgot to mention that this approach is for polygon based maps. If you have got a point based map, then the wizard itself will give you options to set bubble size and colour.

I have been always interested in the potential of map reports. So naturally, when I found out a site which had quite a number of shapefiles for free, I decided to blog something just for the fun of working with map reports. Being around in the forums, I had seen a lot of posts asking how to drill down or zoom to the next level in map reports and I always had to explain the answer in words. I decided to take this opportunity to blog down the answer with some pictures. To implement this, I downloaded the shapefiles of India and followed the following steps :- 1) For the demo, I have used the administrative level 1 (States) and administrative level 2 (Districts) of India. The 2 above mentioned shapefiles are imported into the database (to see how to import shapefiles to tables, refer to Import Shapefiles into SQL Server and Aggregate Spatial Data (Geometry)) as the size of the shapefiles was around 2 MB and that would slow down the report. 2) Make a basic map report for the States with the source of spatial data as a SQL Server spatial query. The above report shows colouring by sales for each state. The dataset used for this report is shown below SELECT NAME_1 AS State, Sales, geom AS Geo
FROM     IND_adm1 The above query returns all the states for India. 3) Make the second report which shows a similar report for the next level. The dataset used for the query is given below SELECT NAME_2 AS District, Sales, geom AS Geo, NAME_1 AS State
FROM     IND_adm2
WHERE  (NAME_1 = @PAR_State) The important part to note here is the WHERE condition. This query returns all the districts for the state which is equal to the report parameter. Since this query is used as the source of the map, the map report will automatically show just the selected state. 4) Make a report parameter PAR_State in the second report and give a default value of any state, for eg, Kerala. Check whether the report is working by previewing it. 5) Go back to the first report and specify an action to the second report. For this, right click on the map and select Polygon Properties. Then go to the Action tab and pass the State field to the PAR_State report parameter as shown below 6) Click on OK and deploy both the reports. Now you should be able to click on the states and drill down to the districts as shown below. Now this might not be the only way to achieve drill down in map reports but I hope this gives you an idea on how to further play around with it and get your results. Meanwhile, for those extra attentive guys who must be wondering how I managed to change the background images for each state, I have uploaded a different image for each state in the database (No, I am not getting paid by the Indian Tourism Board for the extra effort ) and used it based on the state (refer SSRS reports using database images). Have fun and festive greetings in advance…