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