Multi-level Grouping and Subtotaling Tutorial

This tutorial builds on the Grouping Tutorial so if you haven't yet completed it, please do so before starting this tutorial.

Dynamics NAV Cronus Example

Often you want a report to contain more than one level of grouping. For instance, you might want to add more detail to the above example by not only grouping the customers by state but by city as well. The result would be a list of customers grouped by state then by city. The first thing you need to do is create room for this intermediate level of grouping by inserting a column between C and D, and a row between 3 and 4. You can also move the column headings down to the newly inserted row 4, as shown in the picture below.

The next thing you need to do is add the formula to insert the list of cities. Open the Customer table in the Jet Browser, select the Values return option, and drag the City field into cell D4. Note the stair-step pattern of the three NL functions, which is the standard format for grouped reports. Just as you did in the previous example, you must link the list (cities, in this case) to the grouping criteria (the state) using a filter, as shown below.

You should also add the filter for Sales ($) <> 0 to your City NL function. Just as you did before, you will we have to adjust the size of the replicator regions to account for the new level of grouping (in effect, nesting the NL functions). In order to do this, you have to change the What argument in the newly added NL function from "Rows" to "Rows=2" and in the NL function listing states from "Rows=5" to "Rows=6". You do not need to adjust the subtotal (you will still do a subtotal by state). Since you have increased the size of the outermost region, you will have to move the subtotaling region of the grand total function down one row. This will allow for the anchor row, as shown in the picture below.

If you run the report as is you see that the first city under each state is the only one for which the customer list does not return an error (except in the case of the state values that do not exist which is a special case). In order to see the source of the error select Jet/Tools/Unhide, select cell E9, and press the F2 key. This highlights the cell references that the formula is making and indicates that the cell is attempting to use a blank filter. This is a standard problem when doing multi-level grouping. What you need to do to resolve it is to copy the value from cell C3 over into column B and down, filling the 6-row region of the replicator, as shown below.

Note that you do not want to copy and paste the formula from cell C3, but rather set each cell equal to the adjacent cell (in cell B3, "=C3", in cell B4, "=B3", etc.). Next, you need to adjust the filter in cell E5 to refer to the value (state) that is on the same row to ensure you do not lose the cell reference. Finally, add a filter to the NL formula in E5 to group the customer lists by not only the state but also the newly inserted city as well. See the example below.

For aesthetic purposes, you may also want to hide column B. If you run the report above you will obtain a list of customers grouped by state then by city as stated in the goal. Note that you can add as many levels of grouping as deemed necessary (for instance, you could further expand this example by adding order details by customer).

Access Northwind Example

Often you want a report to contain more than one level of grouping. For instance, you might want to add more detail to the above example by not only grouping the customers by country but by city as well. The result would be a list of customers grouped by city, grouped by country. The first thing you need to do is to create room for this intermediate level of grouping by inserting a column between C and D, and a row between 3 and 4. You can also move the column headings down to the newly inserted row 4, as shown in the picture below.

The next thing you need to do is add the formula to insert the list of cities. Open the Customers table in the Jet Browser, select the Values return option, and drag the City field into cell D4. Note the stair-step pattern of the three NL functions, which is the standard format for grouped reports. As in the previous example, you must link lists to the grouping criteria using filters, as shown below.

You will also have to adjust the size of the replicator regions to account for the new level of grouping (in effect, nesting the NL functions). In order to do this, you have to change the What argument in the newly added NL function from "Rows" to "Rows=2" and in the NL function listing countries from "Rows=3" to "Rows=4".

If you run the report as is, you see that the first city under each country is the only one for which the customer list does not return an error (take Austria as an example). In order to see the source of the error you must select Jet/Tools/Unhide, select the problem cell and press the F2 key. This will highlight the cell references that the formula is making (as seen below) and indicate that you are attempting to use a blank filter.

This is a standard problem when doing a multi-level grouping. What you need to do to resolve it is to copy the value from cell C3 into column B and down, filling the 4-row region of the replicator.

Note that you do not want to copy and paste the formula from cell C3, but rather set each cell equal to the adjacent cell (in cell B3 you have "=C3", in cell B4 you have "=B3", etc.). Finally, you need to adjust the filter in cell E5 to refer to the value (country) that is on the same row to ensure you do not lose the cell reference. See the example below.

If you run the report seen above you will obtain a list of customers grouped by country, grouped by city. Note that you can add as many levels of grouping as you want (for instance, you could further expand this example by adding order details by customer).