• Article

Exercise 1: Authoring the Sales Summary Report

In this exercise, you will create a Report Server project. You will then create a shared data source for the AdventureWorksDW2008R2 SQL Server database, and then use this data source to author a report. The report will prompt the user for a calendar year and will present a matrix of sales grouped by salespeople and calendar quarter. Finally, the matrix will include a drillthrough action and will be decorated with a sparkline that provides a concise graphical representation of the quarterly sales.

The finished report will resemble the following.

Figure 1

Previewing the Salesperson Summary Report

Task 1 – Creating the Report Server Project

In this task, you will open an existing solution that consists of the completed labs. You will then add a Report Server project to the solution.

  1. Open SQL Server Business Intelligence Development Studio from Start | All Programs | Microsoft SQL Server 2008 R2 | SQL Server Business Intelligence Development Studio.
  2. To open the AdventureWorksBI solution, on the File menu, select Open | Project/Solution.

  3. In the Open Project window, navigate to the Ex1-AuthoringTheSalesSummaryReport\Begin folder located in the Source folder for this lab, select the AdventureWorksBI.sln file, and then click Open.

    Note:
    This solution consists of all completed labs that precede this lab.

  4. In Solution Explorer, if necessary, collapse the Populate DW project.
  5. To add a new project to the solution, on the File menu, select Add | New Project.
  6. In the Add New Project window, select Report Server Project.
  7. In the Name box, replace the text with Sales Reports, and then click OK.
  8. To save the solution, on the File menu, select Save All.

Task 2 – Configuring the Report Server Project

In this task, you will configure the TargetServerURL property as preparation for the deployment task later in this lab.

  1. In Solution Explorer, right-click the Sales Reports project, and then select Properties.

  2. In the Sales Reports Property Pages window, set the TargetServerURL property to https://<servername>/ReportServer, and then click OK.

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

Task 3 – Creating the Shared Data Source

In this task, you will create a shared data source for the AdventureWorksDW2008R2 SQL Server relational database. It is a best practice to create a single shared data source per database and then reference it in reports.

  1. In Solution Explorer, in the Sales Reports project, right-click the Shared Data Sources folder, and then select Add New Data Source.
  2. In the Shared Data Source Properties window, in the Name box, replace the text with AdventureWorksDW2008R2.
  3. To construct the connection string, click Edit.

  4. In the Connection Properties window, configure the connection properties based on the following, and then click OK.

    Property

    Value

    Server Name

    SqlServerTrainingKitAlias

    Database Name

    AdventureWorksDW2008R2

  5. In the Shared Data Source Properties window, click OK.
  6. To save the entire solution, on the File menu, select Save All.

Task 4 – Adding the Sales Detail Report

In this task, you will add an existing report. This report will be configured as the target for a drillthrough action in Task 12.

  1. In Solution Explorer, in the Sales Reports project, right-click the Reports folder, and then select Add | Existing Item.
  2. In the Add Existing Item window, navigate to the Assets folder located in the Source folder for this lab, select the Salesperson Detail.rdl file, and then click Add.
  3. To save the entire solution, on the File menu, select Save All.

Task 5 – Creating the Sales Summary Report

In this task, you will create a new report and set the report’s Description property.

  1. In Solution Explorer, in the Sales Reports project, right-click the Reports folder, and then select Add | New Item.

    Note:
    The Add New Report option launches the Report Wizard which is not required in this lab.

  2. In the Add New Item window, in the Templates pane, select Report.

    Figure 2

    Selecting the Report Template

  3. In the Name box, replace the text with Salesperson Summary, and then click Add.

    Note:
    The report is automatically opened in the report designer.

  4. To select the report, click anywhere inside the report designer, and then in the Properties window, set the Description property to Sales by salesperson and quarter.

    Note:
    If the Properties window is not open, on the View menu, select Properties Window.

  5. To save the entire solution, on the File menu, select Save All.

Task 6 – Creating the Report Data Source

