Yes, there are other articles on maps in Reporting Services, but none as good as this one so let's get cracking.
Widget production
I'm going to be using data from the Markysan widget production corp to demonstrate a few techniques.I want a visual representation of what widget production is doing in geographic regions.
I want to know how many widgets were made in each marketing region and where the highly productive factories are around the country.
To do this I will need not only the business (widget count by postcode) data, but also spatial data that can identify where all the factories are, on a map.
Having the data is only half the battle though. By getting to know the map control, some nifty results can be achieved.
Don't laugh, I think it looks nice. Besides, this article is about knowing how to use the map control, rather than user experience, so there.
This report has embedded in it, the spatial polygon outlines of England, Scotland, Wales and Cornwall.
If you looked at the data embedded in this report it would be like any other polygon spatial data, one record for each polygon.
I have my own postcode area boundary data I like to use. I say "my own" because I spent effort converting the source KML file that my good friend Brian found with his superior interwebs searching skills, into a usable SQL Server spatial dataset.
The basic gist was constructing the T-SQL insert statements from the pairs of latitude/longitude coordinate pairs that are in KML files.
It was a bit of a mission converting both the OS code point open data and the postcode area polygons to SQL spatial data, but satisfying and well worth it. I'll do an article on that when/if I get a chance.
As I won't be actually using the embedded spatial data set in this example, I should really delete that layer to keep the .rdl file size down.
I have added a second polygon layer to the map control, based on my postcode area data.
This data set could be embedded into the report along with the country boundary one and turned into a handy report template. Layers could then be hidden or deleted depending on the scenario.
Having this spatial data available is only useful for a few scenarios:
1) You can use the polygons to represent your data in some way. As both spatial datasets are regional boundary polygons, their use is obviously limited to expressing measures by region.
My marketing department gave me lookup data that grouped postcode areas into their marketing regions. This kind of data can be used to create any arbitrary geographic regions. They just have to be based on postcode areas, because that is what the polygons in the dataset represent.
Of course, you can create you own polygon boundary data manually, which is something I looked into, but it looked a big job broke out so good luck with that.
In the case of non postcode based spatial polygons your choices are limited I'm afraid. Either make them yourself or pay someone else to do it.
There is the Bing Maps integration option. While a neat feature, I don't always like the look of the results with the Bing map in the background, unless you are desperate for a background. At certain zoom levels it just looks naff.
It would be pertinent to use it if you were doing something with roads or routes though.
2) The can also be used simply as backgrounds, with more point or polygon layers on top.
3) Navigation. A small(ish) map control can be the region picker for a second map control that shows a higher level of detail.
I'll cover this in a future chapter to this article, but I'm talking about both the map on the left and the links in the central table, being used to control the second map on the right:
The map control on the left has no other purposes than giving an indication of the boundaries of the marketing regions and easier parameter selection for the more detailed map control on the right.
I have so far explored two:
I knocked up a C sharp console app to do the conversion and insert each record into a table. The end result is useful, detailed data.
For postcode-based data sets, it is well worth exposing all the elements of the postcode with computed columns, derived from the full postcode value.
There are 1.6 million rows in the table. One for every UK postcode. It is quite big and can be difficult to move around due to it's sheer size.
Creating the point column as a computed column:
CREATE TABLE [dbo].[PostCodeOutCodePoints](
[id] [int] IDENTITY(1,1) NOT NULL,
[outcode] [varchar](4) NOT NULL,
[lat] [varchar](20) NOT NULL,
[lng] [varchar](50) NOT NULL,
Coords AS GEOGRAPHY::Point(lat,lng,4326)
(
Just so you know, I use the terms 'out code' and 'districts' interchangeably in relation to UK postcodes.
Once dressed up, this dataset look like this:
There is one record for each postcode outcode/district; PL13, GL21, W1 etc.
The spatial data is a point. I have not investigated where these points lie in their respective district boundaries.
If you don't know already, a third 'Spatial results' tab appears in the query results pane when there is spatial data in the results. This is a great way of checking things before you get to the report.
Having the centre points for different levels of regional boundaries can be very useful.
A regional drill-down map report is going to need to plot data point somewhere inside the polygon in the layer behind. It will all look a bit wonky if points are not close to the central point of the polygon they are associated with.
In the past I have had to make my own code-points for regions. I ended up just using the coordinates of the first postcode sector in each district.
Then do the same thing for areas. Use the coordinates of the first postcode district/sector, for each area. Those are likely to be near the centre most of the time. If they end up wonky, then adjust them.
Complete postcodes are made up of outcode and incode elements:
The out-code is the area+district, up-to the first 4 characters, (PL13).
The in-code is the sector+unit, and is always 3 characters. (2NG).
The space in a full postcode is really just there for the benefit of readability for us humans. Full postcodes are actually 7 characters long. In-codes shorter than 4 are right-padded to 4 chars.
The best way to deal with them in my experience is to store them with all spaces stripped. That way you don't need to worry about the length, or whether there's a space there or not.
Providing the postcode is complete, the right-hand 3 characters will always be the in-code. Everything to the left of that will be the out-code.
You'll just have to take steps to protect your queries from incomplete postcodes coming from the source system.
In this case I will be using the small, out code point dataset together with the out codes from the widget production factories to produce a data set that only contains spatial points for where a widget factory exists.
Region, outcode and point coordinates for all of my widget production complexes:
Here, we are going to use a simple data set that has a widget count for each factory out code:
Widget counts for each district:
This dataset will be used for the analytical aspects of the map, dictating both the size and the colour of each of the data points.
The PostcodeAreas dataset is my postcode area boundary data, which is only used as a background layer in this report.
The analytical 'WidgetCounts', and the spatial 'WidgetLocations' datasets.
Add a new Point Layer to the control and configure it with the WidgetLocations dataset.
Don't bother with the Layer wizard unless you really have to.
Use the WidgetCounts dataset to configure the 'Analytical data' section:
Previewing the report now shows the default 10 pixel point for each factory, as we haven't configured the analytical properties of the control.
Open the Point Size Rule properties and set up the size rules:
These are the default settings but please play around with all the different properties, previewing the report each time, to find the best looking result for your data.
Widget production figures are now indicated through the size of the point:
It is all looking a but busy, but let's configure the colour rule before adding any parameters to the report and datasets.
There are a few options here but I have gone for a custom colour range:
Now then, this is looking more like it:
Polygon Spatial Data
I will start with the only map of the UK in the Codeplex map gallery. The site isn't exactly well stocked in the maps department but the TEngScotWales.rdl report is a good starting point for what I have planned here.This report has embedded in it, the spatial polygon outlines of England, Scotland, Wales and Cornwall.
If you looked at the data embedded in this report it would be like any other polygon spatial data, one record for each polygon.
I have my own postcode area boundary data I like to use. I say "my own" because I spent effort converting the source KML file that my good friend Brian found with his superior interwebs searching skills, into a usable SQL Server spatial dataset.
The basic gist was constructing the T-SQL insert statements from the pairs of latitude/longitude coordinate pairs that are in KML files.
It was a bit of a mission converting both the OS code point open data and the postcode area polygons to SQL spatial data, but satisfying and well worth it. I'll do an article on that when/if I get a chance.
As I won't be actually using the embedded spatial data set in this example, I should really delete that layer to keep the .rdl file size down.
I have added a second polygon layer to the map control, based on my postcode area data.
This data set could be embedded into the report along with the country boundary one and turned into a handy report template. Layers could then be hidden or deleted depending on the scenario.
The postcode area polygons are more detailed than the country polygons.
Having this spatial data available is only useful for a few scenarios:
1) You can use the polygons to represent your data in some way. As both spatial datasets are regional boundary polygons, their use is obviously limited to expressing measures by region.
My marketing department gave me lookup data that grouped postcode areas into their marketing regions. This kind of data can be used to create any arbitrary geographic regions. They just have to be based on postcode areas, because that is what the polygons in the dataset represent.
Of course, you can create you own polygon boundary data manually, which is something I looked into, but it looked a big job broke out so good luck with that.
In the case of non postcode based spatial polygons your choices are limited I'm afraid. Either make them yourself or pay someone else to do it.
There is the Bing Maps integration option. While a neat feature, I don't always like the look of the results with the Bing map in the background, unless you are desperate for a background. At certain zoom levels it just looks naff.
It would be pertinent to use it if you were doing something with roads or routes though.
2) The can also be used simply as backgrounds, with more point or polygon layers on top.
I think these simple polygon outlines look nice and clean.
3) Navigation. A small(ish) map control can be the region picker for a second map control that shows a higher level of detail.
I'll cover this in a future chapter to this article, but I'm talking about both the map on the left and the links in the central table, being used to control the second map on the right:
The map control on the left has no other purposes than giving an indication of the boundaries of the marketing regions and easier parameter selection for the more detailed map control on the right.
Code-Point Spatial Data
There are probably many way of getting the code-point data into SQL Server table as spatial data.I have so far explored two:
The hard way
Get the full OS Code-Point Open dataset from data.uk.gov. When I did this I had to convert the OS eastings and nothings to longitude and latitude in degrees. I believe the download now contains long/lat coordinates, but don't quote me on that.I knocked up a C sharp console app to do the conversion and insert each record into a table. The end result is useful, detailed data.
For postcode-based data sets, it is well worth exposing all the elements of the postcode with computed columns, derived from the full postcode value.
There are 1.6 million rows in the table. One for every UK postcode. It is quite big and can be difficult to move around due to it's sheer size.
An easier-but-lower-resolution way
An easier, but lower data resolution option is to use a smaller out code based (2,972 rows), dataset available from the nice people at Free Map Tools. You just need to derive the geographic point column yourself, from the latitude and longitude values in the dataset.Creating the point column as a computed column:
CREATE TABLE [dbo].[PostCodeOutCodePoints](
[id] [int] IDENTITY(1,1) NOT NULL,
[outcode] [varchar](4) NOT NULL,
[lat] [varchar](20) NOT NULL,
[lng] [varchar](50) NOT NULL,
Coords AS GEOGRAPHY::Point(lat,lng,4326)
(
Just so you know, I use the terms 'out code' and 'districts' interchangeably in relation to UK postcodes.
Once dressed up, this dataset look like this:
There is one record for each postcode outcode/district; PL13, GL21, W1 etc.
The spatial data is a point. I have not investigated where these points lie in their respective district boundaries.
If you don't know already, a third 'Spatial results' tab appears in the query results pane when there is spatial data in the results. This is a great way of checking things before you get to the report.
Having the centre points for different levels of regional boundaries can be very useful.
A regional drill-down map report is going to need to plot data point somewhere inside the polygon in the layer behind. It will all look a bit wonky if points are not close to the central point of the polygon they are associated with.
In the past I have had to make my own code-points for regions. I ended up just using the coordinates of the first postcode sector in each district.
Then do the same thing for areas. Use the coordinates of the first postcode district/sector, for each area. Those are likely to be near the centre most of the time. If they end up wonky, then adjust them.
The anatomy of UK postcodes.
| PL | Area - usually an abbreviation of the postal town, unless in London. They often cover whole counties or 'administrative boundaries' as they are now. |
| PL13 | District |
| PL13 2 | Sector |
| PL13 2NG | Unit - Street-level resolution. Postcode units are 15 households on average. Working at this level, it would make sense to use the Bing Maps integration. |
Complete postcodes are made up of outcode and incode elements:
The out-code is the area+district, up-to the first 4 characters, (PL13).
The in-code is the sector+unit, and is always 3 characters. (2NG).
The space in a full postcode is really just there for the benefit of readability for us humans. Full postcodes are actually 7 characters long. In-codes shorter than 4 are right-padded to 4 chars.
The best way to deal with them in my experience is to store them with all spaces stripped. That way you don't need to worry about the length, or whether there's a space there or not.
Providing the postcode is complete, the right-hand 3 characters will always be the in-code. Everything to the left of that will be the out-code.
You'll just have to take steps to protect your queries from incomplete postcodes coming from the source system.
Business Spatial Data
Building a spatial data set for use in Reporting Services involves joining business data with any of the spatial data sets available to you.In this case I will be using the small, out code point dataset together with the out codes from the widget production factories to produce a data set that only contains spatial points for where a widget factory exists.
Region, outcode and point coordinates for all of my widget production complexes:
Business Analytical Data
The Spatial data set is responsible for drawing the points on the map. But we need more data from the business to use as the analytical data set in the report.Here, we are going to use a simple data set that has a widget count for each factory out code:
Widget counts for each district:
This dataset will be used for the analytical aspects of the map, dictating both the size and the colour of each of the data points.
The Report
Start with whatever report template you have then add the spatial and analytical datasets to it:The PostcodeAreas dataset is my postcode area boundary data, which is only used as a background layer in this report.
The analytical 'WidgetCounts', and the spatial 'WidgetLocations' datasets.
Don't bother with the Layer wizard unless you really have to.
Use the WidgetCounts dataset to configure the 'Analytical data' section:
The only available option for fields to match on between the two datasets I am using in this example, is the outcode field.
Previewing the report now shows the default 10 pixel point for each factory, as we haven't configured the analytical properties of the control.
Open the Point Size Rule properties and set up the size rules:
These are the default settings but please play around with all the different properties, previewing the report each time, to find the best looking result for your data.
Widget production figures are now indicated through the size of the point:
There are a few options here but I have gone for a custom colour range:
Now then, this is looking more like it:
Regional focus.
Remember that I included the marketing region in the WidgetLocations dataset? The report parameter is going to work with this to limit the results to a single region.Add a predicate with a parameter to the dataset query, create a Region report parameter and wire it all up. I shouldn't need to tell you how to do this so I won't waste time here.
I opted for a single-select drop-down list of values. I'll probably ending up hiding the parameter on the report itself as I have other plans in the parameter selection department.
So now the results are filtered by region:
With the default viewport settings the map control is auto-centering on the datapoints but not zooming in. This is why it still looks a bit busy.
I'm doing this in SQL Server 2008 R2 by the way, for not particular reason. I don't think there are that many differences in Reporting Services 2012 but I'll check if anything is relevant to these ramblings.
Map Viewport properties.
I normally leave the General tab alone, except for the projection setting. I recommend trying each of the projections to see which one you like the best. They all result in different aspect ratios, some of them being quite different.
There is an issue with 2008 R2 where the 'Auto' Zoom level doesn't do what it should do.
The workaround is to set the expression for the Zoom level to ="Auto".
Note to self: Check that this has been fixed in 2012.
With the Zoom level expression hacked and the 'Center map to show all data-bound map elements' option selected, the whole map control now zooms in to the data points that are now filtered by the parameter, which works nicely and looks proper-job.
Parameter Navigation
I have always thought that parameters selection is a bit naff for the user. Surely by now, in this day and age, everything should be thought controlled?We have an opportunity with this particular report to lose the pesky parameter drop down, replacing it with something better.
Create a new dataset that contains region names. Use it to populate a table next to the map and arrange for the action of the region textbox to link to itself, the same report as you are in, passing the region as the @Region parameter.
The parameter and the wiring-up to the dataset obviously needs to stay as it is for this to work as the textbox actions will still be passing the region value, to itself, as a parameter.
Hide the Region parameter if you like.
The usual thing to do next would be to arrange for a drill-through to a more detailed report based on the region.
One more thing before I go. Set the tooltip property of the points in the point layer's point properties pages.
In the next part I'll be talking about using the polygons in reports.
fin.
No comments:
Post a Comment
Note: only a member of this blog may post a comment.