Reporting

The following walk through demonstrates how to create a Master-Detail report using a SubReport report item to contain detail data. The master report data in this example consists of product categories from the AdventureWorks database. The detail report displays product names and numbers.  A Report Parameter is used to filter the detail report on product category.

Create the Detail Report

  1. Create a new class library named "MasterDetail" to contain the reports.
  2. Add a new Telerik Report item to the project and name it DetailReport.
  3. The Report Wizard will display automatically, cancel it. Click on the report selector (upper left corner of design surface) to select the report.

  4. Navigate to its DataSource property, open it, select "Add New Data Source" and select SqlDataSource Component.
  5. Setup the data connection to the AdventureWorks database, save it so it can be used later when creating the master report and click Next. Enter the following SQL statement and Finish the DataSource wizard.
    CopySQL
    SELECT 
        P.Name,ProductID,ProductNumber,PC.ProductCategoryID
    FROM 
        Production.Product P,
        Production.ProductSubcategory PS,
        Production.ProductCategory PC
    WHERE 
        P.ProductSubcategoryID=PS.ProductSubcategoryID
        AND PS.ProductCategoryID=PC.ProductCategoryID
  6. Remove the page header and page footer sections. Open the Data Explorer and add the ProductNumber and Name fields to the detail section of the report.

The detail report should now look something like this in the designer.

Configure the Report Parameter and Filter

This next set of steps creates a report parameter and a filter that uses the report parameter. The master report passes in the current ProductCategoryID for each record and the detail report prints all products that have that ProductCategoryID.

  1. In the Property Window navigate to the ReportParameters property and click the ellipses.
  2. In the ReportParameter Collection Editor click the Add button. In the Name property for the parameter enter ProductCategoryID. Enter zero for the Value property.
  3. In the Property Window navigate to the Filters property and click the ellipses.
  4. Click the New button to create a new filter. In the Expression drop down for the filter select =Fields.ProductCategoryID. Leave the operator at the default equals ("=") sign. In the Value property drop down select the report parameter we created in earlier step: =Parameters.ProductCategoryID.Value.
  5. Click the OK button to close the dialog.

Create the Master Report

The master report will contain only a simple listing of product categories.

  1. Add a new report item to the project and name it MasterReport.
  2. The Report Wizard will display automatically, select New Report.
  3. On Choose Data Source page of the wizard, click Add New Data Source button and select SqlDataSource Component. On the Choose Your Data Connection page of the wizard, select the saved database connection you created for the detail report. Click the Next button.
  4. Enter the SQL statement below and click the Next button.
    CopySQL
    SELECT ProductCategoryID,Name FROM Production.ProductCategory
  5. In the Design Data Layout page of the wizard add the Name column to the detail section and click the Next button. Continue taking the defaults until you finish the Wizard.

Configure the SubReport

This next set of steps ties both reports together using a SubReport to display the detail and passing the current ProductCategoryID from each record of the master report to the detail. The detail report uses the parameter to filter the data to only products for the specified product category.

  1. Resize the detail section to create some vertical room for a SubReport item.
  2. Drag a SubReport from the Toolbox to the detail section of the report.
  3. In the Properties Window for the SubReport, open the ReportSource dialog, select Type name (TypeReportSource) and set it to the DetailReport.
  4. Click the Edit Parameters button that would display the Edit Parameters dialog.
  5. Click the New button to define a parameter.
  6. Set the Parameter Name to ProductCategoryID and the parameter value to =Fields.ProductCategoryID.
  7. Click the OK button to close the parameters dialog and click OK once again to close the Report Source dialog.

    Click the Preview button to preview the MasterReport. Notice that product numbers and names from the detail report display under each product category from the master report.