Lesson 2: Adding a Table with the Table or Matrix Wizard (Report Builder 2.0)

1. Click the Design button to switch to design view.

2. On the design surface, under Click to add data, click Table or Matrix.

   The Choose a connection to a data source page opens.

   If there is a designated shared data sources folder on the report server, a list of data sources appears.

3. Select the shared data source that points to the AdventureWorks2008 sample database, or click Browse to browse to the shared data source, and then click Open.

4. Click Next.

You have added a shared data source reference to the sample database. Next, you will create a query that specifies which data to use.

1. On the Design a query page, the relational query designer is open. For this tutorial, you will use the text-based query designer.

   Click Edit As Text. The text-based query designer displays a query pane and a results pane.

2. Paste the following Transact-SQL query into the Query box.

   SELECT
     SH.OrderDate
     ,SH.SalesOrderNumber
     ,SD.OrderQty
     ,SD.LineTotal
     ,P.Name AS [Product]
     ,PS.Name AS [Subcategory]
   FROM
     Sales.SalesOrderHeader AS SH
     INNER JOIN Sales.SalesOrderDetail AS SD
      ON SH.SalesOrderID = SD.SalesOrderID
     INNER JOIN Production.Product AS P
      ON SD.ProductID = P.ProductID
     INNER JOIN Production.ProductSubcategory AS PS
      ON PS.ProductSubcategoryID = P.ProductSubcategoryID
     INNER JOIN Production.ProductCategory AS PC
      ON PC.ProductCategoryID = PS.ProductCategoryID
   WHERE PC.Name = 'Clothing' AND SH.OnlineOrderFlag = 0
   ORDER BY P.Name
   

3. Click Run (!) on the toolbar to run the query.

   The query runs and displays the result set for the fields OrderDate, SalesOrderNumber, OrderQty, LineTotal, Product, and Subcategory from five tables in the AdventureWorks2008 database. The query makes use of Transact-SQL functionality such as aliases. For example, the SalesOrderHeader table is called SH.

   In the result set, the column headings are based on the column names in the database tables but are not saved in the report.

   In the dataset, the field names are based on the column names and are saved in the report. After you complete the wizard, you can use the Report Data pane to view the collection of dataset fields.

4. Click Next.

You have added a dataset with a query that specifies which data to use in the table. Next, you will arrange the data in groups.

1. On the Arrange fields page, drag Product to Values.

2. Drag OrderQty to Values and place below Product.

   The default for numeric fields is to summarize the data with the Sum function. You can use the drop-down arrow to select a different summary function.

3. Drag LineTotal to Values and place below OrderQty.

   Steps 1, 2, and 3 specify the data to display in the table.

4. Drag OrderDate to Row groups.

5. Drag SalesOrderNumber to Row groups and place below OrderDate.

   Steps 4 and 5 organize the values for the fields first by date, and then by all orders for that date.

6. Click Next.

When you run the report, the table displays each date, all orders for each date, and all products, quantities, and line totals for each order. Next, you will choose a layout for the table.

1. On the Choose the Layout page, under Options, verify that Show subtotals and grand totals is selected.

2. Verify that Blocked, subtotal below is selected.

   The Preview pane displays a table with five rows. When you run the report, each row will display in the following way:

   1. The first row will repeat once for the table to show column headings.

   2. The second row will repeat once for each line item in the sales order and display the product name, order quantity, and line total.

   3. The third will repeat once for each sales order to display subtotals per order.

   4. The fourth row will repeat once for each order date to display the subtotals per day.

   5. The fifth row will repeat once for the table to display the grand totals.

3. Clear the option Expand/collapse groups. In this tutorial, the report you create does not use the drilldown feature that enables a user to expand a parent group hierarchy to display child group rows and detail rows.

4. Click Next.

   You have designed the table layout. Next, you will choose a style.

1. On the Choose a Style page, in the Styles pane, select a style.

   A style specifies a font style, a set of colors, and a border style. When you select a style, the Preview pane displays a sample of the table with that style.

2. Click Finish.

   The table is added to the design surface. The table has 5 columns and 5 rows. The Row Groups pane shows three row groups: OrderDate, SalesOrderNumber, and Details. Detail data is all the data that is retrieved by the dataset query.

3. Click Run to preview the report.

   For each line item in a sales order, the table displays the product name, the quantity ordered, and the line item total. This data is organized first by order date and then by order number.