Lesson 1: Defining a Dataset Query for a Matrix Report
In this lesson, you will add a new report to the report server project that you created in the tutorial Creating a Basic Table Report, define a data source, and define a dataset query. You will use the AdventureWorks2008R2 sample database as your data source. This tutorial assumes that this database is located in the default instance of SQL Server installed on your local computer.
To open an existing Reporting Services Project
Click Start, point to All Programs, point to Microsoft SQL Server 2008 R2, and then click Business Intelligence Development Studio.
On the File menu, point to Open, and then click Project/Solution.
Navigate to the report server project named Tutorial.
In the Tutorial folder, click Tutorial.sln.
Click Open to open the project.
The Tutorial project is displayed in Solution Explorer.
To create a new report
In Solution Explorer, right-click Reports, point to Add, and click New Item.
If you do not see Solution Explorer, from the View menu, click Solution Explorer.
In the Add New Item dialog box, in the Templates pane, select Report.
In Name, type Sales by Area and Year.rdl and click Add.
The Report Designer opens in Design mode and displays a blank report definition.
To define a Transact-SQL query for report data
In the Report Data pane, click New, and then click Data Source. The Data Source Properties dialog box opens.
In Name, type AdventureWorks2008R2.
Verify that Embedded connection is selected and that the Type is Microsoft SQL Server.
In Connection string, type following:
Data source=localhost; initial catalog=AdventureWorks2008R2
The data source appears in the Report Data pane.
In the Report Data pane, right-click AdventureWorks2008R2, and then click Add Dataset.
In Name, type Sales.
In Query type, verify that Text is selected.
Below the Query pane, click Query Designer to open the text-based query designer.
In the query pane, paste the following Transact-SQL query:
SELECT SOH.SalesPersonID AS ID, P.FirstName, P.LastName, SOH.SalesOrderNumber AS [Order], SOH.OrderDate AS [Date], DATEPART(yy, SOH.OrderDate) AS [Year], DATEPART(mm, SOH.OrderDate) AS [Month], ST.[Group] AS [Geography], ST.CountryRegionCode AS CountryRegion, ST.Name AS Territory, PPC.Name AS Category, PPS.Name AS Subcat, PP.Name AS Product, PP.Color, PP.Size, CASE WHEN PP.Size = 'S' THEN 1 WHEN PP.Size = 'M' THEN 2 WHEN PP.Size = 'L' THEN 3 WHEN PP.Size = 'XL' THEN 4 ELSE PP.Size END AS SizeSortOrder, SUM(SD.OrderQty) AS Qty, SUM(SD.LineTotal) AS LineTotal FROM Sales.SalesPerson AS SP INNER JOIN Sales.SalesOrderHeader AS SOH ON SP.BusinessEntityID = SOH.SalesPersonID INNER JOIN Person.Person AS P ON P.BusinessEntityID = SP.BusinessEntityID INNER JOIN Sales.SalesOrderDetail AS SD ON SD.SalesOrderID = SOH.SalesOrderID INNER JOIN Production.Product AS PP ON SD.ProductID = PP.ProductID INNER JOIN Sales.SalesTerritory AS ST ON ST.TerritoryID = SP.TerritoryID 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, P.LastName, P.FirstName, ST.[Group], ST.CountryRegionCode, ST.Name, PP.Color, PP.Size HAVING (DATEPART(yy,SOH.OrderDate) IN ('2003','2004') AND ST.[Group] = 'North America' AND LEFT(PPS.Name,1) IN ('C','T') AND LEFT(PPC.Name,1) = 'C')
To view the results of the query, click Run (!) on the query designer toolbar.
In the result set, you see the data from 18 fields in seven different tables in the AdventureWorks2008R2 database. This query includes a variety of fields that can be used for grouping data in the report, including year and month from the order date, geographical location of sales territory (for country/region and territory), and product category and subcategory. In addition, the sales data has been filtered to retrieve only sales orders from the years 2003 and 2004, for sales that took place in North America, and for categories Clothing and Components, and for subcategories that begin with the letter C. Filtering is used in this tutorial to create compact examples that can be displayed on a single page.
Click OK. Click OK again.
The fields from the dataset query appear in the Report Data pane.
You have successfully specified a query that retrieves data for your report. Next, you will add a Matrix data region to the design surface and organize data in the matrix by adding groups. See Lesson 2: Adding a Matrix Data Region with Row and Column Groups.