In this task, you will create a reference to the AdventureWorksDW2008R2 shared data source created in Task 3.

  1. In the Report Data window (located on the left), right-click the Data Sources folder, and then select Add Data Source.
  2. In the Data Source Properties window, in the Name box, replace the text with AdventureWorksDW2008R2.

  3. Select the Use Shared Data Source Reference option, and then in the corresponding dropdown list, select the AdventureWorksDW2008R2 shared data source.

    Figure 3

    Creating the Report Data Source

  4. Click OK.

Task 7 – Creating the Report Dataset

In this task, you will create the main dataset used by the report. You will import the query definition from a file, and then modify the query to support a query parameter.

  1. In the Report Data window, right-click the AdventureWorksDW2008R2 data source, and then select Add Dataset.
  2. In the Dataset Properties window, in the Name box, replace the text with dsMain.
  3. To create the query, click Query Designer.
  4. Maximize the Query Designer window.
  5. To import a query file, on the toolbar, click Import.
  6. In the Import Query window, navigate to the Assets folder located in the Source folder for this lab, select the dsMain.sql file, and then click Open.
  7. Review the imported query, and in particular notice the WHERE clause that restricts the query result to calendar year 2007.

  8. On the toolbar, click the Run button.

    Figure 4

    Clicking the Run Button

  9. Review the query result, and in particular notice the five columns that are returned.

    Note:
    Defining a query that restricts the query result for a particular year does not promote reuse of this report for other years. You will parameterize this query which will allow executing this query for any year in the following steps on this task.

  10. In the query statement, in the WHERE clause, replace 2007 with @Year.

    T-SQL

    WHERE (CalendarYear = @Year)

  11. Click OK to commit the query and close the Query Designer window.

    Note:
    Before committing the dataset properties, notice in the Report Data window that the Parameters folder is empty. When you click OK in the next step, appreciate that the dataset designer automatically creates a parameter to support collecting a value from the user to substitute into the query parameter.

  12. Click OK.

  13. In the Report Data window, expand the Parameters folder, and notice the Year report parameter.

    Figure 5

    Reviewing the Report Data Window

Task 8 – Creating the Year Dataset

In this task, you will create an additional dataset to provide the available values for the Year report parameter.

  1. In the Report Data window, right-click the AdventureWorksDW2008R2 data source, and then select Add Dataset.
  2. In the Dataset Properties window, in the Name box, replace the text with dsYear.
  3. To import a query file, click Import.
  4. In the Import Query window, navigate to the Assets folder located in the Source folder for this lab, select the dsYear.sql file, and then click Open.
  5. Click OK.

Task 9 – Configuring the Year Report Parameter

In this task, you will configure the Year report parameter to prompt the user with available values sourced from the dsYear dataset.

  1. In the Report Data window, right-click the Year parameter, and then select Parameter Properties.
  2. In the Report Parameter Properties window, select the Available Values page.
  3. Select the Get Values From a Query option.

  4. Configure the query properties based on the following, and then click OK.

    Property

    Value

    Dataset

    dsYear

    Value Field

    CalendarYearKey

    Label Field

    CalendarYearLabel

    Note:
    The Value Field is usually a database key and the Label Field is used to present labels to the user in a dropdown list.

Task 10 – Authoring the Report Header

In this task, you will introduce a report header. Inside the report header you will then add a report title and image.

  1. In the report designer, right-click inside the report body (the white rectangle), and then select Insert | Page Header.

  2. Select the Toolbox tab.

    Note:
    If the Toolbox is not visible, on the View menu, select Toolbox.

  3. To add a report title, from the Toolbox, drag the Textbox into the top left corner of the page header.
  4. Right-click the textbox, and then select Expression.

  5. In the Expression window, in the Category list, select Built-in Fields, and then in the Item list, double-click ReportName.

    Note:
    This expression will dynamically assign the catalogued report name to the textbox.

  6. Click OK.

  7. Ensure that the textbox is selected, and then in the Properties window, configure the following:

    Note:
    If the Properties window is not visible, on the View menu, select Properties Window.

    Note:
    The following location size properties are in inches. If your environment is set to metric measurements, you will need to append in (for inches) to the end of the value.

    Property

    Value

    BorderStyle | Bottom

    Solid

    Font | Size

    22

    Location | Left

    0

    Location | Top

    0

    Size | Width

    4

    Size | Height

    0.4

  8. To add the company logo into the page header, from the Toolbox, drag the Image and drop it to the right of the report title.
  9. In the Image Properties window, click Import.
  10. In the Open window, navigate to the Assets folder located in the Source folder for this lab, select the AdventureWorksLogo.jpg file, and then click Open.
  11. In the Image Properties window, click OK.

  12. Ensure that the image is selected, and then in the Properties window, configure the following:

    Property

    Value

    Location | Left

    4.5

    Location | Top

    0

    Size | Width

    2

    Size | Height

    0.6

  13. The report header design should resemble the following.

    Figure 6

    Reviewing the Report Header Design

