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

Maps Example - SQL Server 2008 R2

In this example we will look at SSRS reports using Maps. Beginning Spatial is very good place to get started on learning Spatial Data types and maps. We will look at simple example of consuming below query provided in beginning spatial to consume in report.

DECLARE @table1 TABLE( id CHAR(1), shape geometry );

INSERT INTO @table1 VALUES
('A','POLYGON((0 0, 4 0, 4 1, 0 0))'),
('B','POLYGON((4 2, 3 2, 3 3, 4 2))'),
('C','POLYGON((7 3, 7 1, 8 1, 7 3))'),
('D','POLYGON((8 3, 11 3, 11 5, 8 3))');

SELECT

id,
shape
FROM @table1;


Let's get started

Step 1- Create blank report and add map as shown below

Step 2 - Add Dataset

Step 3 - Add Data Source

Step 4 - Add below query

Step 5 - Ignore Error

Step 6 - Choose Spatial Data

Step 7 - Map visualization option

Step 8 - Choose Dataset to Display

Step 9 - Data visualization and Scheme

Step 10 - Report Preview

Happy Learning!!!

SQL Server 2008 R2 Examples

List of Detailed Step-by-Step Examples on SQL Server 2008 R2 Features

• Creating a Map Report with a Spatial Data Query
• Creating a Chart with a Text Annotation in an ASP.NET Web Application
• Exporting a Report Feed from SQL Server 2008 R2 Reporting Services

Complete List of Exercises - SQL Server 2008 R2 - Reporting Services


Happy Learning!!!

SQL Server 2008 R2 Feature - Data-tier applications

Downloaded and Installed SQL 2008 R2 Bits. Link
Data Tier Application - data-tier application (DAC) defines the SQL Server Database Engine schemas and objects that are required to support an application. Link. All the elements of Database - Schema, Procs, Triggers, Views can be extracted into a Data Tier App Project. This is same as reverse engineer feature we have in VSDB 2008 where we can extract all objects from a database in a database project.

DAC is available from SQL 2008 R2 onwards. VSDB project supports from SQL 2005, 2008, R2 versions.
Created a Test Database and created few tables and Procs
--STEP 1
CREATE TABLE TESTTable1
( Id INT Identity(1,1),
Name VARCHAR(50))

INSERT INTO TESTTable1(Name)
SELECT 'Test'

CREATE PROC TestProc
(@Id Int)
AS
BEGIN
 SELECT * FROM TESTTable1 (NOLOCK) WHERE Id = @Id
END

--STEP 2
Extracting Data Tier Application. Right click on Database->Tasks->Extract Data Tier Application

After this provide properties and save the Package

Created a Datatier App in VSTS 2010

Import the Created Data Tier Application from the package

After Successful Import, You Can view the tables, procedures created in it.

Now if you want to deploy this in a database. Choose Project->Properties->Under Deploy Options->Specify the connection to directly deploy it. I am able to successfully build it. On trying to deploy I got the error Invalid object name 'msdb.dbo.sysdac_instances'. I am trying to deploy of SQL 2008 Instance. It need to be R2/CTP3 to support Data Tier Deployment.

More Reads

Microsoft SQL Server Management: Developing Managed Data-Tier Applications


Happy Learning!!!