Tutorial: Define a dataset for the table report (Reporting Services)
Article
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.
Create a Transact-SQL query that retrieves sales order information from the AdventureWorks2022 database.
Open your Sales Order report definition file (.rdl) in Visual Studio.
In the Report Data pane, select New > Dataset.... The Dataset Properties dialog opens with the Query section highlighted.
In the Name box, enter "AdventureWorksDataset".
Select the Use a dataset embedded in my report option.
From the Data source list, select AdventureWorks2022.
For Query type, select the Text option.
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'
(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.
Select OK to exit the Query Designer.
Select OK to exit the Dataset Properties dialog. The Report Data pane displays the AdventureWorksDataset dataset and fields.
Demonstrate methods and best practices that align with business and technical requirements for modeling, visualizing, and analyzing data with Microsoft Power BI.