Archive for July, 2013

SSRS basics – how to visualize spatial data

Posted: July 18, 2013 in MSSQL
Tags:
0
Task: to show how to render spatial data report in SSRS


New spatial data types geometry and geography were introduced in SQL Server 2008 and the new map feature in SQL Server Reporting Services 2008 R2.
The report will use the AdventureWorksDW2012 database and show reseller sales amount by state (US).
The process of creating the map report consists of the following steps:

Code 1: Create datasets


Data Source:
Data Source=localhost;Initial Catalog=AdventureWorksDW2012

Dataset:
dsResellerSales
SELECT g.StateProvinceCode, SUM(f.SalesAmount) AS 'SalesAmount'
FROM dbo.FactResellerSales f JOIN dbo.DimDate d ON d.DateKey = f.ShipDateKey
 JOIN dbo.DimReseller s ON s.ResellerKey = f.ResellerKey
 JOIN dbo.DimGeography g ON g.GeographyKey = s.GeographyKey
WHERE d.CalendarYear = @CalendarYear AND g.CountryRegionCode = 'US'
GROUP BY g.StateProvinceCode

Dataset:
dsOrderYears
SELECT DISTINCT YEAR(f.OrderDate) AS 'OrderYear'
FROM dbo.FactResellerSales f JOIN dbo.DimDate d ON d.DateKey = f.ShipDateKey
 JOIN dbo.DimReseller s ON s.ResellerKey = f.ResellerKey
 JOIN dbo.DimGeography g ON g.GeographyKey = s.GeographyKey
WHERE g.CountryRegionCode = 'US'
ORDER BY 'OrderYear'


Figure 1: New map layer – choose a source of spatial data

Figure 1: New map layer – choose a source of spatial data


Figure 2: New map layer – choose spatial data and map view options

Figure 2: New map layer – choose spatial data and map view options


Figure 3: New map layer – choose map visualization

Figure 3: New map layer – choose map visualization


Figure 4: New map layer – choose the analytical dataset

Figure 4: New map layer – choose the analytical dataset


Figure 5: New map layer – specify the match fields for spatial and analytical data

Figure 5: New map layer – specify the match fields for spatial and analytical data


Figure 6: New map layer – choose color theme and data visualization

Figure 6: New map layer – choose color theme and data visualization


Figure 7: Map layers – right click – map properties

Figure 7: Map layers – right click – map properties


Figure 8: Report parameter properties – calendar year

Figure 8: Report parameter properties – calendar year


Figure 9: Preview reseller sales amount map

Figure 9: Preview reseller sales amount map


Source code:

Additional references: