Creating a Hierarchical Report by Linking Reports

The third approach to building hierarchical reports is implemented using two independent reports that are linked together. In fact, this approach can be used effectively to create a “web” of reports. Instead of embedding the details in the master report, they’re contained in a separate “stand-alone” report.

Step 1: Copy the previous hierarchical report 

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


Step 2: Provide a link from the summary report to the detail report. 

Replace the Action.ShowElement (“actShowOrderDetails”) element beneath the customer ID column (“colCustomerID”) with an Action.Report element and, beneath it, add a Target.Report element. By selecting and dragging, move the Link Params element from beneath the Sub-Report (“sbrOrderDetails”) to it as well.

Figure 3.30: Definition Editor Panel after modifying Action elements

Set the Target.Report element’s Report Definition File attribute to reference the sub-report developed in the previous section. Notice that, when the attribute is selected, you can select from a list of reports in the project that appears in the Suggestions panel.

Figure 3.31: Definition Editor Panel after setting Target element attributes

Step 3: Remove the sub-report.

Remove the More Info Row (“mirOrderDetails”) element and all its child elements. 

Figure 3.32: Definition Editor Panel after removing elements

At this point, the hierarchical report is fully functional, and the summary report should look similar to the original report. When the user selects a Customer ID, the order details for the selected user are displayed in a separate detail report as shown below.

Figure 3.33: Report Preview Panel showing detail report

Of course, these are not the only options for building your reporting application, and these approaches are not mutually exclusive. Often, a combination of two or more of these approaches is used to provide an effective, intuitive reporting application. 

Adding Sub-Report Sorting By Column

To allow sorting by column in a sub-report, add a Sort element to the desired Data Table Column element:

Figure 3.28: Definition Editor Panel after adding Sort element

Set the Data Column attribute to the column to sort by (i.e. OrderDate). In addition, when sorting non-character data, you should set the Data Type attribute to specify the type of data being sorted, in this case Dates.

Figure 3.29: Attributes Panel after setting Sort element attributes

In addition, in the Chapter3Report2 report definition, you need to change the Sub-Report  (sbrOrderDetails) element’s Sub-Report Mode attribute to “IncludeFrame”. 

Hint: The columns in a sub-report only support sorting whenever the Sub-Report element Sub-Report Mode attribute is set to a value of ‘IncludeFrame’. If the Sub-Report Mode attribute is set to a value of ‘Embedded’, then the sort elements are ignored. 

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.

Adding a Report Summary Row

When you made a copy of the Chapter 2 report to use in this chapter, you “remarked” the summary row. This summary row displayed the number of customers in the report. Now, let’s modify the summary-related elements to display the number of orders in the report.

First, right-click and unremark the green Summary Row (“sumCustomerCount”) and Data Column Summary (“csmCustomerCount”) elements.

Next, move the Data Column Summary element from beneath the colCustomerName element to beneath the colOrderCount element. You can do this using the Cut and Paste items in the right-click popup menu or by selecting the element to move and then dragging it to and dropping it on its destination parent element (“colOrderCount”).

Figure 3.18: Definition Editor Panel after moving Data Column Summary element

Now set the Data Column Summary element’s attributes as shown below:

Figure 3.19: Attributes Panel after setting Data Column Summary element settings

At this point, the report should appear in the Preview panel as shown below:

Figure 3.20: Report Preview Panel

Note that the final row of the report now contains the total number of orders.

Adding Report Sorting By Column

Sorting by the customer name was already supported by the report, but let’s add sorting by the order count, too. Add a Sort element to the desired Data Table Column element (“colOrderCount”), as shown below:

Figure 3.16: Definition Editor Panel after adding Sort element

Set its Data Column attribute to the column to sort by (i.e. OrderCount) and its Data Type attribute to specify numerical sorting, instead of alphabetic sorting (i.e. Number), as shown below:

Figure 3.17: Attributes Panel after setting the Data Column Sort element attributes

Hint: The columns in a sub-table don’t support sorting. Use a sub-report instead to allow sorting. 

Preview the report and you’ll see that you can now also sort it by the number of orders.

Adding Drill-Down Details

A common style of hierarchical reporting is a summary report with “drill-down” details. This style of report only shows details whenever the user selects a summary row. 

Step 1: Set the sub-table to be initially hidden

Set the More Info Row element’s attributes as shown below:

Figure 3.13: Attributes Panel after setting the More Info Row element attributes

Preview the report and notice that the order details are no longer visible. 

Step 2: Make the Customer ID value a link that shows the sub-table when clicked

Logi Report includes a number of Action elements that cause processing when their parent element is clicked. We’ll use this to make the Customer ID data value a link. 

Add an Action.ShowElement element to the Label element that displays the Customer ID data (“lblCustomerID”). Set its Element ID attribute to the ID attribute of the More Info Row element (i.e. “mirOrderDetails”) and its ID attribute to something meaningful. 

Figure 3.14: Definition Editor Panel after adding Action element

Preview the report again. Initially, only the customer order summary is displayed, but whenever a Customer ID value is clicked, its Order Details sub-table is displayed. Your report should look something like this:

Figure 3.15: Report Preview Panel

Notice that the Customer ID links work as “toggles”, showing or hiding their detail data each time the links are clicked. 

Hint: If the More Info Row element Show Modes attribute is not set, then initially the order details are displayed. In this case, selecting a Customer ID hides the order details sub-table. 

Creating a Hierarchical Report with Sub-Tables

One approach to presenting hierarchical data in a report is to include the sub-table definition in the report definition.


