Creating the Sales_by_Region_2008R2 Report (SSRS)

This tutorial helps you build the Sales_by_Region_2008R2 report from the AdventureWorks 2008R2 sample reports.

This report displays information about Adventure Works customers in the United States. Customers include individuals and stores. Individuals order online directly from Adventure Works. Stores are resellers for Adventure Works products.

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 perform the following tasks:

  • Define a dataset and query that retrieves data for all US individual sales, all US store sales, and aggregated US store sales by state.

  • Add a US map from the map gallery. The map wizard adds a polygon layer to display the shapes of the US states.

  • Use the map wizard to configure the map polygon layer to vary state color by sales totals for individuals.

  • Add a map point layer to display store locations.

  • Use the map wizard to configure the map point layer to vary marker color by sales totals for each store.

  • Define a report parameter to specify the distance of interest between a store and customers who live nearby.

  • Add a drillthrough action on the store location marker to open the Customers_Near_Stores_2008R2 report.

  • Add a Bing Maps tile layer and three report parameters to enable a user to interactively vary the display.

  • Add a table to display aggregated sales totals for individuals by US state. Use the Lookup Function (Report Builder 3.0 and SSRS) to include aggregated sales totals for stores by US state.

  • Add a table to display all stores in a state. Define the name of the state as a bookmark.

  • Define a hyperlink action on each US state in the map that jumps to the state in the table that lists all the stores in that state.

  • Change the order of parameters on the report viewer toolbar.

  • Publish and verify the report.

Estimated time to complete this tutorial: 20 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 display Bing Map tiles, the report server must be configured to support this feature. For more information, see Planning for Map Support.

Note

For more information about the use of Bing map tiles in your report, see Additional Terms of Use and Privacy Statement.

Copy the Base Report

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 Sales_by_Region_2008R2.rdl.

Get Individual and Store Sales Data

To create the dataset for the individual sales

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

    SELECT soh.SalesOrderID, soh.TotalDue, soh.OrderDate, 
      c.CustomerID, p.FirstName, p.LastName, 
        RTRIM(sp.StateProvinceCode) as StateProvinceCode, 
        ad.City, ad.PostalCode, ad.SpatialLocation
    FROM Sales.SalesOrderHeader AS soh INNER JOIN
       Sales.Customer AS c ON soh.CustomerID = c.CustomerID INNER JOIN
       Person.BusinessEntity AS b ON b.BusinessEntityID = c.PersonID INNER JOIN
       Person.Person AS p ON p.BusinessEntityID = b.BusinessEntityID INNER JOIN
       Person.BusinessEntityAddress AS a ON a.BusinessEntityID = b.BusinessEntityID INNER JOIN
       Person.Address AS ad ON ad.AddressID = a.AddressID INNER JOIN
       Person.AddressType AS at ON at.AddressTypeID = a.AddressTypeID INNER JOIN
       Person.StateProvince AS sp ON sp.StateProvinceID = ad.StateProvinceID
    WHERE(c.PersonID IS NOT NULL) 
       AND (at.Name = N'Home') 
       AND (sp.CountryRegionCode = N'US')
    

The dataset query selects data, including the home address, for all US customers who are individuals, not stores.

When you add a map, this data provides the analytical data that determines the color of each state based on its aggregated value for TotalDue. StateProvinceCode is the match field that relates the spatial data to the analytical data in this dataset.

To create the dataset for reseller store sales

  • In Solution Explorer, add a shared dataset named StoreSales. Use the AdventureWorks2008R2 shared data source and the following query:

    SELECT soh.SalesOrderID, soh.TotalDue, soh.OrderDate, c.CustomerID, 
       st.Name AS Store, 
       sp.StateProvinceCode, 
    -- StateProvinceCode is 3 chars. 
    --    Use RTRIM here or in the expression for the match field. 
       ad.City, ad.PostalCode, ad.SpatialLocation
    FROM Sales.SalesOrderHeader AS soh INNER JOIN
      Sales.Customer AS c ON soh.CustomerID = c.CustomerID INNER JOIN
      Person.BusinessEntity AS b ON b.BusinessEntityID = c.StoreID INNER JOIN
      Sales.Store AS st ON st.BusinessEntityID 
         = b.BusinessEntityID INNER JOIN
      Person.BusinessEntityAddress AS a 
         ON a.BusinessEntityID = b.BusinessEntityID INNER JOIN
      Person.Address AS ad ON ad.AddressID = a.AddressID INNER JOIN
      Person.AddressType AS at ON at.AddressTypeID = a.AddressTypeID
         INNER JOIN Person.StateProvince AS sp 
         ON sp.StateProvinceID = ad.StateProvinceID
    WHERE (c.StoreID IS NOT NULL) 
       AND (at.Name = N'Main Office') 
       AND (sp.CountryRegionCode = N'US')
    

