Hands-On Lab

Building and Sharing Your First
Report Builder 3.0 Reports

Lab version:1.0.0

Last updated:11/15/2018

Contents

Overview

Exercise 1: Creating a Report Using the Table Wizard

Task 1 – Launching Report Builder

Task 2 – Creating a Report Using the Table Wizard

Task 3 – Developing the Table Report Layout

Task 4 – Configuring the Report Parameter

Task 5 – Publishing Report Parts

Exercise 2: Creating a Report Using the Map Wizard

Task 1 – Creating a Report Using the Map Wizard

Task 2 – Introducing a Report Part

Task 3 – Developing the Map Report Layout

Task 4 –Finishing Up

Summary

Overview

This lab will author two reports using Report Builder 3.0. In the first exercise, you will create a report by using the Table Wizard. You will then create and configure a report parameter for this report. Next, you will publish the report parameter and main dataset as report parts.

In the second exercise, you will create a report by using the Map Wizard. The map analytic data will reference the shared dataset published in the first exercise. You will then introduce the report parameter published in the first exercise.

Note: Before you start with this exercise you must ensure that your machine meets the system requirements detailed in the next section. Additionally, you must complete the setup steps described in the next section.

Objectives

The objectives of this exercise are to:

  • Use Report Builder 3.0 to:

◦Create a report using the Table Wizard

◦Publish report parts

◦Create a report using the Map Wizard

◦Introduce report parts into a report

System Requirements

You must have installed the following items to complete this lab:

  • Microsoft SQL Server 2008 R2:

◦Database Engine

◦Reporting Services (Native mode)

  • SQL Server AdventureWorks2008 R2 sample databases

◦AdventureWorksDW2008R2

  • Microsoft SQL Server 2008 R2 Report Builder 3.0

Setup

All the requisites for this lab are verified using the Configuration Wizard. To make sure that everything is correctly configured, follow these steps.

Note: To perform the setup steps you need to run the scripts in a command window with administrator privileges.

  1. Launch the Configuration Wizard for this lab by double-clicking the Dependencies.dep file located under the Source\Setup folder of this lab. Install any pre-requisites that are missing (rescanning if necessary) and complete the wizard.

Cleanup

There is no need to cleanup if you intend to continue the sequence of labs in this training kit.

  1. To restore the original state of the AdventureWorksDW2008R2SQL Server database and remove the Reporting Servicescontent, execute the Cleanup.cmdscript located under the Setup folder in the Source folder of this lab.

Exercises

This Hands-On Lab comprises the following exercise:

  1. Creating a Report Using the Table Wizard
  2. Creating a Report Using the Map Wizard

Estimated time to complete this lab: 30minutes.

Exercise 1: Creating a Report Using the Table Wizard

In this exercise, you will commence by launching Report Builder, and then create a report using the Table Wizard. Having developed the report layout, including the configuration of a report parameter, you will publish the report parameter and main dataset for use by the report authored in Exercise 2.

The finished report will resemble the following.

Figure 1

Previewing the Sales by State report

Task 1 – Launching Report Builder

In this task, you will open Report Manager to launch Report Builder.

  1. Open Internet Explorer from Start | All Programs | Internet Explorer.
  2. In the Internet Explorer window, in the URL box, enter and then press Enter.

Note: You will need to substitute <servername> for the name of the machine that hosts Reporting Services.

  1. To launch Report Builder, on the Report Managertoolbar, click Report Builder.

Figure 2

Launching Report Builder

  1. If Report Builderhas not already been installed, you will be prompted to download and install the application. When prompted to run the application, click Run.

Figure 3

Installing Report Builder

Task 2 – Creating a Report Using the Table Wizard

In this task, you will use the Table Wizard to create the report. This will involve the creation of a dataset, and arranging the dataset fields to produce the table design. The dataset will be configured to support a parameter that willenable users to request the report for a specific calendar quarter.

  1. When Report Builder launches, to create a new table report, in the Getting Started window, click Table or Matrix Wizard.

Figure 4

Launching the Table or Matrix Wizard

  1. In the New Table or Matrix window, in the Choose a Dataset step, ensure theCreate a Datasetoption is selected(located at the bottom of the window), and then click Next.
  2. To browse to a published shared dataset, in the Choose a Connection to a Data Source step, click Browse.
  3. In the Select Data Source window, double-click the Data Sources folder, select the AdventureWorksDW2008R2 data source, and then click Open.
  4. In the New Table or Matrix window, click Next.
  5. In the Design a Querystep, in the Database View pane, expand the Views folder, and then check the vReportSalesByState view.
  6. In the Selected Fields pane, notice that all the view columns become fields.
  7. To preview the data, click Run Query.

