Group and Subtotal Data

How-To Videos

The videos demonstrate each step in the process of creating a Grouping report as also described in the example below.

Nesting NL Functions

Since you can create a dynamic list using an NL function, the next logical step is to group the items in the list.  The mechanism for grouping is nested NL functions.

When one NL is inside the region of another, the NL functions are nested.  The outer NL function lists the grouping criteria, and then the inner NL can use the results of the outer NL as a filter.

When you select report mode, the outer NL will evaluate first, inserting copies of its region for each record and filling in its data values.

Once the outer NL has expanded, the inner NL will expand, making copies of its own region for each of the outer NLs.

Grouping Example (using Dynamics NAV)

A simple example of a grouped list is one listing Customers with their sales - grouped by state and subtotaled.

Since Customers are being listed, one good place to start is the Customer table. First you want to list those customers who have sales, then you can worry about the grouping.  The Sales ($) flow field works well as a filter field for this purpose.

1. If you drag and drop the Name , State and Sales ($) fields out of the Jet Browser...

   

   ... then use the Jet Function Wizard (Jfx) to add a filter for Sales ($) <> 0 to the NL function...

   

   ...and run the report, you will get a complete list of the customers who have sales.

   

2. The next step is to group the customers by state. The overall idea is that you want to list unique states that have customers with sales, then use that list as a series of filters for the existing NL that lists customers

   To achieve this goal, select Values in the Jet Browser, then drag and drop the State field into the cell that is one row up and one column to the left of the existing NL command.

   

   In the picture above, you can see the function in cell C3.

3. Next you can add the same filter-field/filter pair as you used in the previous NL command for Sales ($) <> 0 .  If you run the report, you will get a list of state names, then a list of customers as shown below.

   

   Note that the column headers of Name , State and Sales ($) all got copied by the NL function in cell C3 listing the States.  Also note that the first customer state is blank.  In this case, the customers outside the US have a blank state so it is a valid value that should be preserved.

4. The next step is to change the NL command listing States in C3 from Rows to Rows=2 (which will nest the two NL commands as described above) and to move the headings out of the range being copied by that function.

   

   Now when the report runs, the Jet Excel add-in will copy rows 3 and 4 for each state, including the customer list.

5. Next, you can add a filter to the NL listing customers to filter by the state cell. Since you could have blank states, you need to pre-pend "@@" to the beginning of the filter:

   

   (see the article on Blank Filters for more information)

   If you run the report, you will get a list of states, then a list of customers under each state.

   

Subtotaling

The final task is to add a subtotal by state for the Sales ($) field.  This can be either a simple Sum function if you don't want a grand total, or a Subtotal function if you do want a grand total.  For the sake of completeness, this example will calculate subtotals and a grand total.

1. The first Subtotal function goes two rows below the NL listing Customers.  The function should look like the one pictured below.

   

   Note that both rows 4 and 5 are included in the range of the Subtotal function.

2. Next, you need to expand the Rows=2 of the NL that lists States to include rows 3-6 so the new subtotal cell will fall into the region that gets copied.  For aesthetics, you can include row 7 so we have a blank line between states.  The Rows=2 becomes Rows=5

   

3. Now you need to add the Grand Total.  Since there are 5 rows in the region on the state replicator, you need to skip row 8 and put the grand total on row 9 or 10.  We'll use row 10 to allow for further expansion we'll explore in the Multi-Level Grouping Tutorial

   

4. Finally, you can run the report and get a complete list of customers who have sales, grouped and subtotaled by state.

   

Related Articles

• Multi-level Grouping and Subtotaling