Tutorial: Define a dataset for the table report (Reporting Services)

After you define the data source for the paginated report, you define a dataset for a data source. In Reporting Services, a dataset contains data that you use in reports. A dataset includes a pointer to a data source and a query for use by the report, calculated fields, and variables.

In this tutorial, you:

• Define a dataset for the table report.
• Create a Transact-SQL query to retrieve sales order information.

Prerequisites

• Completion of Step 1: Create a report server project.
• Completion of Step 2: Specify connection information.

Define a Transact-SQL query for report data

Create a Transact-SQL query that retrieves sales order information from the AdventureWorks2022 database.

1. Open your Sales Order report definition file (.rdl) in Visual Studio.

2. In the Report Data pane, select New > Dataset.... The Dataset Properties dialog opens with the Query section highlighted.

   

3. In the Name box, enter "AdventureWorksDataset".

4. Select the Use a dataset embedded in my report option.

5. From the Data source list, select AdventureWorks2022.

6. For Query type, select the Text option.

7. Enter, or copy and paste, the following Transact-SQL query into the Query text box.

   SELECT
      soh.OrderDate AS [Date],
      soh.SalesOrderNumber AS [Order],
      pps.Name AS [Subcat],
      pp.Name as [Product],
      SUM(sd.OrderQty) AS [Qty],
      SUM(sd.LineTotal) AS [LineTotal]
   FROM Sales.SalesPerson sp
   INNER JOIN Sales.SalesOrderHeader AS soh
         ON sp.BusinessEntityID = soh.SalesPersonID
      INNER JOIN Sales.SalesOrderDetail AS sd
         ON sd.SalesOrderID = soh.SalesOrderID
      INNER JOIN Production.Product AS pp
         ON sd.ProductID = pp.ProductID
      INNER JOIN Production.ProductSubcategory AS pps
         ON pp.ProductSubcategoryID = pps.ProductSubcategoryID
      INNER JOIN Production.ProductCategory AS ppc
         ON ppc.ProductCategoryID = pps.ProductCategoryID
   GROUP BY ppc.Name, soh.OrderDate, soh.SalesOrderNumber, pps.Name, pp.Name,soh.SalesPersonID  
   HAVING ppc.Name = 'Clothing'
   

8. (Optional) Select Query Designer. The query appears in the text-based Query Designer. Select run to view the results of the query ( ) on the Query Designer toolbar. The dataset displayed contains six fields from four tables in the AdventureWorks2022 database. The query makes use of Transact-SQL functionality such as aliases. For example, the SalesOrderHeader table is called soh.

9. Select OK to exit the Query Designer.

10. Select OK to exit the Dataset Properties dialog. The Report Data pane displays the AdventureWorksDataset dataset and fields.

    

Next step

Step 4: Add a table to the report (Reporting Services)