Report Information
Description Purpose: Drillthrough report from store markers in Sales_by_Region_2008R2. Displays information about customers who live within the specified number of miles from a store. The store geolocation is a String parameter that contains a Well Known Text (WKT) value.
Author Mary Lingel MSFT
ReportID 602b0e43-347c-4a5b-8326-d0c562c9ddcb
Data Sources
AdventureWorks2008R2
Name AdventureWorks2008R2
Security Type None
Datasets
CustomerLocations
Name CustomerLocations
Query
Data Source AdventureWorks2008R2
CommandText SELECT c.CustomerID, c.PersonID AS BusinessEntityID, p.FirstName AS Name1, p.LastName AS Name2, ea.EmailAddress, ad.City, ad.PostalCode,
sp.Name AS StateProvince, sp.StateProvinceCode, ad.SpatialLocation.STDistance(@GeoLocation) / 1609.344 AS DistanceinMiles, ad.SpatialLocation,
p.Demographics.value('declare namespace awns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; (awns:IndividualSurvey/awns:NumberCarsOwned) [1]',
'int') AS NumberCarsOwned,
p.Demographics.value('declare namespace awns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; (awns:IndividualSurvey/awns:CommuteDistance) [1]',
'varchar(30)') AS CommuteDistance
FROM Sales.Customer AS c INNER JOIN
Person.Person AS p ON p.BusinessEntityID = c.PersonID INNER JOIN
Person.BusinessEntityAddress AS a ON a.BusinessEntityID = p.BusinessEntityID INNER JOIN
Person.AddressType AS t ON a.AddressTypeID = t.AddressTypeID INNER JOIN
Person.Address AS ad ON ad.AddressID = a.AddressID INNER JOIN
Person.EmailAddress AS ea ON ea.BusinessEntityID = p.BusinessEntityID INNER JOIN
Person.StateProvince AS sp ON sp.StateProvinceID = ad.StateProvinceID
WHERE (c.StoreID IS NULL) AND (t.Name = N'Home') AND (sp.CountryRegionCode = N'US') AND (ad.SpatialLocation.STDistance(@GeoLocation) / 1609.344 < @Radius)
Parameters
@GeoLocation =Parameters!GeoLocation.Value
@Radius =Parameters!Radius.Value
Fields
CustomerID
Name CustomerID
Source CustomerID
Data Type Int32
BusinessEntityID
Name BusinessEntityID
Source BusinessEntityID
Data Type Int32
Name1
Name Name1
Source Name1
Data Type String
Name2
Name Name2
Source Name2
Data Type String
EmailAddress
Name EmailAddress
Source EmailAddress
Data Type String
City
Name City
Source City
Data Type String
PostalCode
Name PostalCode
Source PostalCode
Data Type String
StateProvince
Name StateProvince
Source StateProvince
Data Type String
StateProvinceCode
Name StateProvinceCode
Source StateProvinceCode
Data Type String
DistanceinMiles
Name DistanceinMiles
Source DistanceinMiles
Data Type Double
SpatialLocation
Name SpatialLocation
Source SpatialLocation
Data Type Microsoft.SqlServer.Types.SqlGeography
NumberCarsOwned
Name NumberCarsOwned
Source NumberCarsOwned
Data Type Int32
CommuteDistance
Name CommuteDistance
Source CommuteDistance
Data Type String
StoreLocation
Name StoreLocation
Query
Data Source AdventureWorks2008R2
CommandText SELECT
[StoreID] as BusinessEntityID
, s.Name as Store
-- , t.Name as AddressType -- Main Office or Shipping
, ad.PostalCode
-- , sp.CountryRegionCode
--, sp.Name as StateProvince
--, sp.StateProvinceCode
, ad.SpatialLocation.ToString() as GeoLocation
FROM [Sales].[Customer] c
INNER JOIN Sales.Store s ON s.BusinessEntityID = c.StoreID
INNER JOIN Person.BusinessEntityAddress a ON a.BusinessEntityID = s.BusinessEntityID
INNER JOIN Person.AddressType t ON a.AddressTypeID=t.AddressTypeID
INNER JOIN Person.[Address] ad ON ad.AddressID = a.AddressID
INNER JOIN Person.StateProvince sp ON sp.StateProvinceID = ad.StateProvinceID
WHERE -- PersonID IS NULL means Customer is a store
PersonID IS NULL
AND t.Name = N'Main Office'
AND sp.CountryRegionCode = N'US'
Parameters
Fields
BusinessEntityID
Name BusinessEntityID
Source BusinessEntityID
Data Type Int32
Store
Name Store
Source Store
Data Type String
PostalCode
Name PostalCode
Source PostalCode
Data Type String
GeoLocation
Name GeoLocation
Source GeoLocation
Data Type String
Parameters
GeoLocation
Name GeoLocation
Data Type String
Prompt Store location?
Default Value
Dataset Reference
Values POINT (-82.4151596338717 40.7459610884615)
Valid Value
Dataset Reference
Dataset Name StoreLocation
Label Store
Value GeoLocation
Radius
Name Radius
Data Type Integer
Prompt Distance in miles?
Default Value
Dataset Reference
Values 100
Valid Value
Dataset Reference
ShowAll
Name ShowAll
Data Type Boolean
Prompt Show all information?
Default Value
Dataset Reference
Values false
Valid Value
Dataset Reference
Embedded Images
AdventureWorksLogo
Name AdventureWorksLogo
Type jpeg
Page Header
Report Items
Logo
Name Logo
Type Image
Source Type Embedded Image Name
Embedded Image Name AdventureWorksLogo
ReportName
Name ReportName
Type Text Box
Content =Globals!ReportName
tbBackInstructions
Name tbBackInstructions
Type Text Box
Content To return to the main report, use the browser Back button.
tbTitle
Name tbTitle
Type Text Box
Content Demographics for the =CountDistinct(Fields!CustomerID.Value,"CustomerLocations") =IIF(CountDistinct(Fields!CustomerID.Value,"CustomerLocations")=1,"Customer","Customers")
living within =Parameters!Radius.Value miles of the store
=Parameters!GeoLocation.Label
Grouped by Commute Distance
Body
Report Items
Textbox3
Name Textbox3
Type Text Box
Content Name
Textbox5
Name Textbox5
Type Text Box
Content Distancein Miles
Textbox7
Name Textbox7
Type Text Box
Content Number Cars Owned
Textbox11
Name Textbox11
Type Text Box
Content Email Address
Name2
Name Name2
Type Text Box
Content =Fields!Name2.Value, =Fields!Name1.Value
DistanceinMiles
Name DistanceinMiles
Type Text Box
Content =Fields!DistanceinMiles.Value
NumberCarsOwned
Name NumberCarsOwned
Type Text Box
Content =Fields!NumberCarsOwned.Value
EmailAddress
Name EmailAddress
Type Text Box
Content =Fields!EmailAddress.Value
Textbox13
Name Textbox13
Type Text Box
Content Commute Distance
CommuteDistance
Name CommuteDistance
Type Text Box
Content =Fields!CommuteDistance.Value
Data Regions
tblxTable_Demographics
Name tblxTable_Demographics
Type Table
Row Member 1
Row Member 2
CommuteDistance
Name CommuteDistance
Expressions =Fields!CommuteDistance.Value
Sort Expressions =Fields!CommuteDistance.Value
Row Member 2
Details
Name Details
Source Dataset CustomerLocations
Page Footer
Report Items
tbDescription
Name tbDescription
Type Text Box
Content Purpose: Drillthrough report from store markers in Sales_by_Region_2008R2. Displays information about customers who live within the specified number of miles from a store. The store geolocation is a String parameter that contains a Well Known Text (WKT) value.
Visibility
Show or hide based on expression =NOT Parameters!ShowAll.Value