Spatial / Map Reports

Heat Maps for SSRS using Map Control

Heat Maps for SSRS using Map Control

March 10, 2012

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.

HeatMaps in SSRS

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

Spatial Dataset

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.

Analyticaal Dataset

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.

Choose spatial dataset

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.

Choose analytical dataset

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

Specify the match fields

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

Fields to visualize

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.

HeatMap Preview

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.

Posted by SQLJason, 13 comments
Adding Maps to SSRS Map Gallery

Adding Maps to SSRS Map Gallery

March 2, 2012

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.

Add Maps to SSRS 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.

1 Filtered query for Europe

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.

2 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.

3 Source for spatial data

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

4 Choose dataset

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

5 Embed map data in report

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.

6 Report design

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.

7 Add report to map gallery

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.

8 View map in map gallery

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.

Posted by SQLJason, 14 comments
Reducing SSRS RDL size by uploading Shapefiles to ReportServer

Reducing SSRS RDL size by uploading Shapefiles to ReportServer

January 15, 2012

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.

REDUCING RDL SIZE BY UPLOADING SHAPEFILES TO REPORTSERVER

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.

1 map report from Colombia shapefile

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.

2 embedded spatial data in report rdl

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.

3 Shapefile rdl 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.

4 Upload shp and dbf to reportserver

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.

5 Selecting layer data in map layers

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.

6 Linking the map to the uploaded files in reportserver

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.

7 Modified Shapefile reduced rdl size

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!

Posted by SQLJason, 2 comments
Fast Track to Spatial Reporting – SQLBits session material for download

Fast Track to Spatial Reporting – SQLBits session material for download

October 2, 2011

So one more objective struck off from my annual list. Even though I had nightmares where my laptop crashed just before my session, nothing untoward happened and I managed to complete my first session at SQLBits without any problems. SQLBits 9 Query Across the Merseyside Speaker The session was scheduled on Oct 1 at 8.10 am in the morning (at a time when I would normally be snoring in my bed). There was a brief moment of confusion when I discovered around 7.30 am that my laptop did not have the VGA port for plugging in the projector (the new DELL systems just have the HDMI and mini USB ports). Luckily, James Boother (twitter | blog) and Neil Hambly (twitter | blog) helped me get a converter and salvaged the situation. I was able to complete all the demos within the stipulated 1 hour time though I thought I had to race at some parts. Anyways, I will be blogging about my overall SQLBits 9 experience in detail soon. Till then, feel free to download the materials that I used in my session below:-

  • Session slides – contains the PowerPoint presentation (2 MB)
  • Report RDLs – contains all the report files used in the demo (12 MB)
  • Database backup – contains the spatial database used for the reports (41 MB)
  • Demo notes – short notes describing the demo (5 KB)

 

Posted by SQLJason, 5 comments
Bubble Heatmap in SSRS Map Reports

Bubble Heatmap in SSRS Map Reports

February 25, 2011

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 Smile). 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. Bubble Map Report 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. [Shape2SQL for geometry shapefile conversion[9].png] 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. 1 Prepare Datasets 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. 2 Choose source of spatial data 4) Choose the dataset which has the spatial data and click on next. 3 Choose spatial dataset 5) In the next screen, the spatial field should automatically be detected and we can click on next straightaway. 4 Choose spatial data 6) Choose Color Analytical Map as the visualization technique and click next. 5 Choose map visualization 7) Choose the analytical dataset containing the statistics in the screen that comes and then click on next. 6 choose analytical dataset 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. 7 Specify match fields 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. 8 Choose color theme 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 9 Disable polygon color rule 11) Right click on the map and select the Show Center Points option 10 Select Show Center Points 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. 11 Center Point size and color 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 Bubble heatmap 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…Sleepy smile 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.

Posted by SQLJason, 6 comments
Drill down in SSRS Map Reports

Drill down in SSRS Map Reports

December 19, 2010

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. State report 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 Dataset 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) Dataset Districts 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. Kerala 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. 1 Action - Polygon Properties Then go to the Action tab and pass the State field to the PAR_State report parameter as shown below 2 Action - Report parameter passing 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. Screen Captures 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 Smile) and used it based on the state (refer SSRS reports using database images). Have fun and festive greetings in advance…

Posted by SQLJason, 3 comments
Import Shapefiles into SQL Server and Aggregate Spatial Data (Geometry)

Import Shapefiles into SQL Server and Aggregate Spatial Data (Geometry)

September 4, 2010

