ActiveReports 15 .NET Edition
ActiveReports 15 User Guide / Samples and Walkthroughs / Walkthroughs / Page Report/RDL Report Walkthroughs / Layout / Recursive Hierarchy Reports
In This Topic
    Recursive Hierarchy Reports
    In This Topic

    You can create a report using a recursive hierarchy and the Level function to show parent-child relationships in data. This walkthrough illustrates how to create a recursive hierarchy report.

    The walkthrough is split into the following activities:

    Note:
    • This walkthrough uses the Store table from the Reels database. The Reels.mdb file can be downloaded from GitHub: ..\Samples15\Data\Reels.mdb.
    • Although this walkthrough uses Page reports, you can also implement this using RDL reports.

    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

    Recursive Hierarchy report at design time

    Recursive Hierarchy report at design time

    Run-Time Layout

    Recursive Hierarchy report at run time

    Recursive Hierarchy report at run time

    To add an ActiveReport to the Visual Studio project

    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 15 Page Report and in the Name field, rename the file as rptRecursiveHierarchy.
    4. Click the Add button to open a new fixed page report in the designer.

    See Basic Concepts for information on adding different report layouts.

    To connect the 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.
      Adding a datasource
    2. In the Report Data Source Dialog that appears, select the General page and in the Name field, enter a name like ReportData.
    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 create a dataset to populate the parameter values

    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 Stores. 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 StoreID FROM Store
      
    4. Click the Validate DataSet icon Validate DataSet icon at the top right hand corner above the Query box to validate the query.
    5. Click OK to close the dialog. Your data set and queried fields appear as nodes in the Report Explorer.

    To add a report parameter

    1. In the Report Explorer, right-click the data source node and select the Parameters node or select Parameter from the Add button.
    2. In the Report - Parameters dialog that appears, set the following values:

      In the General tab

      • Name: StoreID
      • DataType: Integer
      • Text for prompting users for a value: Select a store number

      In the Available Values tab select From query

      • Dataset: Stores
      • Value: StoreID
      • Label: StoreID
    3. Click OK to close the dialog and add the parameter under the Parameters node of the Report Explorer.

    To add a dataset for the report

    1. In the Report Explorer, right-click the data source node and select the Add Data Set option.
    2. In the DataSet Dialog that appears, select the General page and enter the name of the dataset as Employees. This name appears as a child node to the data source icon in the Report Explorer.
    3. On the Parameters page, add a parameter with the following properties.
      • Parameter Name: @StoreID
      • Value: =Parameters!StoreID.Value
    4. On the Query page of this dialog, change the Command Type to StoredProcedure and enter the following stored procedure into the Query text box (the question mark denotes the parameter)
      StoredProcedure Query
      Copy Code
      EmployeesForStore ?
      EmployeesForStore ?
    5. Click the Validate icon to validate the query and to populate the Fields list.
    6. Click OK to close the dialog. Your data set and queried fields appear as nodes in the Report Explorer.

    To create a layout for the report

    1. From the toolbox, drag a Table control onto the body of the report and go to the Properties window to set the following properties.
      Property Name Property Value
      Location 0in, 0in
      DataSetName Employees
      FixedSize (only for Page reports) 6.5in, 9in
    2. Click inside the table to display the row and column handles along the left and top edges of the table.
    3. Select the columns by clicking the grey column header above the column and change the Width property in the order as follows.
      Column Property Name
      Second Column Width: 1.5in
      Third Column Width: 1.05in
      First Column Width: 2.2in
    4. Right-click the grey column header above the third column and select Insert Column to the Right.
    5. Select the fourth column and change its Width property to 1.75in.
      Tip: Making some columns narrower before adding columns or making other columns wider prevents your report width from changing.
    6. In the Report Explorer from the Employees dataset, drag the following fields onto the detail row of the table.
      Data Field Column Name
      Title TableColumn1
      LastName TableColumn2
      Supervisor TableColumn3
      Department TableColumn4
    7. Select the LastName field in the second column of the detail row and in the Properties window, set the Value property to =Fields!LastName.Value & " " & Fields!EmployeeID.Value. This will display the Employee ID number along with each employee's last name.
    8. Select the static label in the second column of the header row of the table and in the properties window, set its Value property to Last Name and ID.
    9. Select the Supervisor field in the third column of the detail row and in the Properties window, set its TextAlign property to Center.
    10. Select the static label in the third column of the detail row and in the Properties window, set its Value property to Supervisor ID.
    11. Select the header row by clicking the table handle to the left of the row and in the Properties window, set the following properties.
      Property Name Property Value
      TextAlign Center
      FontWeight Bold
      BackgroundColor DarkSlateBlue
      Color White
    12. Select the detail row and in the Properties window, set the BorderStyle property to Solid.
    13. Right-click the table handle to the left of the header row and select Insert Row Above.
    14. While holding down the CTRL key, click each of the cells in the newly added top row.
    15. Right-click inside the selected cells and select Merge Cells to create a cell that spans the table.
    16. Go to the Properties window to set the following properties.
      Property Name Property Value
      TextAlign Center
      FontSize 14pt
      Value ="Store Number " & Parameters!StoreID.Value
    17. Right-click the table handle to the left of the row and select Table Footer to remove the footer row from the table.
    Note: In case you are setting a recursive hierarchy on a Page report, set the DataSetName property in the Fixed Page Dialog to Employees.

    To set up a recursive hierarchy

    1. Right-click the table handle to the left of the Detail row and select Edit Group to open the Table-Detail Grouping dialog.
    2. Under Group on: Expression, select =Fields!EmployeeID.Value.
    3. Under Parent group:, select =Fields!Supervisor.Value.
    4. Click OK to close the dialog.

    To use the Level function to display the hierarchy

    1. Select the cell in the first column of the Detail row that reads =Fields!Title.Value and in the Properties window, expand the Padding property.
    2. In the Padding > Left property, enter the expression =2 + (Level() * 15) & "pt".
      Note: Adding 2 to the result ensures that a normal amount of padding is always used.

    To use the Level function with themes in the BackgroundColor property of a textbox

    1. Download Reels.rdlx-theme file from GitHub.
    2. Copy the file Reels.rdlx-theme and paste it into the folder in which you saved your report.
    3. In the Report Explorer, select Report.
    4. In the Properties window in the Theme property, enter the theme file name: Reels.rdlx-theme.
    5. Click the table handle to the left of the detail row to select the entire row.
    6. In the Properties window, set the BackgroundColor property to =Theme.Colors(Level() + 1, 4).

    To view the report

    • Click the preview tab to view the report at design time.

    OR

    See Also