Figure 5

Previewing the data

  1. Notice that the CalendarQuarterKey field values are expressed as integers combining the calendar year and quarter. For example, 20053represents the third quarter of calendar year 2005.
  2. In the Selected Fields list, ensure that the CalendarQuarterKeyis selected, and then in the Applied Filters pane, click Add Filter.

Figure 6

Adding the CalendarQuarterKey field as a filter

  1. In the Applied Filters list, click inside the CalendarQuarterKey filter value, and enter 20082,and then press Enter.
  2. To create a report parameter for the filter, check the Parameter checkbox.

Figure 7

Configuring the CalendarQuarterKey filter

  1. Click Next.
  2. In the Arrange Fields step, in the Available Fields list, select the Country field, and then while pressing the Control key, select the State field also.
  3. Drag the selected fields into the Row Groups box.
  4. From the Available Fields list, drag the Sales field into the Values box.
  5. Verify that the field arrangement looks like the following.

Figure 8

Verifying the field arrangement

  1. Click Next.
  2. In the Choose the Layout step, in the Options, select the Stepped, Subtotal Above option, and then click Next.
  3. In the Choose a Style step, review the available styles, and then click Finish.
  4. In Report Builder, if necessary, maximize the window.
  5. To save the report, click the top left corner button (for the purpose of this lab, it will be referred to as the Report Builder button), and then select Save.

Figure 9

Saving the report

  1. In the Save as Report window, double-click the Sales Reports folder.
  2. In the Name box, replace the text with Sales by State, and then click Save.

Task 3 – Developing the Table Report Layout

In this task, you will format the report layout that will involve adding a report header, configuring the title textbox, and formatting the table, its columns and number value formats.

  1. To add a header to the report, on the Insert ribbon tab, inside the Header & Footer group, click Header, and then select Add Header.
  2. Select the title textbox (the text in the title textbox reads Click to Add Title), and then drag it into the top left corner of the report header.
  3. In the Report Data pane (located on the left), expand the Built-in Fields folder, and then drag the Report Name field into the title textbox.

Note: The last step has assigned an expression that will dynamically assign the name of the report to the textbox.

  1. While the report title textbox is selected, on the Home ribbon tab, inside the Border group, select the bottom border.

Figure 10

Formatting the textbox bottom border

  1. To select the table, click anywhere inside itto reveal the column and row guides, and then click the top left corner.

Figure 11

Selecting the table

  1. Click and drag the four-headed arrow to reposition the table to the top left corner of the body of the report.
  2. To widen the first column, click anywhere inside the tableto reveal the column and row guides, and then drag the right edge of the first column guide to widen the column to approximately twice its original size.
  3. To select all of the second column’s textboxes, select the second column guide.

Figure 12

Selecting all of the second column’s textboxes

  1. To right-align the textboxes, on the Home ribbon tab, inside the Paragraph group, click the Right align button.

Figure 13

Clicking the Right Align button

  1. To format the Sales values, select the first [Sum(Sales)] textbox.
  2. On the Home ribbon tab, inside the Number group, in the dropdown list, select Number.
  3. Repeat the last two steps to format the other two sales textboxes.
  4. To preview the report, on the Home ribbon tab, click Run (located on the far left).
  5. Notice the report parameter and its default value requesting data for the second calendar quarter of 2008.

Note: You will configure this report parameter to prompt a user-friendly name, and present a list of available values in the next task.

  1. Expand Australia to reveal the states of Australia.
  2. To save the report, click the Report Builder button, and the select Save.

Task 4 – Configuring the Report Parameter