The dataset query selects data, including the main office address, for all US customers that are reseller stores.

When you add a map layer to display store locations, this data provides the spatial data and the analytical data that determines the color of each store marker based on its aggregated value for TotalDue.

Get Aggregated Store Sales for Each State

Sometimes you want to show data from multiple datasets in a single table. Although you cannot do a JOIN between data from multiple datasets in the report, you can write expressions that retrieve values from multiple datasets and display them in the same table.

In the report, you will display aggregated sales for individuals and stores in a table next to the map to help verify that the map colors are what you expect. Aggregated sales for individuals will come from the dataset IndividualCustomerSales. Aggregated sales for stores will come from the StoreSaleTotals dataset that you create in the next procedure.

To create a dataset for aggregated store sales

  • In Solution Explorer, add a shared dataset named StoreSaleTotals. Use the AdventureWorks2008R2 shared data source and the following query:

    SELECT sp.StateProvinceCode, COUNT(DISTINCT (c.StoreID)) as NStores, SUM(soh.TotalDue) AS Total
    FROM Sales.SalesOrderHeader AS soh INNER JOIN
       Sales.Customer AS c ON soh.CustomerID = c.CustomerID INNER JOIN
       Person.BusinessEntity AS b ON b.BusinessEntityID = c.StoreID INNER JOIN
       Sales.Store AS st ON st.BusinessEntityID = b.BusinessEntityID INNER JOIN
       Person.BusinessEntityAddress AS a ON a.BusinessEntityID = b.BusinessEntityID INNER JOIN
       Person.Address AS ad ON ad.AddressID = a.AddressID INNER JOIN
       Person.AddressType AS at ON at.AddressTypeID = a.AddressTypeID INNER JOIN
       Person.StateProvince AS sp ON sp.StateProvinceID = ad.StateProvinceID
    WHERE (c.StoreID IS NOT NULL) AND (at.Name = N'Main Office') AND (sp.CountryRegionCode = N'US')
    GROUP BY sp.StateProvinceCode
    ORDER BY sp.StateProvinceCode
    

The dataset query returns totals for store sale totals for each state.

In the report, you will display the sales totals for each individual. By design, a matrix data region is bound to the data from one dataset. By using the Lookup function with a scope parameter that specifies this dataset, the matrix can include data for each store.

Add the Scenario Text

To add the scenario text

  • In the scenario description text box, replace the existing text with the following text and format as needed:

    AdventureWorks customer sales come from reseller stores and individual internet sales. State color is based on the sales amounts for all individual sales. Marker color is based on sales amounts for reseller stores. Markers indicate the location of each store. Click the marker to find individuals who live within a [@Radius] mile radius of the store.

Create a Report Parameter for the Drillthrough Action

To add a parameter to specify the initial distance of customer to store

  1. In the Report Data pane, add a parameter named Distance. Do the following:

  2. Set Prompt to Distance?.

  3. Set Data type to Integer.

  4. Set Select parameter visibility to Hidden.

  5. On Default Values, select Specify values, add a value and set it to to 50.

This is the number of miles that will be passed through to the drillthrough report for the action on the store marker that you will create in a later step.

Add the Description

To add the description

  1. In the page footer, replace the text in description text box with the following text and format as needed:

    Purpose: Map report. For each state, displays sales totals for individuals, sales totals for reseller stores, and store locations. Includes the US map from the map gallery, analytical data from a dataset, polygon layer (one for each state), polygon fill for no sales, polygon color rules based on individual sales, markers for stores, marker colors based on store sales, marker drillthrough action, multiple map titles and legends, and use of Lookup function.

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

  3. In Description, paste the description text.

Display Aggregate Totals for Individual and Store Customers

