• Article

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

  1. Click Start, point to All Programs, point to Microsoft SQL Server 2008 R2, and then click Business Intelligence Development Studio.

  2. On the File menu, point to Open, and then click Project/Solution.

  3. Navigate to the report server project named Tutorial.

  4. In the Tutorial folder, click Tutorial.sln.

  5. Click Open to open the project.

    The Tutorial project is displayed in Solution Explorer.

To create a new report

  1. In Solution Explorer, right-click Reports, point to Add, and click New Item.

    Note

    If you do not see Solution Explorer, from the View menu, click Solution Explorer.

  2. In the Add New Item dialog box, in the Templates pane, select Report.

  3. 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

  1. In the Report Data pane, click New, and then click Data Source. The Data Source Properties dialog box opens.

  2. In Name, type AdventureWorks2008R2.

    Verify that Embedded connection is selected and that the Type is Microsoft SQL Server.

  3. In Connection string, type following:

    Data source=localhost; initial catalog=AdventureWorks2008R2

  4. Click OK.

    The data source appears in the Report Data pane.

  5. In the Report Data pane, right-click AdventureWorks2008R2, and then click Add Dataset.

  6. In Name, type Sales.

  7. In Query type, verify that Text is selected.

  8. Below the Query pane, click Query Designer to open the text-based query designer.

  9. 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')

  10. 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.

  11. Click OK. Click OK again.

    The fields from the dataset query appear in the Report Data pane.

Next Task

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.