Creating the Sales_by_Region_2008R2 Report (SSRS)

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.

• 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.

• 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.

• 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.

• 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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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).

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.

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.

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.

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].

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.

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

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.

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.

• 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).