Task 11 – Configuring the Matrix Data Region

In this task, you will add a Matrix to the report body and configure it to display the salespeople on the rows, calendar quarters on the columns, and at the intersection of both groups, the sum of sales.

  1. From the Toolbox, drag the Matrix and drop it into the top left corner of the body of the report.

  2. Ensure that the matrix is selected, and then in the Properties window, configure the following.

    Property

    Value

    Location | Left

    0

    Location | Top

    0

  3. To introduce the Year parameter as a label, right-click in the top left textbox of the matrix, and then select Expression.

    Figure 7

    Setting the Top Left Textbox Matrix Expression

  4. In the Expression window, in the Category list, select Parameters, and then in the Values list, double-click Year.

  5. In the Expression box, replace the Value property with the Label property, and then click OK.

    VB.NET

    =Parameters!Year.Label

    Note:
    It is a best practice to include parameter selections in the report so that it describes the data that is presented. The expression you just developed will place the Year parameter label into the header row of the matrix.

  6. To configure the row groups, hover over the Rows textbox, click the Field List icon, and then select AdventureWorksDW2008R2 | dsMain | SalespersonName.

    Figure 8

    Clicking the Field List Icon

  7. To configure the column groups, hover over the Columns textbox, click the Field List icon, and then select CalendarQuarter.
  8. To format the CalendarQuarter field value, right-click the [CalendarQuarter] textbox, and then select Expression.

  9. In the Expression window, in the Expression box, modify the expression to the following, and then click OK.

    VB.NET

    ="Q" & Fields!CalendarQuarter.Value

  10. Ensure the CalendarQuarter textbox is selected, and then in the Properties window, modify the TextAlign property to Center.

  11. To configure the matrix data, hover over the Data textbox, click the Field List icon, and then select SalesAmount.

    Note:
    The select field is aggregated using the Sum function. If required you can modify the expression to a different aggregate function (Count, Min, Max, etc.).

  12. To format the sales values, right-click the [Sum(SalesAmount)] textbox, and then select Text Box Properties.
  13. In the Text Box Properties window, select the Number page.
  14. In the Category list, select Number.

  15. Reduce the Decimal Places to 0 (zero), check the Use 1000 Separator checkbox.

    Figure 9

    Formatting the SalesAmount Value

  16. Click OK.
  17. To add totals to the rows, right-click the [SalespersonName] textbox, and then select Add Total.
  18. To add totals to the columns, right-click the CalendarQuarter (labeled «Expr»)textbox, and then select Add Total.

  19. To format the column widths, select the first column guide, and then in the Properties window, modify the Size |Width property to 1.8.

    Figure 10

    Selecting the Column Guide

  20. Repeat the last step to modify the width of the remaining two columns to 0.8.

  21. To format the header row, select the first row guide, and then in the Properties window, configure the following.

    Figure 11

    Selecting the Row Guide

    Property

    Value

    BackgroundColor

    LightGrey

    Font | FontWeight

    Bold

  22. To format the footer row, select the last row guide, and then in the Properties window, modify the Font |Weight property to Bold.

  23. The matrix design should resemble the following.

    Figure 12

    Reviewing the Matrix Design

Task 12 – Configuring the Drillthrough Action