In this task, you will develop the Quarterreport parameter to prompt the user with available values and default to the first of those values. You will also extend that layout of the report to display the parameter selection in the header of the report.

  1. To return to design mode, on the Run ribbon tab, click Design (located on the far left).
  2. To add a dataset to provide available values for the report parameter, in the Report Data pane, expand the Data Sources folder, right-click the AdventureWorksDW2008R2 data source, and then select Add Dataset.
  3. In the Dataset Properties window, in the Name box, replace the text with dsCalendarQuarter.
  4. In the Query Type options, select Stored Procedure.
  5. In the Select or Enter Stored Procedure Name dropdown list, select the uspReportParam_CalendarQuarterKey stored procedure, and then click OK.
  6. In the Report Data pane, notice the addition of the dataset and that it contains two fields: CalendarQuarterKey and CalendarQuarterLabel.
  7. To configure the report parameter, in the Report Data pane, expand the Parameters folder, right-click the CalendarQuarterKey report parameter, and then select Parameter Properties.
  8. In the Report Parameter Properties window, in the Prompt box, replace the text with Quarter.
  9. Select the Available Valuespage.
  10. Select the Get Values From a Query option, and then configure the following properties.

Property / Value
Dataset / dsCalendarQuarter
Value Field / CalendarQuarterKey
Label Field / CalendarQuarterLabel

Figure 14

Configuring the report parameter available values

  1. Select the Default Valuespage.
  2. Select the Get Values From a Query option, and then configure the following properties.

Property / Value
Dataset / dsCalendarQuarter
Value Field / CalendarQuarterKey

Figure 15

Configuring the report parameter default value

Note: This configuration will ensure that the first row retrieved from the dataset becomes the report parameter’s default value.

  1. Click OK.
  2. To add the parameter selection to the report header, in the Report Data pane, drag the CalendarQuarterKeyreport parameter and drop it into the report header, directly beneath the report title.
  3. Click and drag the four-headed arrow of the textbox to reposition it directly beneath the report title and aligned to the very left of the report title textbox.
  4. To configure the textbox expression, right-click inside the report parameter textbox, and then select Expression.

Figure 16

Configuring the textbox expression

  1. In the Expression window, modify the expression by replacing the Value property with the Label property, and then click OK.

Visual Basic

=Parameters!CalendarQuarterKey.Label

  1. To preview the report, on the Home ribbon tab, click Run.
  2. In the Quarter parameter dropdown list, select CY2008 Q1, and then click View Report (located at the far right).
  3. Notice that the report header displays the parameter selection.
  4. To save the report, click the Report Builder button, and the select Save.

Task5 – Publishing Report Parts

In this task, you will publish the CalendarQuarterKey report parameter and the dataset used bythe table. In the next exercise, you will create a new report that will use both of these shared components.

  1. To return to design mode, on the Run ribbon tab, click Design.
  2. To publish the report parts, click the Report Builder button, and the select Publish Report Parts.
  3. In the Publish Report Parts window, select the Review and Modify Report Parts Before Publishing.

Figure 17

Publishing report parts

  1. To select and configure the report parts to publish, in the Report Parts group, notice that the CalendarQuarterKey report parameter is already checked.
  2. Uncheck the Tablix1 report part.
  3. In the Datasets group, check theDataSet1 dataset.
  4. Click the word DataSet1, and then modify the text to SalesByState.
  5. To expand the dataset details, click the arrow to the left of the SalesByStatedataset.

Figure 18

Configuring the SalesByStatedataset

  1. To configure a different location to store the dataset, click the Browse button.

Figure 19

Clicking the Browse button

  1. In the Select Folder window, click the Up One Level button.

Figure 20

Clicking the Up One Level button

  1. Select the Datasets folder, and then click OK.
  2. Verify that your configuration looks like the following.

Figure 21

Reviewing the Publish Report Parts configuration

  1. To publish the selected parts to the report server, click Publish.
  2. Click Close.
  3. In the Report Data pane, notice that DataSet1 icon now includes an arrow which signifies that it references a shared dataset.

Figure 22

Reviewing the shared dataset icon

  1. To save the report, click the Report Builder button, and the select Save.

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 23

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 SalesByState shared 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 24

Launching the Map Wizard

  1. In the New Map window, in the Choose a Source of Spatial Data step, notice that theMap Galleryoption is selected, then in the Map Gallery, select USA by State Inset, and then click Next.

Figure 25

Selecting the USA by State Inset map gallery

  1. 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.
  2. In the Choose Map Visualization step, select the Color Analytical Map option, and then click Next.

Figure 26

Selecting the Color Analytical Map visualization

  1. 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.
  2. Click Browse.
  3. In the Select Dataset window, double-click the Datasets folder, select the SalesByState dataset, and then click Open.
  4. In the New Map window, click Next.
  5. 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 27

Configuring the relationship between the spatial and analytical data

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

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