Lesson 1: Defining a Dataset Query for a Matrix Report

SQL Server 2008 R2

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.


    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:

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

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.

Community Additions