Step 1: Copy the basic report

We’ll base the hierarchical report on the report developed in Chapter 2. To copy a report, right-click the report, and select the Copy option in the pop-up menu, then right-click the Reports folder, and select the Paste option in the pop-up menu.

Rename the copied file to “Chapter3Report1” and it should open in the Definition Editor.

Figure 3.1: Application Panel - Reports folder pop-up menu

“Comment out” or remark the Data Column Summary (csmCustomerCount), and Summary Row (“sumCustomerCount”) elements. To remark an element, right-click the element and select the Remark option in the pop-up menu.

Figure 3.2: Report Definition element pop-up menu 

Remarking an element provides the capability to temporarily remove an element from a report. The report is rendered as if the remarked elements do not exist. These elements appear green in the Definition Editor. To unremark an element, right-click the element, and select the Remark option on the pop-menu again.

Step 2: Modify the data layer with an SQL GROUP BY clause

Modify the data layer to display summary data for each customer. In this example, we’ll use the GROUP BY SQL clause to summarize the number of orders for each customer. Set the “dlCustomerData” DataLayer element’s Source attribute to the following SQL query:

SELECT Customers.CustomerID, Customers.CompanyName, COUNT(Customers.CustomerID) AS OrderCount 
FROM Customers
INNER JOIN Orders
ON Orders.CustomerID = Customers.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName

Hint: In a SQL SELECT statement, a column or function output can be given a specific name (an “alias”) in the results set using the AS statement to provide more readable results (i.e. AS OrderCount).

Hint: In Logi Studio you can double-click any attribute name, such as “Source”, to open an “Attribute Zoom window” which will give you more space to copy and paste the SQL statement.

Alternate Step 2: Modify the data layer with a Group Filter element

An alternative to the SQL GROUP BY clause is to use the Group Filter element with the Group Aggregate Column attribute set to the column name used to group the rows (i.e. CustomerID).  In general, data layer grouping, sorting, etc. may be implemented in the database (SQL statement or stored procedure) or using the appropriate Logi Report elements.

Implementing these data layer functionalities in the database is more complex, but provides better performance and vice versa.

You will also need to add a Group Aggregate Column sub-element to the GroupFilter element to calculate the number of orders for each customer. Your definition will look like this:

Figure 3.4: Report Definition showing alternate approach

The new elements’ attributes should be set like this:

Figure 3.5:  Attributes Panel settings for Group Filter and Group Aggregate Column elements

If you use this alternate approach, then your SQL statement for the data layer’s Source attribute will be:

SELECT Customers.CustomerID, Customers.CompanyName

FROM Customers

INNER JOIN Orders

ON Orders.CustomerID = Customers.CustomerID

The Group Aggregate Column element adds a new column to the results set in the data layer and it can be referenced like any other column by using the @Data.<ColumnName>~ token (in this case, @Data.OrderCount~).

Step 3: Add an aggregate column to the table

Add a new table column to display the number of orders for each customer. Add a Data Table Column and Label element to your definition as shown below: 

Figure 3.6: Report Definition after adding Data Table Column and Label elements

And set their attributes as shown below:

Figure 3.7:  Attributes Panel settings for Data Table Column and Label elements

No matter which approach we use in Step 2, @Data.OrderCount~ points to the right data.

Step 4: Create the sub-table

At this point, if you preview it, the report displays a list of customers in the database, and the number of orders for each customer. The next step is making the report hierarchical, adding detail data within master data. 

Begin by adding a More Info Row sub-element to the Data Table element and below it, a Sub-Data Table element, as shown below:

Figure 3.8: Definition Editor Panel after adding More Info Row and Sub-Date Table elements. 

In effect, the More Info Row element creates a row within the results set to contain the sub-table. It’s common to set its Span First Column attribute to a value of 2, to indent the sub-table one column.

The More Info Row does not require an ID. Set the ID for the Sub-Data Table element to “sdtOrderTable”. 

Step 5: Define the sub-table data layer

A Sub-Data Table element, like any data table, needs a data layer. However, because of its place in the hierarchy, we need to use a Sub-DataLayer element with it. 

Add a Sub-DataLayer element as a child of the Sub-Data Table to create a data layer that we’ll relate to the parent table data layer (creating the master-detail relationship). In addition, the Sub-DataLayer element must contain a DataLayer and Sub-DataLayerRelationColumn element, as shown below.

Figure 3.9: Definition Editor Panel after adding elements

For this example, we’ll use the “connNorthwind” connection and the following SQL query:

SELECT * FROM Orders

The Sub-Data Layer element only needs an ID attribute. Set the DataLayer.SQL and Sub-Data Layer Relation Column elements’ attributes as shown below:

Figure 3.10: Attributes Panel after setting DataLayer and Sub-Data Layer Relation Column attributes

The two tables in this report, Customers and Orders, will now be related by their CustomerID columns.

Step 6: Define the sub-table columns

Define the columns for the sub-table next. Adding columns to a Sub-Data Table is exactly the same as adding columns to a Data Table (as described earlier), so add Data Table Column and Label elements as shown below:

Figure 3.11: Definition Editor Panel after adding the Data Table Column and Label elements

Set the new elements’ attributes according to this table: 

Element Type	ID	Column Header	Caption
Data Table Column	colOrderID	Order ID
Label	lblOrderID		@Data.OrderID~
Data Table Column	colOrderDate	Order Date
Label	lblOrderDate		@Data.OrderDate~

The report is now fully functional and should look similar to the partial report shown below:

Figure 3.12: Report Preview Panel