SSRS Report: Using the Map Wizard

Resources:

• http://msdn.microsoft.com/en-us/sql10r2byfbi-trainingcourse_sql10r2byfbi10-hol-01_topic3

Exercise 2: Creating a Report Using the Map Wizard

In this exercise, you will create a report using the Map Wizard. Having developed the report layout, you will include the two components published in Exercise 1.

The finished report will resemble the following.

Figure 1

Previewing the US Sales by State report

Task 1 – Creating a Report Using the Map Wizard

In this task, you will use the Map Wizard to create the report. This will involve the use of the SalesByStateshared dataset published in Exercise 1.

1. Click the Report Builder button, and then select New.
2. In the Getting Started window, click Map Wizard.
   
   Figure 2
   

   Launching the Map Wizard
3. In the New Map window, in the Choose a Source of Spatial Data step, notice that the Map Gallery option is selected, then in the Map Gallery, select USA by State Inset, and then click Next.
   
   Figure 3
   

   Selecting the USA by State Inset map gallery
4. In the Choose Spatial Data and Map View Options, notice the options to zoom in and out, and to add aBing Maps layer, and then click Next.
5. In the Choose Map Visualization step, select the Color Analytical Map option, and then click Next.
   
   Figure 4
   

   Selecting the Color Analytical Map visualization
6. In the Choose the Analytical Dataset step, to source the shared dataset created in the previous exercise, select the Choose an Existing Dataset in this Report or a Shared Dataset.
7. Click Browse.
8. In the Select Dataset window, double-click the Datasets folder, select the SalesByState dataset, and then click Open.
9. In the New Map window, click Next.
10. In the Specify the Match Fields for Spatial and Analytical Data step, check the STUSPS spatial dataset field, then in the corresponding Analytical Dataset Fields dropdown list, select the StateCode field, and then click Next.
    
    Figure 5
    

    Configuring the relationship between the spatial and analytical data
11. In the Choose Color Theme and Data Visualization step, configure the following properties.

    Property	Value
    Field to Visualize	[Sum(Sales)]
    Color Rule	Light-Dark

    
    Figure 6
    

    Configuring the data visualization properties
12. Click Finish.
13. To preview the report, on the Home ribbon tab, click Run.
14. Notice the report parameter name is not particularly user friendly, and it is presently set to pass NULL.
15. Uncheck the NULL checkbox, then in the Calendar Quarter Key parameter box, enter 20082, and then clickView Report.
16. To save the report, click the Report Builder button, and the select Save.
17. In the Save as Report window, in the Name box, replace the text with US Sales by State, and then clickSave.

Task 2 – Introducing a Report Part

In this task, you will introduce the CalendarQuarterKey report parameter published in the previous exercise.

1. To return to design mode, on the Run ribbon tab, click Design.
2. In the Report Data pane, expand the Datasets folder.
3. Notice the SalesByState shared dataset.
4. In the Report Data pane, expand the Parameters folder.
5. Right-click the CalendarQuarterKey report parameter, and then select Delete.
6. When prompted to delete the report parameter, click OK.
7. To introduce the published report parameter, on the Insert ribbon tab, click Report Parts (located on the far left).
8. In the Report Part Gallery (which opens on the right side), click the Search button.
   
   Figure 7
   

   Clicking the Search button
9. Select the CalendarQuarterKey report part, and then notice the properties for the report part displayed at the bottom of the Report Part Gallery.
   
   Figure 8
   

   Reviewing the CalendarQuarterKey report part properties
10. To introduce the report part into the report design, double-click it.
11. In the Report Data pane, verify that the CalendarQuarterKey report parameter has been added.
12. Notice also that the dsCalendarQuarter dataset has been added to support the available values of the report parameter.
13. To remove the textbox added to the report design (in the middle of the map) when adding the report parameter, select the textbox, and then press Delete.
14. To hide the Report Part Gallery, on the Insert ribbon tab, click Report Parts.

Task 3 – Developing the Map Report Layout

In this task, you will format the map report layout that will involve defining the map title and legend title, removing the color and distance scales, and introducing a tooltip expression for each polygon (US state) in the map.

1. Right-click the Map Title text, and then select Title Properties.
2. In the Map Title Properties window, to the right of the Title Text box, click the function button.
   
   Figure 9
   

   Clicking the function button
3. In the Expression window, in the expression box, remove the entire expression text.
4. In the Category list, select Built-in Fields.
5. To add the report name to the expression, in the Item list, double-click the ReportName field.
6. Complete the expression to look like the following.
   Visual Basic

   =Globals!ReportName & " " & 
7. Position the cursor at the very end of the expression, and ensure there is a space after the last ampersand (&).
8. In the Category list, select Parameters.
9. In the Values list, double-click the CalendarQuarterKey parameter.
10. Modify the expression by replacing the Value property with the Label property.
11. Verify that the completed expression looks like the following.
    Visual Basic

    =Globals!ReportName & " " & Parameters!CalendarQuarterKey.Label
12. Click OK.
13. In the Map Title Properties window, click OK.
14. To rename the legend title, right-click the Title text in the legend, and then select Legend Title Properties.
    
    Figure 10
    

    Configuring the Legend Title properties
15. In the Map Legend Title Properties window, in the Legend Title Text box, replace the text with Sales, and then click OK.
16. To remove the color scale, select the scale, and then press the Delete key.
    
    Figure 11
    

    Selecting the color scale
17. To remove the distance scale, select the scale, and then press the Delete key.
18. To configure the polygon properties, click inside the map at least twice to open the Map Layers window, which is located on the right of the report design (you may have to scroll to the right to see it).
19. Right-click the polygon layer, and then select Polygon Properties.
    
    Figure 12
    

    Configuring the Polygon properties
20. In the Map Polygon Properties window, to the right of the Tooltip dropdown list, click the function button.
21. In the Expression window, enter the following expression, and then click OK.
    Note:

    The expression required in the following step may be copied from the Assets\Snippets.txt file in theSource folder of this lab.
    Visual Basic

    =Fields!State.Value & vbCrLf & "Sales: " & CInt(Fields!Sales.Value).ToString("N0")

    Note:

    This expression concatenates the value of the State field with a carriage return and line feed, and then concatenates this with the value of the Sales field converted to an integer and formatted as a numeric with zero decimal places (N0).
22. In the Map Polygon Properties window, click OK.
23. To preview the report, on the Home ribbon tab, click Run.
24. Hover over several shaded states to review the tooltip.
25. In the Quarter parameter dropdown list, select CY2008 Q1, and then click View Report.
26. To save the report, click the Report Builder button, and the select Save.

Task 4 –Finishing Up

In this task, you will finish up by closing all applications.

1. Click the Report Builder button, and then click Exit Report Builder.