Windward OhanaReport Designer Office Edition User Manual FAQsHow Do I Group Data Using Nested ForEach Tags?

How Do I Group Data Using Nested ForEach Tags?

This article demonstrates how to group items using nested ForEach Tags.  Nesting ForEach Tags consists of creating two loops, an inner loop and an outer loop, such that for each item returned by the outer loop, the inner loop executes completely and returns its items associated to the outer loop's item. For example, an outer loop could be a list of Customers, and the inner loop a list of Orders; the result of executing both loops would be a list of Orders grouped by Customer.

When Do I Use This Feature?

Nested ForEach loops are best used in cases where items from two or more datasets (tables or nodes) must be correlated with one another to create a single, integrated list or table. The entries in the final list or table would share at least one common item of data, such as a customer number. 

In this example, you will see how to create an outer loop containing a list of customers and associate it with an inner loop containing a list of orders.  The result will give you a full list of customers referencing each order placed by each customer.  This technique allows you to group orders by the customers that requested them.

This feature is typically used when data items are stored in different locations in your data source (such as different tables or different nodes).  A common field (column, node, attribute, or value) is necessary in both data items to filter them in the inner ForEach Tag and create the relation between the two loops and data values they return.  The resulting output will first return the data value for the outer loop and then return all the related data values for the inner loop before repeating by advancing to the next data value returned by the outer loop.

Sample Template

We'll use the attached sample template to develop our example of using Nested ForEach Tags to group Orders by Customers. The sample template uses our public SQL Server, XML, JSON and OData data sources. 

Create the Outer ForEach Loop of Customers

First we'll create the outer ForEach loop which lists all customers in our example data source.

In the SQL example, select the "[Customer Outer Loop]" ForEach Tag, and click on the Wizard button on the AutoTag ribbon. This brings up the SQL Wizard on the ForEach Tag.

In the Wizard, drag the CompanyName and CustomerID columns of the Customers table from the Data Pane onto the Columns pane. We want to display the CompanyName in our output as the Customer, and group together the Orders for each Customer. The CustomerID column is common to both the Customers and Orders table, so it is the common column we'll use to associate Customers to their Orders.

Create the Inner ForEach Loop of Orders

Next we'll create an inner ForEach loop containing the columns and values that will be grouped and output.

Bring up the SQL Wizard on the "[Orders Inner Loop]" as was done above.

Drag the CompanyName field from the Customers table and the OrderID field from the Orders table from the Data Pane onto the Columns pane.  The generated output preview shows each Company with all associated Orders next to it.  The output is now grouped first by Company and then by Orders.  

At this point if you generate output, the inner ForEach loop will print every Company and every Order for each iteration of the outer ForEach loop.  This would generate a lot of data and not give the desired result. The next step demonstrates adding a variable filter to the inner ForEach loop select to return only the orders associated with the current outer ForEach loop iteration.  

For example:  the first iteration of the outer ForEach loop returns "Alfreds Futterkiste." By filtering the inner ForEach loop for the company "Alfreds Futterkiste," the inner ForEach loop will only return the orders associated with that company.   The next time the outer ForEach loop iterates, the inner ForEach loop will only return the orders associated with the company "AnaTrujillo Emparedados y helados".

To output only one Company and all of its Orders in one iteration of the outer ForEach loop, a filter needs to be applied to the inner ForEach loop select statement.

  • Open the Wizard of the inner ForEach loop. Select the inner ForEach Tag "[Orders Inner Loop]" and bring up the SQL Wizard as above.

  • Set a filter in the inner ForEach loop Wizard by creating a condition where the common field from the inner ForEach loop is equal to the common field from the outer ForEach loop variable (in this case the CustomerID column in the Customers and Orders tables).

In this example the the outer ForEach loop lists the Customers using the Customers.CompanyName column, and the inner ForEach loop lists the Orders using the Orders.OrderID column. The Customers and Orders tables are linked by common column CustomerID.

Insert Out Tags to Display the Orders Grouped by Customers Data

The Out Tag for the Company Name is inserted before the inner ForEach Tag loop.  This prevents the Company Name from repeating each time the inner ForEach Tag loop iterates.

With the inner and outer ForEach loops filtered, the Out Tags in both loops need data assigned to them to generate the correct values when output. Both Out Tags need to have values assigned to them coming from the outer and inner ForEach loops.  This is done so every time each ForEach loop iterates a new value is output instead of the same value each time.  

In the Out Tag "[CompanyName]", use the Tag Editor or Data Tree to drag the Customers.CompanyName column onto the Query pane.  There it will appear as the variable ${Customers.CompanyName}, causing the Out Tag to output the name of each customer's Company each time the outer ForEach loop iterates.

In the Out Tag "[Orders]" for the inner ForEach loop, use the Data tree or Tag editor to drag the Orders.OrderID column onto the Query Pane. It will now appear as the variable ${Orders.OrderID}, causing the Out Tag to output the value of each associated Order for the outer ForEach Tag Company.  

Final Output

The final output will return separate tables for each customer that lists the Company Name in the first row followed by several rows of Orders associated with each company:

0 Comments

Add your comment

E-Mail me when someone replies to this comment