To display customer totals by state in a matrix

  1. Add a matrix to display data from the IndividualCustomerSales dataset.

  2. Drag [StateProvinceCode] to Rows.

  3. Click the field tag in the Data cell, and then click CustomerID. [Sum(CustomerID)] is added.

  4. Click [Sum(CustomerID)] to select the text that was just added, then right-click, point to Summarize By, and click CountDistinct. This expression calculates the number of unique customers in each state.

  5. Right-click the last column handle, point to Insert Column, and click Inside Group - Right.

  6. Click the field tag, and then click TotalDue. [Sum(TotalDue)] is added. This expression calculates the total sales for all sales for individual customers for each state. Format this text box as currency.

  7. Right-click the column handle, point to Insert Column, and click Inside Group - Right.

  8. In the text box that you just added, type the following expression:

    =Lookup(TRIM(Fields!StateProvinceCode.Value),TRIM(Fields!StateProvinceCode.Value),Fields!NStores.Value,"StoreSaleTotals")

    This looks up the number of stores NStores from the dataset StoreSaleTotals.

  9. On the Font page, set Color to Blue and Effects to Underline.

  10. On the Action page, select Go to bookmark.

  11. In Select bookmark, type =TRIM(Fields!StateProvinceCode.Value).

  12. Right-click the column handle, point to Insert Column, and click Inside Group - Right.

  13. In the text box, type the following expression:

    =Lookup(TRIM(Fields!StateProvinceCode.Value),TRIM(Fields!StateProvinceCode.Value),Fields!Total.Value,"StoreSaleTotals")

    This looks up the aggregated state sales Total from the dataset StoreSaleTotals. Format this text box as currency.

  14. Replace the column header text with the following text: Sales Totals for all Customers.

  15. To add a group header row, right-click the handle for the row group, point to Insert Row, and then click Outside Group - Above.

  16. Add the following text to label the last four columns: Number of Individuals, Total Sales, Number of Stores, Total Sales.

  17. Format the matrix as needed.

By comparing the data in the matrix to the map display, you can verify that the map you add in a later step has been configured correctly. Notice that not every state has stores.

Reduce the Space Needed by the Matrix

You can rotate text in a column header to help control the width of the rendered matrix.

To set text box rotation

  1. Click the text box in the row header that contains the text Number of Individuals.

  2. Insert a new line after the word of.

  3. In the Properties pane, find WritingMode, and set it to Vertical.

  4. Repeat steps 2 and 3 for the text box that contains Number of Stores.

  5. Adjust the column widths of the matrix to minimize the space that it takes.

Add a Map and the US States Map Polygon Layer

To add a map to display total sales from individual customers, by state

  1. Insert a map by using the map wizard.

  2. From the Map gallery, choose USA by State Inset.

  3. On the Choose spatial data and map view options, select Add a Bing Maps layer.

  4. For map visualization, select Color Analytical Map.

  5. For analytical dataset, select IndividualCustomerSales.

    This map layer varies US state color based on total sales for individuals who live in the state.

  6. For Match fields, select STUSPS. This is the state abbreviation.

  7. For Analytical Dataset Field, select StateProvinceCode.

  8. For Theme, select Slate.

  9. For Field to visualize, select [Sum(TotalDue)].

  10. For Color rule, select Light-Dark.

  11. Select Display labels, and set Data field to #STUSPS.

  12. Complete the wizard.

  13. Position the map on the left side of the report and use snaplines to align the top of the matrix next to it.

To configure the legends

  1. Right-click the distance scale, and clear the Show Distance Scale option.

  2. Right-click the color scale, and clear the Show Color Scale option.

  3. Change the legend title to Individual Sales.

  4. In Legend properties, change Legend layout to Column.

  5. Clear the option Show legend outside the viewport.

  6. Change the position to the lower left corner of the map.

To configure the default color and tooltip for US states

  1. In the Map Layers pane, right-click the polygon layer, click Polygon Properties.

  2. In Tooltip, set the following expression: =FormatCurrency(Sum(Fields!TotalDue.Value),0).

  3. On the Fill page, set Color to Light Steel Blue.

This sets the default color for all polygons on the layer. If there is analytical data for a state, the polygon color rules override this color. This means that all states that have this color have no associated data. In the next step, you can add a second map title to provide this information to report users.

To configure the map titles

  1. Right-click the map, point to Map, and then click Add Title.

  2. Change the new title to AdventureWorks Customer Sales Totals. Format as needed.

  3. Right-click the second map title.

  4. In Title Properties, do the following:

    1. On the General page, in Title text, type the following text: (This color represents areas with no sales.)

    2. Clear the option Show title outside the viewport.

    3. On the Font page, set Font to Tahoma, Size to 9pt, Color to Dark Blue, and clear the Bold option.

    4. On the Fill page, set Color to Light Steel Blue.

      To avoid hiding the geolocation data copyright attribution at the bottom of the map, leave this note under the map title.

