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 );

INSERT INTO @table1 VALUES

 ('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);

INSERT INTO @table2 VALUES

 ('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!!!