Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet 365 Financials
Jet Professional
Jet Enterprise
Submit a Request

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 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).

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request