Map Report Notes
Santosh Gadila
Reporting Services maps are comprised of layers. There are four types of layers, each corresponding to the different types of spatial data that can be displayed in Reporting Services maps:
Polygon – the polygon layer is used to show enclosed areas such as countries or states. A polygon layer in a planar system could also be used to draw shapes such as the rectangles of a heat map
Line – the line layer is used for displaying paths or routes
Point – the point layer is used to display specific locations on a map or a specific coordinate on a plane
Tile – tile layers are used to display Bing maps in reports
Each map has at least one layer. The Map Wizard guides users through the configuration of one map layer. After the map is created, additional layers can be added using the Layer Wizard.
The first step of the Wizard is to specify the source of spatial data for the map. There are three possible sources for spatial data:
Map Gallery – The Map Gallery refers to a set of Reporting Services reports which contain embedded spatial data. By selecting a map from the Map Gallery as the source of spatial data, the spatial data from the map gallery report is in turn embedded in the report being created. By default the Map Gallery for the Report Designer is installed in the folder %ProgramFiles(x86)%\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\MapGallery. The Map Gallery for Report Builder is installed, by default, in the folder %ProgramFiles(x86)%\Microsoft SQL Server\Report Builder 3.0\MapGallery.
ESRI Shapefile – The Environmental Systems Research Institute, Inc. (ESRI) developed and regulates a spatial data format for geographic data. According to the specification, the geographic data is stored in a set of files. Reporting Services requires two files: a .SHP file and a .DBF file. The ESRI specification includes additional files such as a .SHX file or .PRJ file which are not required by Reporting Services. The data from an ESRI shapefile can be embedded in the report or it can be retrieved dynamically at runtime if the .SHP and .DBF files are stored in an accessible location. For a SharePoint integrated instance of Reporting Services this might be a shared document library.
SQL Server Spatial Query – Spatial data stored in SQLGeometry or SQLGeography columns of a SQL Server relational database table can be used as the source data for maps
------------------------------------------------
Select s.BusinessEntityID, s.Name, a.SpatialLocation
From Sales.store s
Inner Join Person.BusinessEntityAddress bea
On s.BusinessEntityID = bea.BusinessEntityID
Inner
Join Person.AddressType at
On bea.AddressTypeID = at.AddressTypeID and at.Name = 'Main Office'
Inner Join Person.Address a
On bea.AddressID = a.AddressID
Inner Join person.StateProvince sp
On a.StateProvinceID
= sp.StateProvinceID
Inner Join sales.SalesTerritory st
On sp.TerritoryID = st.TerritoryID
Inner Join Person.CountryRegion cr
On st.CountryRegionCode = cr.CountryRegionCode
Where cr.name = 'United States'
Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\MapGallery
------------------- MAP REPORT Query----------------------
SELECT DISTINCT DimGeography.StateProvinceName,
SUM(FactResellerSales.OrderQuantity) AS OrderQuantity,
SUM(FactResellerSales.SalesAmount) AS SalesAmount,
DimGeography.City, DimReseller.ResellerName
FROM DimReseller INNER JOIN
FactResellerSales ON
DimReseller.ResellerKey = FactResellerSales.ResellerKey
INNER JOIN
DimGeography ON
DimReseller.GeographyKey = DimGeography.GeographyKey
WHERE (DimGeography.EnglishCountryRegionName = 'United States')
GROUP BY
DimGeography.StateProvinceName, DimGeography.City,
DimReseller.ResellerName