To configure the polygon color rules and add a legend

  1. In the Map Layers pane, right-click the polygon layer, and then click Polygon Color Rule.

  2. On the General page, do the following:

    1. Verify that Visualize data by using color ranges is selected.

    2. Verify that Data field is set to [Sum(TotalDue)].

    3. Change Start color to Light Blue.

    4. Change Middle Color to Cornflower Blue.

    5. Change End color to Blue.

  3. On the Legend page, clear the option Show in color scale.

  4. Verify that Show in this legend is Legend1.

  5. In Legend text, use the following text to change the format from number to currency: #FROMVALUE{C0} - #TOVALUE{C0}

The legend displays 5 ranges of sales totals and the corresponding color.

To configure the map viewport

  1. Click the corner of the map to select the viewport, and open Viewport Properties.

  2. On the Fill page, set Color to WhiteSmoke.

  3. In the Map view section of the Map Layers pane, use the slider and direction arrows to adjust the view of the map as needed.

The viewport background is visible when the tile layer is hidden or transparent.

Add a Store Location Map Point Layer

To add a point layer on which to display store locations

  1. On the Map Layers pane toolbar, click New layer wizard. The map wizard opens.

    As you step through the wizard pages, use the following information to configure the map.

  2. Select SQL Server spatial query.

  3. For Choose an existing dataset with SQL Server spatial data in this report, choose StoreSales.

  4. Verify that the layer type is Point.

  5. Select Analytical Marker Map.

  6. For Choose an existing dataset in this report or a shared dataset, choose StoreSales.

  7. Verify that Use marker colors to visualize data is selected.

  8. In Data field, click [Sum(TotalDue)].

  9. In Marker, click Star.

  10. Accept the other values, and complete the wizard.

The legend for the markers appears in the first legend that was added to the report. You will change the color rule and display the rules in the second legend in the next step.

To add a legend for the point layer

  1. Right-click the map, point to Map, and then click Add Legend.

  2. In Legend Title Properties, change Legend title text to Store Sales.

  3. Change the position to the lower right corner of the map.

To configure color rules for the point layer

  1. In the Map Layers pane, right-click the point layer, and then click Point Color Rule.

  2. On the General page, do the following:

    1. Verify that Visualize data by using color ranges is selected.

    2. Verify that Data field is [Sum(TotalDue)].

    3. Set Start color to Pale Turquoise.

    4. Set Middle color to Turquoise.

    5. Set End color to Aqua.

  3. On the Legend page, do the following:

    1. Clear Show in color scale.

    2. Select Legend2.

    3. Set Legend text to #FROMVALUE{C0} - #TOVALUE{C0}

The legend information is removed from the first legend and added to the second legend.

To add a tooltip and drillthrough action on the store marker

  1. In the Map Layer pane, right-click the point layer, and then click Point Properties.

  2. On the General page, in Tooltip, select [Store].

  3. Change Marker size to 15pt.

  4. On the Action page, select the Go to report option.

  5. In Specify a report, type Customers_Near_Stores_2008R2.

  6. Click Add to specify a parameter value.

  7. In Name, from the drop-down list, select GeoLocation.

  8. In Value, type the following expression: =First(Fields!SpatialLocation.Value).ToString().

  9. Click Add to specify a parameter value.

  10. In Name, type [@Distance].

  11. In Value, type 50.

For more information about building the target report, see Creating the Customers_Near_Stores_2008R2 Report (SSRS).

Add Parameters that Enable the Report User to Vary the Tile Layer

To add a parameter to vary the display the tile layer

  1. Add a report parameter named ShowBingMaps.

  2. Set Prompt to Show Bing Map background?

  3. On Available Values, specify the following individual values: Visible, Hidden.

  4. On Default Values, specify Hidden.

To add a parameter to vary the tile type

  1. Add a report parameter named BingMapTileType.

  2. Set Prompt to Bing Map tile type?

  3. On Available Values, specify the following individual values: Road, Aerial, Hybrid.

  4. On Default Values, specify Road.

To add a parameter to customize the polygon layer transparency

  1. Add a report parameter named USStatesTransparency.

  2. Set Prompt to Transparency for US States?

  3. Set Data type to Float.

  4. On Available Values, specify the following individual values: 0, 10, 25, 35, 50, 75.

  5. On Default Values, specify 0.

