Lesson 2: Adding a Table with the Table or Matrix Wizard (Report Builder 2.0)
After you create a report, you can use the Table wizard to add a table and data to your report. The wizard helps you define a connection to a data source, create a query to specify which data to use, organize the data in groups and add rows to display summarized values for the grouped data.
In this lesson, you will use the AdventureWorks2008 sample database as your source of report data.
To launch the table wizard and add a connection
Click the Design button to switch to design view.
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.
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.
You have added a shared data source reference to the sample database. Next, you will create a query that specifies which data to use.
To define a Transact-SQL query for report data
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.
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
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.
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.
To organize data into groups
On the Arrange fields page, drag Product to Values.
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.
Drag LineTotal to Values and place below OrderQty.
Steps 1, 2, and 3 specify the data to display in the table.
Drag OrderDate to Row groups.
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.
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.
To add subtotals and totals
On the Choose the Layout page, under Options, verify that Show subtotals and grand totals is selected.
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:
The first row will repeat once for the table to show column headings.
The second row will repeat once for each line item in the sales order and display the product name, order quantity, and line total.
The third will repeat once for each sales order to display subtotals per order.
The fourth row will repeat once for each order date to display the subtotals per day.
The fifth row will repeat once for the table to display the grand totals.
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.
You have designed the table layout. Next, you will choose a style.
To specify a table style
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.
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.
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.
You have successfully added a table to a new report by using a wizard. Next, you will add formatting to the report. See Lesson 3: Formatting a Report (Report Builder 2.0).