Multi-level Grouping and Subtotaling Tutorial

Multi-Level Grouping (Dynamics NAV Example)

Often we want a report to contain more than one level of grouping.  For instance, we might want to add more detail to the earlier example by not only grouping the customers by state, but by city as well.  The result would be a list of customers grouped first by state and then by city.

The first thing we 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.  We can also move the column headings down to the newly inserted row 4, as shown in the picture below.

The next thing we need to do is add the formula to insert the list of cities.  We'll 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 we did in the previous tutorial, we must link the list (cities, in this case) to the grouping criteria (the state) using a filter, as shown below.  We should also add the filter for Sales ($) <> 0 to our City NL function. 

Just as we did before, we will need 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, we 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.  We do not need to adjust the subtotal (we will still do a subtotal by state).

If we were to run the report as-is, we would see that the first city under each state is the only one for which the customer list appears:

If we were to take a look at the functions, we would see that the cell references in our function is not looking at the correct cell.  This is a standard challenge when doing multi-level grouping.  What we 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 we 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, we need to adjust the filter in cell E5 to refer to the value (state) that is on the same row in column B to ensure we do not lose the cell reference.  Finally, we'll 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:

For aesthetic purposes, we may also want to hide column B.  Since we're grouping by state, we also may not want to display column G.

If we run the report, we will obtain a list of customers grouped by state then by city as stated in the goal.

Note that we can add as many levels of grouping as deemed necessary (for instance, we  could further expand this example by adding order details by customer).

Multi-Level Grouping with OFFSET() Function

There are times when using the above method with Cell references can cause performance degradation when NL("Rows") functions replicates a large number of rows. To alleviate this performance hindrance, we have come up with a technique to use Excel functionality to make the same cell references while keeping the expected performance.

First, we need to create room for this OFFSET Grouping by inserting a column between B and C.

The next thing we need to do would be to create a static cell with the value of 0 in column C starting in the same row as the NL("Rows") replicator function.

After, we will create the a function chain with cell references, but with the addition of an incrementing formula using the OFFSET() Function in the cell following the 0.

Then expand the formula to cover the same amount of rows as the original multi-level grouping.

Next, in the NL function where we were previously targeting column B we will now use an OFFSET() function to target column C. The OFFSET() function would be written as OFFSET($C4-$C4,1) which means starting at C4, go up the number of rows specified by the value, then over to the desired column to target the desired cell.

Lastly, we will want to hide this new column.

This will result in the NL Function being able to reference the desired cell with only a few references rather than a long chain that can grow exponentially depending on the number of rows produced.