• Article

Creating the Customers_Near_Stores_2008R2 Report (SSRS)

This tutorial helps you build the Customers_Near_Stores_2008R2 report from the suite of AdventureWorks 2008R2 sample reports.

This report displays a list of customers and customer demographics for individual AdventureWorks customers who live within a specified radius of a geolocation. The geolocation is passed in as a String parameter that contains Well Known Text (WKT) representation of a geographic location. For more information about the suite of reports and their relationships, see Tutorials: Creating AdventureWorks 2008R2 Sample Reports (SSRS).

What You Will Learn

In this tutorial you will to do the following tasks:

  • Add a dataset with individual customer information, including the distance from the home address to a specified location.

  • Add a dataset with reseller store information.

  • Configure a parameter that contains a geolocation as text.

  • Configure a parameter that specifies a distance from the store.

  • Display the data source, dataset, and chosen parameter values on the last page of the report.

  • Add a report title that includes information about the purpose of the report.

  • Add the report description.

  • Add a table to display customer demographics for customers that live within the specified distance of the specified store.

  • Define a NoRowsMessage to display when no customers live within the specified distance.

  • Preview and verify the report.

Estimated time to complete this tutorial: 15 minutes.

Requirements

For the list of requirements, see Prerequisites for AdventureWorks 2008R2 Sample Reports (SSRS).

This tutorial assumes that you have completed Creating the Report Server Project and the AdventureWorks2008R2_Base Report (SSRS).

To open the project and make a copy of a report

  1. In Business Intelligence Development Studio, open the report server project AdventureWorks 2008R2.

  2. In Solution Explorer, do the following:

    1. Right-click the report AdventureWorks2008R2_Base.rdl, and then click Copy.

    2. Right-click the project node, and then click Paste.

    3. Rename the copied report to Customers_Near_Stores_2008R2.rdl.

To create the dataset for customer information

  • In the Report Data pane, add an embedded dataset named CustomerLocations. Use the AdventureWorks2008R2 shared data source and the following query:

    SELECT
    [CustomerID]
   , [PersonID] as BusinessEntityID 
   , p.FirstName as Name1
   , p.LastName AS Name2
   , ea.EmailAddress
  -- , t.Name as AddressType -- Home or Shipping
   , 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="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; (awns:IndividualSurvey/awns:NumberCarsOwned) [1]','int') as NumberCarsOwned
   , p.Demographics.value('declare namespace awns="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; (awns:IndividualSurvey/awns:CommuteDistance) [1]','varchar(30)') as CommuteDistance
  FROM [Sales].[Customer] c
   INNER JOIN Person.Person p ON p.BusinessEntityID = c.PersonID
   INNER JOIN Person.BusinessEntityAddress a ON a.BusinessEntityID = p.BusinessEntityID
   INNER JOIN Person.AddressType t ON a.AddressTypeID = t.AddressTypeID
   INNER JOIN Person.[Address] ad ON ad.AddressID = a.AddressID 
   INNER JOIN Person.EmailAddress ea ON ea.BusinessEntityID = p.BusinessEntityID
   INNER JOIN Person.StateProvince sp ON sp.StateProvinceID = ad.StateProvinceID
 WHERE   -- StoreID IS NULL means Customer is an Individual
    StoreID IS NULL 
    AND t.Name = N'Home' 
    AND sp.CountryRegionCode = N'US'
    -- 1 Mile = 1609.344 Meters 
    AND (ad.SpatialLocation.STDistance((@GeoLocation))/1609.344) < (@Radius)

The dataset query returns customer information, including customer demographics, for customers who live within @Radius miles of @GeoLocation.

In the report, this data is displayed in the matrix.

To create a valid values list for the Geolocation parameter

  • In the Report Data pane, add an embedded dataset named StoreLocation. Use the AdventureWorks2008R2 shared data source and the following query:

    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'

The dataset query returns a list of the main offices of AdventureWorks stores and their geolocations.

In the report, this data is displayed as a list of valid values for the @Geolocation parameter. On the report viewer toolbar, the display shows the parameter label that contains the store name, not the value that contains the geolocation.

To configure the report parameter @Geolocation

  1. Open Parameter Properties for @Geolocation.

  2. Change Prompt to Store location?

  3. On Available Values, select Get values from a query.

  4. For Dataset, select StoreLocation.

  5. For Value, select GeoLocation.

  6. For Label, select Store.

  7. On Default Values, add a value and set it to the following text:

    POINT (-82.4151596338717 40.7459610884615)

This value corresponds to the store named The Bike Mechanics.

To configure the report parameter @Radius

  1. Open the parameter properties for @Radius.

  2. In Prompt, type Distance in miles?

  3. Change Data type to Integer.

  4. On Default Values, add a value and set it to 100.

To add to the page header instructions to return to the parent report

  1. Expand the height of the page header.

  2. Beneath the logo, add a text box named tbBackInstructions with the following text: Use the browser Back button to return to the parent report.

  3. Format the text box as needed.

To add the report title

  1. At the top of the report, below the page header, add a text box named tbTitle with the following text:

    Demographics for the [NCustomers] [Customers]

    living within [@Radius] miles of the store

    [@GeoLocation.Label]

    Grouped by Commute Distance

  2. Right-click [NCustomers] to open the Placeholder Properties dialog box, and set Name to NCustomers and Value to =CountDistinct(Fields!CustomerID.Value,"CustomerLocations").

  3. Right-click [Customers] to open the Placeholder Properties dialog box, and set Name to Customers and Value to =IIF(CountDistinct(Fields!CustomerID.Value,"CustomerLocations")=1,"Customer","Customers").

  4. Center the text box on the report and format each line of text as needed.

To delete the scenario text boxes

  • Delete the ToggleInformation text box and the text box that contains the scenario text.

For this report, the scenario information is included in the report title that you just added.

Add the Description

To add the description

  1. In the page footer, replace the text in description text box with the following text:

    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.

  2. Click the report background to display Report Properties in the property pane.

  3. In Description, paste the description text.

Display Customer Demographics in a Table

To add the table

  1. Add a table named tblxTable_Demographics that displays the following fields from the CustomerLocations dataset:

    • [Name2], [Name1]

    • [DistanceinMiles]

    • [NumberCarsOwned]

    • [EmailAddress]

  2. Add a row group based on [CommuteDistance].

  3. Format the table as needed.

To add a message to display when there is no data

  1. Select the table.

  2. In the Properties pane, find NoRowsMessage, and type the following expression: ="No customers live within " & Parameters!Radius.Value & " miles. Display the parameters on the toolbar and enter a different distance."

Preview and Verify the Report

To preview and verify the report

  • Run the report and verify the following:

    1. The parameter values appear in the report title.

    2. The table is organized by commute distance.

    3. When you change the value of the radius parameter, the number of customers changes.

    4. When there are no customers within the specified distance, you see the NoRowsMessage value.

Next Steps

You have completed building this report. To build other AdventureWorks sample reports, see Tutorials: Creating AdventureWorks 2008R2 Sample Reports (SSRS).

See Also

Other Resources

Tutorials (SSRS)

Change History

Updated content

  • Changed report to be based on AdventureWorks2008R2_Base.rdl