This tutorial builds on the Grouping Tutorial so if you haven't yet completed it, please do so before starting this 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 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).