So, SQL Server Reporting Services 2008 R2 now supports maps as data layout controls. This allows us to create reports such as the one indicated below, with no custom development, natively within Reporting Services.
This opens up massive possibilities, as it supports the display of geospatial data in one of three map formats:
- Built-in maps
- Maps of the USA are supported
- I saw a demo of world maps at SQL PASS, looking forward to CTP3 to seeing them in real life
- Any ESRI shapefile can be used. This is particularly powerful as you can represent data for your country down to any level supported by the particular shapefile employed at the time.
- This is the most exciting option for me. While SQL Server 2008 introduced GeoSpatial data types in the database, there was no simple method to display these shapes to users. So, now it is possible to expose geography and geometry data types visually through reports.
With that preamble, I have been experimenting with the ESRI shapefile option. I needed to employ this approach as I wanted to represent country level data for the world (and the CTP2 doesn’t support non-US maps out of the box, really looking forward to CTP3). I was able to develop the report through the wizard, and assign the relevant data fields to the relevant shapefile fields with no problem. However, when rendering the report, I received the following error:
System.Web.Services.Protocols.SoapException: There was an exception running the extensions specified in the config file. —> System.Web.HttpException: Maximum request length exceeded.
— End of inner exception stack trace —
at System.Web.Services.Protocols.ServerProtocol.SetContext(Type type, HttpContext context, HttpRequest request, HttpResponse response)
at System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest request, HttpResponse response, Boolean& abortProcessing)
Turns out that the Report Server service limits the HTTP Request size by default. Not exactly sure what the default limit is, however it is definitely smaller than the 6Mb required for my shapefile. To fix this setting, simply update the httpRuntime tag in the web.config file in the ReportServer folder. Specify a maxRequestLength attribute to the size required and you’re in the game. So the tag should look something like:
<httpRuntime executionTimeout=”9000″ maxRequestLength = “1024000” />
Watch this space for SQL 2008 R2 lessons learnt in the real world, as I use CTP3 in a prototyping environment over the next few weeks.