Share via

  • Article

Exercise 1: Creating a Report Using the Map Wizard

In this exercise, you will create a report using the Map Wizard.

The finished report will resemble the following.

Figure 1

Previewing the US Sales by State report

Task 1 – Creating the Sales Analysis Shared Data Source

In this task, you will create a report data source to connect to the Sales Analysis tabular BI Semantic Model.

  1. To open Internet Explorer, click the Start button, and then select All Programs | Internet Explorer.
  2. In the URL box, enter the URL for the site collection created during setup, and then press Enter.

  3. In the Quick Launch (located on the left), select the Data Connections library.

    Figure 2

    Selecting the Data Connections Library

  4. On the Library Tools Documents ribbon tab, click the New Document dropdown, and then select Report Data Source.

    Figure 3

    Select the Report Data Source Content Type

  5. Complete the configuration of the data source based on the following properties.

    Note:
    To construct the connection string, substitute <servername> for the tabular instance of Analysis Services that hosts the Sales Analysis database.

    Property

    Value

    Name

    Sales Analysis

    Data Source Type

    Microsoft SQL Server Analysis Services

    Connection String

    Data Source=<ServerInstanceName>;Initial Catalog=Sales Analysis;

  6. To test the data source connectivity, click Test Connection.
  7. Click OK.

  8. To edit the data source properties, hover over the Sales Analysis data source, click the dropdown arrow, and then select Edit Properties.

    Note:
    The report data source is not created as the correct content type. The following step will have you modify the content type.

    Figure 4

    Editing the Document Content Type

  9. In the Data Connections window, in the Content Type dropdown list, select Report Data Source, and then click Save.

Task 2 – Creating a Report Builder Report

In this task, you will use the Map Wizard to create the report based on the Sales Analysis report data source.

  1. In the Quick Launch menu, select Reports.

  2. On the Library Tools Documents ribbon tab, click the New Document dropdown, and then select Report Builder Report.

    Figure 5

    Creating a Report Builder Report

  3. When the Application Run – Security Warning window opens, click Run to authorize the download and installation of Report Builder.

    Note:
    If Report Builder 3.0 has already been installed you will not receive this warning and prompt.

  4. When Report Builder opens, close the Getting Started window.
  5. If necessary, maximize the Report Builder window.
  6. To remove the default report title, in the report design pane, select the edge of the Click to Add Title textbox, and then press Delete.
  7. To remove the report footer, on the Insert ribbon tab, inside the Header & Footer group, click Footer, and then select Remove Footer.

Task 3 – Configuring a Map Data Region

In this task, you will configure a map data region that uses analytic data sourced from the Sales Analysis BI Semantic Model.

  1. To launch the Map Wizard, on the Insert ribbon tab, from inside the Data Visualizations group, click Map, and then select Map Wizard.

  2. 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 6

    Selecting the USA by State Inset map gallery

  3. In the Choose Spatial Data and Map View Options, notice the options to zoom in and out, and to add a Bing Maps layer, and then click Next.

  4. In the Choose Map Visualization step, select the Color Analytical Map option, and then click Next.

    Figure 7

    Selecting the Color Analytical Map Visualization

  5. In the Choose the Analytical Dataset step, notice the selected option to add a dataset, and then click Next.
  6. In the Choose a Connection to a Data Source step, click Browse.

  7. In the Select Data Source window, double-click the Data Connection for PerformancePoint folder, select the Sales Analysis.rsds report data source, and then click Open.

    Note:
    The Data Connection for PerformancePoint library is the Web folder name for the library. Its friendly name (and as it appears in the Quick Launch menu) is Data Connections.

  8. In the New Map window, click Next.

  9. In the Design a Query step, in the Metadata pane, expand the Reseller dimension, expand the Geography hierarchy, and then drag the State level to the query grid.

    Figure 8

    Adding the State Level to the Query Grid

  10. To introduce measures to the query grid, in the Metadata pane, expand Measures, expand ResellerSales, and then drag the Sales and Profit% measures into the query grid, to the right of State.

    Note:
    When you introduce measures into the query grid, the query is automatically executed.

  11. To filter the query result, in the filter pane (located above the query grid), in the Dimension column, select the Date dimension.
  12. In the Hierarchy column, select the Calendar hierarchy, and then in the Filter Expression column, expand the All member, select the CY2007 member, and then click OK.
  13. Notice that the query result now displays the measures by state for calendar year 2007.
  14. To expose the Calendar hierarchy as a report parameter, scroll right to the end of the filter pane.

  15. In the Parameters column, select the checkbox.

    Figure 9

    Exposing the Date Calendar Hierarchy as a Report Parameter

  16. To filter the query result to return only states from the United States, add a second filter based on the following. Do not expose this filter as a report parameter.

    Figure 10

    Exposing the Reseller Geography Hierarchy as a Report Parameter

  17. Notice that the query result now displays the measures by the United States’ states for calendar year 2007.
  18. Click Next.

  19. In the Specify the Match Fields for Spatial and Analytical Data step, check the STATENAME spatial dataset field, then in the corresponding Analytical Dataset Fields dropdown list, select the State field.

    Note:
    This step relates the query result to the polygons (states) that make up the map.

    Figure 11

    Configuring the Relationship Between the Spatial and Analytical Data

  20. Click Next.

  21. In the Choose Color Theme and Data Visualization step, configure the following properties.

    Property

    Value

    Theme

    Generic

    Field to Visualize

    [Sum(Sales)]

    Color Rule

    Light-Dark

  22. Click Finish.
  23. To preview the report, on the Home ribbon tab, click Run.
  24. To save the report, click the Report Builder button (located in the top left corner), and then select Save.
  25. In the Save as Report window, navigate to the Reports library.
  26. In the Name box, replace the text with US Sales by State, and then click Save.