In this task, you will configure an action on the SalespersonName textbox to jump to the Salesperson Detail report. This will involve formatting the textbox to provide a visual clue to report consumers that there is an action, and passing the context of the calendar year and salesperson to the report parameters of the Salesperson Detail report.

  1. Right-click the [SalespersonName] textbox, and then select Text Box Properties.

  2. In the Text Box Properties window, configure the properties based on the following:

    Page

    Property

    Value

    Font

    Color

    Blue

    Font

    Effects

    Underline

  3. Select the Action page.
  4. Select the Go to Report option.
  5. In the Specify a Report dropdown list, select the Salesperson Detail report.
  6. To add a parameter, click Add.
  7. In the Name dropdown list, select the Year report parameter, and then in the corresponding Value dropdown list, select [CalendarYear].
  8. To add the second parameter, click Add again.

  9. In the Name dropdown list, select the Salesperson report parameter, and then in the corresponding Value dropdown list, select [EmployeeID].

    Figure 13

    Configuring the Textbox Action

  10. Click OK.

Task 13 – Introducing the Sparkline

In this task, you will add a Sparkline to represent trend of a sales by quarter for each salesperson.

  1. Right-click the last column guide, and then select Insert Column | Right.
  2. To add a title for the new column, click inside the header textbox, and then type Trend.
  3. From the Toolbox, drag the Sparkline and drop it into textbox directly beneath the Trend textbox.
  4. In the Select Sparkline Type window, in the Area group, select the first type, and then click OK.
  5. Click the Sparkline to open the Chart Data pane (which opens to the right – you may need to scroll to the right to see it).

  6. In the Chart Data pane, click the plus sign to the right of Values, and then select the SalesAmount field.

    Figure 14

    Assigning the Sparkling Value

  7. Click the plus sign to the right of Category Groups, and then select the CalendarQuarter field.

    Note:
    The sparkline is a miniature chart. You have configured the chart to plot sales amount against the category (X axis) consisting of the calendar quarters.

  8. The matrix design should resemble the following.

    Figure 15

    Reviewing the Matrix Design

Task 14 – Previewing the Report

In this task, you will preview the report.

  1. In the report designer, click the Preview tab.
  2. In the Year report parameter, select CY2007, and then click View Report.
  3. Review the report layout and data.

  4. To view a different year, in the Year report parameter, select CY2008, and then click View Report.

    Note:
    The Adventure Works sample sales data finishes at the second calendar quarter of 2008. Notice that the matrix, characterized by dynamic columns and rows, produces just the two quarter columns.

Task 15 – Deploying the Report Server Project

In this task, you will deploy the project, consisting of the shared data source and two reports, to the Report Server.

  1. In Solution Explorer, right-click the Sales Reports projects, and then select Deploy.
  2. When deployment completes (note the status line at the bottom of the window), review the feedback in the Output window.
    Note:
    You will need to run SQL Server Business Intelligence Development Studio with Administrator privileges.

Task 16 – Exploring the Report Items in Report Manager

In this task, you will open Report Manager to explore the data source and report that you developed in this lab. You will then perform a drillthrough to the Salesperson Details report, and then export the report to Excel.

  1. Open Internet Explorer from Start | All Programs | Internet Explorer.
  2. In the Internet Explorer window, in the URL box, type https://<servername>/Reports, and then press Enter.
  3. In the Report Manager application, click the Data Sources folder.
  4. Click the AdventureWorksDW2008R2 data source, and review the properties.

  5. Select the Dependent Items page.

    Figure 16

    Selecting the Dependent Items Page

  6. Click the Salesperson Summary report.
  7. In the Year report parameter, select CY2007, and then click View Report.
  8. Click the salesperson Blythe, Michael to drillthrough to the Salesperson Details report.

  9. On the toolbar, click the Export button, and then select Excel.

    Figure 17

    Exporting the Salesperson Details Report to Excel

  10. In the File Download window, click Open.
  11. In Excel, review the report.

Task 17 – Finishing Up

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

  1. In Excel, on the File tab, select Exit.
  2. Close the Internet Explorer window.
  3. In SQL Server Business Intelligence Development Studio, on the File menu, select Exit.

< previous | next >

Downloads

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

Contents