To configure tile layer properties based on parameters

  1. In the Map Layers pane, right-click the tile layer, and then click Tile Properties.

  2. On the General page, in Type, type the following expression: [@BingMapTileType].

  3. On the Visibility page, in Layer visibility, select Show or hide based on an expression.

  4. Type the following expression: [@ShowBingMaps].

To set the transparency of the US states layer based on a parameter

  1. In the Map Layers pane, right-click the polygon layer, and then click Layer Data. The Map Polygon Layer Properties dialog box opens.

  2. On the Visibility page, in Transparency (percent), type the following expression: [@USStatesTransparency].

  3. Change the order of the report parameters as needed.

Add a Target Bookmark for Top

To add a bookmark for the top of page

  • Select the map, and in the Properties pane, set Bookmark to Top.

Add a Matrix to Display All Stores in a State

On the US states layer, some states have overlapping store locations. Add a table to help the user choose which store they want. When a user clicks the state, the report jumps to the location that displays all stores in that state.

To add a rectangle for the matrix and label

  1. Insert a Rectangle named rectStoresbyState below the map.

  2. Expand the rectangle to the same width as the map.

  3. Add a text box named tbStoresbyStateLabel with the following text: Store sales in each state.

To add a matrix and bookmarks

  1. In the rectangle under the label, insert a Matrix named tblxMatrix_StoresbyState.

  2. From the StoreSales dataset, drag TotalDue to Data. Format the text box as currency.

  3. Add a row group based on StateProvinceCode.

  4. Add a child row group based on Store.

  5. Select the text box that contains StateProvinceCode, and in the Properties pane, set BookMark to the following expression: =TRIM(Fields!StateProvinceCode.Value).

  6. Place the cursor next to StateProvinceCode, add a space, right-click, and then click Create Placeholder.

  7. In Label, type the following text: BacktoTop.

  8. In Value, enter the following expression: ="Back to top".

  9. On the Font page, set Color to Blue and Effects to Underline.

  10. On the Action page, select Go to bookmark.

  11. In Select bookmark, type Top.

  12. Format the matrix as needed.

  1. In the matrix, right-click Store, and then select the text box. In Text Box properties, do the following:

  2. On the Font page, set Color to Blue and Effects to Underline.

  3. On the Action page, select Go to report.

  4. In Specify a report, from the drop-down list, select Customers_Near_Stores_2008R2.

  5. Add the following three target report parameters:

    1. GeoLocation set to the following expression: =Fields!SpatialLocation.Value.ToString().

    2. Radius set to [@Distance].

    3. ShowAll set to [@ShowAll].

  1. In the Map Data pane, right-click the polygon layer, and then click Polygon Properties.

  2. On the Action page, click Go to bookmark, and enter the following expression: =TRIM(Fields!StateProvinceCode.Value).

When you run the report, the action will exist for all states that have stores.

Preview and Verify the Report

To preview and verify the report

  • Run the report and verify the following:

    1. The states vary by color based on the total sales for individuals.

    2. The store markers vary by color based on the total sales for the store.

    3. The legends provide the information to correctly interpret the state and store marker colors.

    4. Each state displays the two character state abbreviation.

    5. The tooltip on each state displays total sales for all individual customers.

    6. The tooltip on each star marker displays the name of the store.

    7. Changing the parameter value for @ShowBingMaps shows and hides the tile layer.

    8. Changing the parameter value for @BingMapTileType displays different types of tiles.

    9. Changing the parameter value for @PolygonLayerTransparency changes the transparency of the layer that displays the US state shapes.

    10. In the matrix next to the map, there is one row for each state. The row contains a value the state abbreviation, the number of individual customers who have a home address there, the corresponding total sales for individual customers, the number of stores with a main office in the state, and the corresponding total sales for those stores.

    11. Click a state that contains stores. The report jumps to the matrix below the map to the entry for that state. All stores in the state are listed.

    12. Click Back to top. The report jumps to the map at the top of the report.

    13. (Optional) Verify the following links after you create the the Customers_Near_Stores_2008R2 report: when you click a store marker, the target report opens. When you a click a store in the store sales matrix, the target report opens. The report displays all customers within a 50 mile radius of the selected store.

For more information about the target report, see Creating the Customers_Near_Stores_2008R2 Report (SSRS).

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

Change History

Updated content

  • Changed report to be based on AdventureWorks2008R2_Base.rdl