Task 4 – Refining the Map 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. To return to design mode, on the Run ribbon tab, click Design.
  2. In the Report Data pane (located on the left), expand the Parameters folder.
  3. Right-click the DateCalendar parameter, and then select Parameter Properties.
  4. In the Report Parameter Properties window, uncheck the Allow Multiple Values checkbox.
  5. Click OK.
  6. Right-click the Map Title text, and then select Title Properties.

  7. In the Map Title Properties window, to the right of the Title Text box, click the function button.

    Figure 12

    Clicking the Function Button

  8. In the Expression window, in the expression box, remove the entire expression text.
  9. In the Category list, select Built-in Fields.
  10. To add the report name to the expression, in the Item list, double-click the ReportName item.

  11. Complete the expression to look like the following.

    Visual Basic

    =Globals!ReportName & " " &

  12. Position the cursor at the very end of the expression, and ensure there is a space after the last ampersand (&).
  13. In the Category list, select Parameters.
  14. In the Values list, double-click the DateCalendar parameter.
  15. Modify the expression by replacing the Value property with the Label property.

  16. Verify that the completed expression looks like the following.

    Visual Basic

    =Globals!ReportName & " " & Parameters!DateCalendar.Label

  17. Click OK.
  18. In the Map Title Properties window, click OK.

  19. To rename the legend title, right-click the Title text in the legend, and then select Legend Title Properties.

    Figure 13

    Configuring the Legend Title Properties

  20. In the Map Legend Title Properties window, in the Legend Title Text box, replace the text with Sales, and then click OK.

  21. To remove the color scale, select the scale, and then press the Delete key.

    Figure 14

    Selecting the Color Scale

  22. To remove the distance scale, select the scale, and then press the Delete key.
  23. 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).

  24. Right-click the polygon layer, and then select Polygon Properties.

    Figure 15

    Configuring the Polygon Properties

  25. In the Map Polygon Properties window, to the right of the Tooltip dropdown list, click the function button.

  26. In the Expression window, enter the following expression.

    Note:
    The expression required in the following step may be copied from the \Assets\Snippets.txt file located in the Source folder for this lab.

    Visual Basic

    =Fields!State.Value & vbCrLf & "Sales: " & FormatNumber(Fields!Sales.Value, 0) & vbCrLf & "Profit: " & FormatPercent(Fields!Profit_.Value, 2)

    Note:
    This expression concatenates the value of the State field with a carriage return and line feed, and then concatenates this with the numeric formatted value of the Sales field and the percent formatted value of the Profit_ field.

    The Profit_ field is based on the Profit% measure. The percent character (%) is not permitted in identifier names used by Reporting Services.

  27. Click OK.
  28. In the Map Polygon Properties window, click OK.
  29. To preview the report, on the Home ribbon tab, click Run.

  30. Hover over several shaded states to review the tooltip. In particular notice that Texas achieved sales of 2,680,474.

    Figure 16

    Reviewing the Texas Sales for CY2007

  31. In the Calendar parameter dropdown list, select CY2006, and then click View Report.
  32. Notice the different sales value for Texas.
  33. To save the report, click the Report Builder button, and the select Save.

Task 5 –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.
  2. Close the Internet Explorer window.

< previous | next >

Downloads

Download The Offline Training Kit
Download Hands-on Lab Document (DOCX)
Download Hands-on Lab Source Files

Contents