SQL Server R2 map report creation (III)

SQL Server R2 map report creation (III)

As shown in: click "Next". Next we create a dataset query that contains both spatial data and analytical data. On the Design query page, click Edit as text ". The query designer switches to text-based mode.

Paste the following query statement into the query Pane:

Select 402 as BusinessEntityID, 'impromptu Trips 'as Store, 'mountain' as Specialty, 'Union Gap 'as City, 'Yakima 'as County, CAST (1500000 as money) as AnnualSales, CAST ('point (-120.352399683576 46.5526404267361) 'as geography) AS SpatialLocation UNION ALL SELECT 518 AS BusinessEntityID, 'latest Sports Equipment 'AS StoreName, 'mountain' as Specialty, 'seattle 'AS City, 'King' as County, CAST ('000000' as money) as AnnualSales, CAST ('point (-122.2716149145 47.6252759540999) 'as geography) AS SpatialLocation UNION ALL Select 656 as BusinessEntityID, 'outstanding Cycles 'as Store, 'touring' as Specialty, 'spokane' as City, 'Spokane 'as County, CAST (2000000 as money) as AnnualSales, CAST ('point (-117.458326188527 47.7390846943325) 'as geography) AS SpatialLocation UNION ALL Select 762 as BusinessEntityID, 'dldly Bike Shop 'as Store, 'road' as Specialty, 'belliname' as City, 'Whatcom' as County, CAST (2000000 as money) as AnnualSales, CAST ('point (-122.526710739 48.7985754516) 'as geography) AS SpatialLocation UNION ALL Select 774 as BusinessEntityID, 'ruged' as Store, 'touring' as Specialty, 'everet' as City, 'Snoomish' as County, CAST (3000000 as money) as AnnualSales, CAST ('point (-122.339928832415 48.0034793435206) 'as geography) AS SpatialLocation UNION ALL Select 870 as BusinessEntityID, 'vast Bike AnnualSales and marshal' as Store, 'mountain 'as Specialty, 'chehalis' as City, 'Lewis 'as County, CAST (3000000 as money) as AnnualSales, CAST ('point (-123.173202922628 46.5949185631117) 'as geography) AS SpatialLocation UNION ALL Select 1138 as BusinessEntityID, 'roving Sports 'as Store, 'road' as Specialty, 'puyallup' as City, 'Pierce 'as County, CAST (1500000 as money) as AnnualSales, CAST ('point (-122.141962349474 47.2117618655752) 'as geography) AS SpatialLocation UNION ALL Select 1230 as BusinessEntityID, 'fitness Supplies 'as Store, 'touring' as Specialty, 'sequim' as City, 'Clallim' as County, CAST (800000 as money) as AnnualSales, CAST ('point (-123.165980874577 48.0931018882565) 'as geography) AS SpatialLocation UNION ALL Select 1270 as BusinessEntityID, 'outdoor Sports supply' as Store, 'mountain 'as Specialty, 'North bend' as City, 'King' as County, CAST (300000 as money) as AnnualSales, CAST ('point (-121.50439946838 47.5225521767213) 'as geography) AS SpatialLocation UNION ALL Select 1388 as BusinessEntityID, 'official Parts Shop 'as Store, 'road' as Specialty, 'kelso' as City, 'Cowlitz' as County, CAST (1000000 as money) as AnnualSales, CAST ('point (-122.77961702831 46.1007615739732) 'as geography) AS SpatialLocation UNION ALL Select 1412 as BusinessEntityID, 'city manufacturing' as Store, 'mountain 'as Specialty, 'shelton' as City, 'Mason 'as County, CAST (2500000 as money) as AnnualSales, CAST ('point (-123.323052823833 47.1401103193325) 'as geography) AS SpatialLocation

We can see seven columns in the query result grid:

BusinessEntityID, Store, Specialty, City, County, AnnualSales, and SpatialLocation.

Server R2 map report generation (3) "alt =" SQL Server R2 map report generation (3) "src =" http://s1.sinaimg.cn/middle/66ff669et8429d53958c0&690&690 "action-type =" show-slide "action-data =" http % 3A % 2F % 2Fs1.sinaimg.cn % 2 Fmiddle % 2F66ff669et8429d53958c0% 26690% "real_src =" http://s1.sinaimg.cn/middle/66ff669et8429d53958c0&690&690 ">

This data indicates a group of stores selling AdventureWorks bicycles in Washington state. Each row in the result set contains a store identifier, store name, bicycle type sold exclusively for the store, the city/town where the store is located, annual sales volume, and spatial location represented by longitude and latitude.

Click "Next" to go to the "select space data and map view options" page, as shown in:
Server R2 map report generation (3) "alt =" SQL Server R2 map report generation (3) "src =" http://s4.sinaimg.cn/middle/66ff669et8429d538ce73&690&690 "action-type =" show-slide "action-data =" http % 3A % 2F % 2Fs4.sinaimg.cn % 2 Fmiddle % 2F66ff669et8429d538ce73% 26690% "real_src =" http://s4.sinaimg.cn/middle/66ff669et8429d538ce73&690&690 ">

Verify that the "Space Field" is SpatialLocation and the "layer type" is "point ". Accept other default values on this page. The map view displays circles that mark the location of the store that sells the AdventureWorks product.

Click "Next ".