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

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.

    Screenshot of the Dataset Properties dialog with the query option 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.

    Screenshot of the Datasets folder showing the AdventureWorksDataset and its fields.

Next step