Learn to use nested ForEach loops in conjunction with an SQL-Group-By statement as well as MSOffice formatting to create a report that is grouped by one variable.
SQL Group-By statements are used to associate a number of variables from your data back to a single parent variable, the variable you’re grouping the other data by. In order to implement a SQL Group-By statement in a table with AutoTag you must use two ForEach Tags, one nested within the other.
In this example we use data from the Northwind sample database available to the public at mssql.windward.net. You can download this template here or follow along in your own version of AutoTag using the Northwind database. This example uses the Customers node from Northwind to build a table where Companies and Contacts are grouped by the Country they are located in.
Download the Slide Version of this tutorial
Try this version if you prefer to click through these instructions slide-by-slide.
Connect to the Northwind database using the following credentials:
Vendor: Sql Server
See Connecting to a Microsoft SQL Database for more help.
Insert a 5X3 table into your template with the following column headers: Country, City, Company, Contact, Phone.
Before we begin tagging, let’s get some basic formatting out of the way.* Merge the 2 cells directly under Country. Then select the cells in the bottom row under City, Company, Contact, and Phone, and remove the border between the second and third row for these cells. (The easiest way is to highlight the bottom cells and then click the Top Border option from the Borders dropdown which will actually remove the line in Word).
Your table should now look like this:
*Without this step, you will generate a report that has the country listed on every row, and an empty row at the end of each Country section. These steps allow a much cleaner presentation (see final Report.)
Insert a ForEach Tag into the second cell of the Country column, and insert the EndForEach Tag outside the table under the Country Column.
- To make sure that the country is only listed once, double click the ForEach Tag to enter the Tag Editor, and then use the Wizard to drag and drop the Country variable (from the Customers node) into the Columns section.
- We’ll also use the Wizard to Sort Countries alphabetically by dragging Country into the Sort section.
- Finally, ensure you select the checkbox for Distinct so AutoTag knows to only pull in one of each Country entry to the report.
- Hit OK, then save your changes and close. Make sure to rename your Tag in the Ribbon.
Place an Out Tag to the right of the ForEach Tag you just created, and use the Data Tree, or double click the tag to bring up the Tag Editor. Expand the <wr:forEach> node and double click the Country variable (Table/Customers/Country). For this outer ForEach loop, there is no need to select anything else from the Customer node since this loop is built around grouping the nested data only by the country they are located in.
Now we’ll create the nested ForEach Tag that we’ll use to populate the rest of our table with Company data for each country.
- Start by inserting a ForEach Tag into the second cell of the City column. Place your EndForEach Tag in the bottom cell of the same column.
- Double click the ForEach Tag and use the Wizard to drag the Customers node into the Column section.
- Next, in the Filter section, create a select statement that sets Country from this dataset as equal to the value of Country from our previous ForEach Tag.
- To do this, in the Filter section, click "click here to add a group," then "click here to add a filter." Click to select a node, and from the new window, select Country (part of Customers) from the Tables node. Keep the statement as "equal to." As for the value, click to show a dropdown -- expand the variable and click the Country variable.
Hit OK, then save and close the tag editor. Set a Nickname for your nested loop (in the AutoTag Ribbon), re-size your cells if needed, and your table should now look like this:
If you don't want to use a nickname, your table may look something like this. However, because it is a tag, the rows will be sized normally when you OutPut even if you don't give a tag a nickname.
Populate the rest of your table with Out Tags in the second cells of the City, Company, Contact, and Phone columns. Click the Out Tag for City, then click the Data Tree (in the AutoTag Ribbon) to select the City data from the second set of data (which is associated with our inner ForEach Loop). Repeat this for the remaining Out Tags.
Your finished template should look like this:
Finish your template with any additional formatting, and then save and run your report by clicking the OutPut button.
Congratulations! Your generated report now has the City, CompanyName, ContactName, and Phone variables from the Northwind database GROUPED BY the Country variable in a stylish, easy-to-read table!