Around 6 months ago, I was desperately looking for some online resources to help me implement some map reports. The shapefile I was dealing with was at a very granular level (which means that it had a lot of data, 2.5 MB in size), and we needed to do some custom aggregations on the spatial data. As the custom aggregations were done at report run time, it turned out to be a major performance bottleneck. That was when this blog by Sean Boon – SQL Server 2008 R2 Map Tips: How To Import Shapefiles Into SQL Server and Aggregate Spatial Data, caught my eye and I got the idea of importing my shapefile into DB and then doing the custom aggregations there itself for solving my performance problems. Armed with the contents from this blog, I started developing my reporting solution but soon stumbled upon a major roadblock – I realized that the blog he had written was for aggregating shapefiles of Geography data type, while the shapefile I had with me was of Geometry data type (read here to find the difference between the two data types). After a couple of days of searching, I finally managed to find the solution and thought of blogging it down at least now.

Map report

The major steps involved in the solution are:-

1) Converting Shapefiles into SQL Server Spatial Data : As mentioned in Sean’s blog, we will be using the tool from http://www.sharpgis.net/page/Shape2SQL.aspx called Shape2SQL. Download the software from the site and then click on the exe to see the user interface. If you go to the site, you will find an image of the interface which guides you how to convert the shapefile into SQL Server Spatial data but then that is for a shapefile of Geography data type. So how do you convert a shapefile of Geometry data type into spatial data? For that, make sure that the interface looks like the image given below:-

Shape2SQL for geometry shapefile conversion

For some reason, I had to create the table beforehand, the tool didn’t automatically create the table as I thought it would. The table creation script is also given below so that you can have an idea of the columns

CREATE TABLE [dbo].[MapData](
[geom] [geometry] NULL,
[PC4CODE] [nvarchar](max) NULL,
[Sales Rep] [nvarchar](max) NULL)

So as you can see, in my table, I would have all the PC4Code information (which is the postal code) at the end of this exercise. Now I update my table such that I get all the parent information in the [Sales Rep] column (which would have all the list of sales regions). My requirement is that instead of having a map by some 4000 odd postal codes, I would like to see a map by just the 40 odd sales regions that I have. A simple select from the MapData table would look like this now:-

NL Map - Detailed

2) Aggregate Polygons in SQL Server – Get assembly : For aggregating polygons of geography data type, we can use the method that Sean has mentioned in his blog. But that particular function would not work in the case of geometry data type. For doing this, please follow the steps mentioned in ConceptDev (Craig Dunn’s blog) quoted below

DOWNLOAD the source (8k) to try out the two aggregates posted on MSDN, or follow these steps:

1. Download the code from MSDN, or my copy. NOTE: You must do all these steps on a PC with SQL Server 2008 (Katmai) or above installed to get the Microsoft.SqlServer.Types.dll assembly. I copied this assembly into C:assemblies.

2. Compile the C# SQLCLR functions in C:assemblies via the Command Prompt

c:windowsmicrosoft.netframeworkv3.5csc /t:library /r:Microsoft.SqlServer.Types.dll C:assembliesaggregate.cs

3. Execute T-SQL to register the aggregate functions in SQL Server 2008
CREATE ASSEMBLY SpatialAggregate FROM ‘c:assembliesAggregate.dll’
go
CREATE AGGREGATE EnvelopeAggregate(@input GEOMETRY) RETURNS GEOMETRY
external name [SpatialAggregate].[SilverlightEarth.Geoquery.SqlClr.EnvelopeAggregate]
go
CREATE AGGREGATE UnionAggregate(@input GEOMETRY) RETURNS GEOMETRY
external name [SpatialAggregate].[SilverlightEarth.Geoquery.SqlClr.UnionAggregate]
go

sp_configure ‘clr enabled‘, 1;
go
RECONFIGURE;
go

3) Aggregate Polygons in SQL Server – Use function to aggregate data and store in table : Now that you have the aggregate functions registered, you can use it in a query to aggregate it and store it in a table like shown below:-

select WAM=M.[Sales_Rep],
geom=dbo.UnionAggregate(geom.MakeValid())
into NL_Map
from MapData M
group by M.[Sales_Rep]

The UnionAggregate function will aggregate the data and the aggregated data can be viewed by a simple select on the new NL_Map table

NL Map - Aggregated to sales region

Now you are all set to use this table as the source of your reports.

Update (25/03/2012)

SQL 2012 has integrated these features and hence if you are using SQL 2012, you can aggregate both geography and geometry data types using the built in functions Geometry::UnionAggregate(geom) and Geography::UnionAggregate(geog). Refer the blog below http://social.technet.microsoft.com/wiki/contents/articles/4136.aspx

“ UnionAggregate() combines multiple spatial objects into a single spatial object, removing interior boundaries, where applicable.
SELECT Geography::UnionAggregate(geog) FROM Counties
WHERE name = ‘Washington’;

Posted by SQLJason, 9 comments