"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

August 06, 2011

SQL server 2008 R2 - SSRS Maps Feature Explored

Next post is based on my question in MSDN forum. Many thanks to Lola for providing clarity and detailed steps in the answer.

For getting started with SSRS please refer to previous posts

Objective of current exercise
  • Represent Area as a Map
  • Display related data (numbers) in the map
  • Show indications in color based on certain data
For this purpose below SQL Queries would be used
Step 1 - Create a blank report
Step 2 - Create two data sets based on below queries
-- Dataset1 in SSRS
DECLARE @table1 TABLE( id CHAR(10), shape geometry, Value money );
 ('ZoneA','POLYGON((0 0, 0 3, -4 3, -4 0, 0 0))',2000),
 ('ZoneB','POLYGON((0 0, 4 0, 4 3, 0 3, 0 0))',12000),
 ('ZoneC','POLYGON((0 0, 4 0, 4 -3, 0 -3, 0 0))',8000),
 ('ZoneD','POLYGON((0 0, 0 -3, -4 -3, -4 0, 0 0))',10000);

SELECT id, shape,Value
FROM @table1  

Query for Second Data Set

-- DataSet2 in SSRS

DECLARE @table2 TABLE( id CHAR(10), shape geometry, counts int);
 ('PointA','POINT(-4 2)', 100),
 ('PointB','POINT(-4 -1)', 250);

SELECT id,shape, counts
 FROM @table2

Step 3 - After creating Data Set you will find them under report data


Step 4 - Now Drag and Drop Map from Tool box. Choose Spatial query as option and select DataSet1

Step 5 - Select shape polygon to display in the report. Next Step select Analytical map

Step 6 - Select Display Label

Step 7 - Now Click on New Layer Wizard as per below snapshot

Step 8 - Select Dataset for the new layer

Step 9 - Select Label to be displayed

Step 10 - Now you would see both points and polygon layer. Next step is to display data (visualise based on data). Select polygon color rule

Step 11 - Under General -> Select option visualize data by using custom colors

Step 12 - Under Distribution select custom option and provide data range for the same

Step 13- Similarly for points layer provide cusom color and data range as per below snapshot

Step 14 -  Finally, Report based on data, Color based on data range, Visualize data using custom colors. This was a very good learning for me. Thanks Lola for providing detailed steps in MSDN answer for my question.

Happy Learning!!!

No comments: