ActiveReports 12
Subreports in Page/RDL Reports
ActiveReports 12 > ActiveReports User Guide > Samples and Walkthroughs > Walkthroughs > Page Report/RDL Report Walkthroughs > Layout > Subreports in Page/RDL Reports

You can create a Page or an RDL report that hosts a subreport. This walkthrough illustrates how to create an RDL report using a subreport.

The walkthrough is split up into the following activities:

Note: This topic uses the Employee, Sale and SaleDetails tables in the Reels database. By default, in ActiveReports, the Reels.mdb file is located at [User Documents folder]\GrapeCity Samples\ActiveReports 12\Data\Reels.mdb.

When you complete this walkthrough you get a layout that looks similar to the following at design time and at run time.

Design-Time Layout

Run-Time Layout

To add a report for the subreport

  1. Create a new Visual Studio project.
  2. From the Project menu, select Add New Item.
  3. In the Add New Item dialog that appears, select ActiveReports 12 RDL Report and in the Name field, rename the file as Sales.rdlx.
  4. Click the Add button to open a new RDL report in the designer.
  5. In the Solution Explorer, select Sales.rdlx and set the Build Action property to Content and the Copy to Output Directory property to Copy Always.

See Adding an ActiveReport to a Project for information on adding different report layouts.

To connect the subreport to a data source

  1. In the Report Explorer, right-click the Data Sources node and select the Add Data Source option or select Data Source from the Add button.
  2. In the Report Data Source Dialog that appears, select the General page and in the Name field, enter a name like Reels.
  3. On this page, create a connection to the Reels database. See Connect to a Data Source for information on connecting to a data source.

To add a report parameter to the subreport

  1. In the Report Explorer, right-click the Parameters node and select the Add Parameter option or select Parameter from the Add button.
  2. Under Name, enter EmployeeID.
  3. Under Data type, select Integer.
  4. Click OK to close the dialog.

To add a dataset with a parameter to the subreport

When you add a query parameter using the syntax required by your database you must add a parameter to the Parameters page to ensure that the parameter value is passed to the query from the Report Parameters collection.

  1. In the Report Explorer, right-click the data source node and select the Add Data Set option or select Data Set from the Add button.
  2. In the DataSet Dialog that appears, select the General page and enter the name of the dataset as EmployeeSales. This name appears as a child node to the data source icon in the Report Explorer.
  3. On the Parameters page under Parameter Name enter EmployeeID.
  4. Under Value enter =Parameters!EmployeeID.Value
  5. On the Query page of this dialog, in the Query field enter the following SQL query.
    SQL Query
    Copy Code
    SELECT * FROM EmployeeSales
    
  6. Click the Validate DataSet icon at the top right hand corner above the Query box to validate the query.

  7. Click OK to close the dialog. Your data set and queried fields appear as nodes in the Report Explorer.

To add controls to display data on the subreport

  1. From the toolbox, drag a Table data region onto the body of the report and go to the properties window to set the DataSetName property to EmployeeSales.
  2. Click inside the table to display the column and row handles along the top and left sides of the table.
  3. Right-click the handle above the rightmost column and select Insert Column to the Right to add another column. 
  4. Click the column handle at the top of each column in turn to select it, and in the property grid, set the Width property as indicated in the table.
    Column Width
    First 1.5in
    Second 1.5in
    Third 1.2in
    Fourth 1.55in
    Tip: In most cases it is easier to resize existing columns before adding new columns because this prevents the table from growing horizontally and pushing the report width beyond what will fit on paper.
  5. Right-click the handle to the left of the table detail row and select Insert Group to open the Table-Groups dialog.
  6. Under Expression select =Fields!EmployeeID.Value. This groups all details from each employee.
  7. Change the Name to Employee and click OK to close the dialog. A grouping row is added to the table.
    Note: You cannot change the name of a table group until after you have set the expression.
  8. Right-click the handle to the left of the table detail row and select Edit Group to access the Table-Detail Grouping dialog.
  9. Under Expression select =Fields!SaleID.Value and click OK to close the dialog. This lists the total amount of each sale instead of listing each item sold within each SaleID.
  10. Right-click the handle to the left of the grouping row and select Insert Row Below. We will use this new row for static labels that repeat at the top of each new group.
  11. Right-click any handle to the left of the table and select Table Header to toggle off the table header.
  12. Right-click any handle to the left of the table and select Table Footer to toggle off the table footer.
  13. In the Report Explorer, select the Body node and go to the Properties window to set the Size property to 5.75in, 1in so that it fits inside the subreport control on the main report.

To add data fields to the Table data region

  1. In the Report Explorer, from the EmployeeSales dataset, drag the following field onto the first group header row of the table.
    Data Field Column Name Property Name
    Name TableColumn1 FontWeight: Bold
  2. Use the Shift key and the mouse to select the first two cells in the first group header row, right-click and select Merge Cells. This allows the employee name to span two columns in the table.
  3. Using the handle to the left of the first group header row, select the row and set the BackgroundColor property to LightSteelBlue.
    Tip: Even if you do not want to use colors in your finished report, it is often helpful to do so during the design of a report to make identification of the various sections easier for troubleshooting when you preview it.
  4. Enter the following text into the cells in the second group header row of the table.
    Data Field Column Name Property Name
    Sale Date TableColumn1 FontWeight: Bold
    TextAlign: Right
    Sale Number TableColumn2 FontWeight: Bold
    TextAlign: Right
    Quantity TableColumn3 FontWeight: Bold
    TextAlign: Right
    Total TableColumn4 FontWeight: Bold
    TextAlign: Right
  5. Using the handle to the left of the second group header row, select the row and set the BackgroundColor property to LightGray.
  6. In the Report Explorer, drag the following fields from the EmployeeSales dataset onto the detail row of the table.
    Data Field Column Name Property Name
    Sale Date TableColumn1 Format: Short date
    SaleID TableColumn2
    Quantity TableColumn3
    Total TableColumn4 Format: Currency
  7. In the detail row of the table, select the textbox with the Quantity data field and go to the Properties window to change the Value property to =Sum(Fields!Quantity.Value). This adds the Sum aggregate to the expression for the field and shows a summary of the quantity field for each SalesID.
  8. In the detail row of the table, select the textbox with the Total data field and go to the Properties window to change the Value property to =Sum(Fields!Total.Value). This adds the Sum aggregate to the expression for the field and shows a summary of the total field for each SalesID.
  9. In the Report Explorer, from the EmployeeSales dataset, drag the following fields onto the group footer row of the table.
    Data Field Column Name Property Name
    Quantity TableColumn3 Value: =Sum(Fields!Quantity.Value)
    Total TableColumn4 Format: Currency
    Value: =Sum(Fields!Total.Value)
  10. Enter the following text into the indicated cell in the group footer row of the table.
    Text Column Name Property Name
    Employee Total: TableColumn2 FontWeight: Bold
    TextAlign: Right
  11. Using the handle to the left of the group footer row, select the row and in the BackgroundColor property select LightGray.
  12. Go to the preview tab, enter 1035 for the Employee ID, and click the View Report button. You get a layout that looks similar to the following at design time and at run time.
    Design-Time Layout Run-Time Layout

  13. From the File menu, select Save and save this file. This report functions as the subreport you use in the main report.

To create the main report

  1. From the Visual Studio Project menu, select Add New Item.
  2. In the Add New Item dialog that appears, select ActiveReports 12 RDL Report and in the Name field, rename the file as Employees.rdlx.
  3. Click the Add button to open a new fixed RDL report in the designer.
  4. In the Report Explorer, select the Body node and go to the Properties window to set the Size property to 6.5in, 3.6in.

To connect the main report to a data source

  1. In the Report Explorer, right-click the Data Sources node and select the Add Data Source option or select Data Source from the Add button.
  2. In the Report Data Source Dialog that appears, select the General page and in the Name field, enter a name like Reels.
  3. On this page, create a connection to the Reels database. See Connect to a Data Source for information on connecting to a data source.

To add a dataset to the main report

  1. In the Report Explorer, right-click the data source node and select the Add Data Set option or select Data Set from the Add button.
  2. In the DataSet Dialog that appears, select the General page and enter the name of the dataset as EmployeeInfo. This name appears as a child node to the data source icon in the Report Explorer.
  3. On the Query page of this dialog, in the Query field enter the following SQL query.
    SQL Query
    Copy Code
    SELECT * FROM EmployeeInfo
    
  4. Click the Validate icon to validate the query and to populate the Fields list.
  5. Click OK to close the dialog. Your data set and queried fields appear as nodes in the Report Explorer.

To add controls to display data on the main report

The following steps demonstrate how you can add controls and create the main report:

To add a static label to the top of the main report

From the toolbox, drag a TextBox control onto the body of the report and set the following properties:

Property Name Property Value
Font Normal, Arial, 14pt, Bold
Location 0in, 0in
Size 6.5in, 0.3in
TextAlign Center
Value Employee Report by City and Store

To add a List data region that repeats data for each city

  1. Drag a List data region from the toolbox onto the body of the report and with the data region selected, go to the Properties Window to set the following properties:
    Property Name Property Value
    BackgroundColor Silver
    DataSetName EmployeeInfo
    Location

    0in, 0.5in

    Size 6.5in, 3.1in
  2. At the bottom of the Properties Window, select the Property dialog command. See Properties Window for further details on accessing commands.
  3. In the List dialog that appears, select Detail Grouping.
  4. Under Expression, select =Fields!City.Value
  5. Click OK to close the dialog.
  6. From the Report Explorer, drag the City field onto the List data region and set the following properties:
    Property Name Property Value
    FontSize 12pt
    Location 0in, 0in
    Size 6.5in, 0.25in
    TextAlign Center

To nest a second List data region that repeats data for each store within the city 

  1. Drag a List data region from the toolbox onto the the first list and with the data region selected, go to the Properties Window to set the following properties:
    Property Name Property Value
    BackgroundColor Beige
    DataSetName EmployeeInfo
    Location 0.125in, 0.3in
    Size 6.25in, 2.7in
  2. At the bottom of the Properties Window, select the Property dialog command. See Properties Window for further details on accessing commands.
  3. In the List dialog that appears, select Detail Grouping.
  4. Under Expression, select =Fields!StoreName.Value
  5. Click OK to close the dialog.
  6. From the Report Explorer, drag the StoreName field onto the list and set the following properties:
    Property Name Property Value
    FontWeight Bold
    Location 0in, 0in
    Size 2in, 0.25in

To nest a third List data region that repeats data for each employee in the store

  1. Drag a List data region from the toolbox onto the second list and with the data region selected, go to the Properties Window to set the following properties:
    Property Name Property Value
    BackgroundColor White
    DataSetName EmployeeInfo
    Location 0in, 0.25in
    Size 6.125in, 2.125in
  2. At the bottom of the Properties Window, select the Property dialog command. See Properties Window for further details on accessing commands.
  3. In the List dialog, select Detail Grouping.
  4. Under Expression, select =Fields!EmployeeID.Value
  5. Click OK to close the dialog.
  6. From the Report Explorer, drag the following fields onto the list and set the following properties:
    Data Field Property Name
    Name Location: 1.125in, 0in
    Size: 2.625in, 0.25in
    Education Location: 1.125in, 0.25in
    Size: 2.625in, 0.25in
    DateOfBirth Location: 5in, 0in
    Size: 0.875in, 0.25in
    Format: Short date
    PhoneNumber Location: 4.875in, 0.25in
    Size: 1in, 0.25in
  7. From the toolbox, drag five text boxes onto the List and set the following properties:
    TextBox Name Value Property Property Name
    TextBox 1 Name: Location: 0.125in, 0in
    Size: 0.625in, 0.25in
    FontWeight: Bold
    TextBox 2 Education: Location: 0.125in, 0.25in
    Size: 0.875in, 0.25in
    FontWeight: Bold
    TextBox 3 Date of Birth: Location: 3.875in, 0in
    Size: 1in, 0.25in
    FontWeight: Bold
    TextBox 4 Phone: Location: 3.875in, 0.25in
    Size: 0.875in, 0.25in
    FontWeight: Bold
    TextBox 5 Sales Record Location: 0.125in, 0.5in
    Size: 1in, 0.25in
    FontWeight: Bold

To add a Subreport control to the main report

  1. From the toolbox, drag a Subreport control onto the third list and with the control selected, go to the Properties Window to set the following properties:
    Property Name Property Value
    Location 0.125in, 0.75in
    NoRows No sales recorded for this employee during 2005.
    ReportName

    Sales (ensure that this report is saved in the same directory as the Sales report)

    Note: To view the report in the preview tab, you should specify the full path to the subreport.
    Size 5.75in, 1.3in
    Visibility: Hidden True (hides the subreport initially)
    Visibility: ToggleItem Sales Record text box added in the previous procedure (puts a toggle image next to the text that shows the subreport when clicked)
  2. At the bottom of the Properties Window, select the Property dialog command. See Properties Window for further details on accessing commands.
  3. On the Parameters page of the Subreport dialog, set the Parameter Name to EmployeeID. This name must match the parameter in the subreport exactly.
  4. Set the Parameter Value to =Fields!EmployeeID.Value.
    Note: You can use the option of having the subreport automatically apply the same theme as the hosting report. This option is available on the General page of the Subreport Properties.
  5. Click OK to close the dialog.

To view the report

OR

Note: Click the + to the left of Sales Record to see the subreport.