Creating a Hierarchical Report with Sub-Reports

The second approach to building hierarchical reports involves implementing the detail report using a sub-report instead of a sub-table.

Step 1: Copy the current hierarchical report definition

We’ll base this hierarchical report on the previous report developed in Chapter 3. So, as before, copy the “Chapter3Report1” report and rename the copy to “Chapter3Report2”.


Step 2: Create a new, blank report definition that will become our sub-report

With the same controls you used to copy the previous report, use Add New File… to create a new, empty report definition. This will be our sub-report so name it “Chapter3SubReport” and it should open in the Definition Editor.

Figure 3.21: Definition Editor Panel after adding the new report

The Report Header and Report Footer elements are not needed and can be deleted. We’ll finished defining the sub-report in a later step, so right now, in the Applications panel, select the Chapter3Report2 definition. 

Step 3: Replace the sub-table with a sub-report 

In the Chapter3Report2 definition, delete the Sub-Data Table (“sdtOrderTable”) element – this will also delete all of its child elements – and replace it with a Sub-Report element. 

Figure 3.22: Definition Editor Panel after adding Sub-Report element

Set the Sub-Report element’s attributes as shown below. Note that the Attributes panel also identifies this element as an “IncludeFrame”. This is an old name carried forward for the sake of compatibility.

Figure 3.23: Attributes Panel after setting Sub-Report element attributes

Step 4: Link the report and sub-report

Add a Target.Report element beneath the Sub-Report element. Target elements are used in a variety of situations to redirect the user to a different report definition, URL, or process. Then add a Link Params element beneath the Sub-Report element as well. 

Figure 3.24: Definition Editor Panel after adding Target.Report and Link Params elements

And set their attributes as shown below. Note something interesting: the Link Params element has no pre-determined attributes! Instead you click Add… and provide both an attribute name and a value.

Figure 3.25: Attributes Panel after setting element attributes

The Link Parms attributes define a name-value pair that will be passed to the sub-report. In this way, the sub-report will be told which Customer ID to report on. 

Step 5: Build the sub-report definition

In the Application panel, select the definition for our sub-report: Chapter3SubReport. In the Definition Editor, add a Data Table element and DataLayer.SQL element to the sub-report. 

Figure 3.26: Definition Editor Panel after adding Data Table and DataLayer elements

Provide appropriate ID attributes for the elements. In this example, in the data layer, we’ll use the connNorthwind connection and the following SQL query as the source:

SELECT * FROM Orders

WHERE CustomerID = '@Request.CustomerID~'

Notice that the SQL query is using a special token, @Request, to reference the information passed from the Link Parms element in the main report. 

Step 6: Create the sub-report table columns

As with any data table, we’ll need to add Data Table Columns in order to display data. By design, these columns are exactly the same as the ones used in the sub-table in the previous report. This means you can go back to the Chapter3Report1 definition, locate the elements, and simply copy ‘n’ paste them into this sub-report definition. When you copy an element, all of its child elements get copied too, simplifying the process.

Figure 3.27: Copying  Data Table Columns and Label elements from a previous report definition

At this point, your second hierarchical report is fully functional and, when you preview it, it should look similar to the previous report. 

Reminder: the report that gets previewed is the one that’s open in the Definition Editor, so be sure you’ve opened Chapter3Report2 before previewing.