Sign Up for Training |
insightsoftware Company Site
Submit a Request
Become a Jet Insider
Give Feedback

Grouping and Subtotaling Tutorial


Related Articles...

How-To Videos

Adding Filters, Lists, and Totals

Adding Columns
and Dimensions
Adding Sheet
Report Options

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.

Excel evaluates the NL functions from left to right, and top to bottom so if you have put one NL function that acts on rows in cell D4, and one that acts on columns in cell B5, the Jet Excel add-in will evaluate cell B5 first.

Grouping Example (using Dynamics NAV)

  1. If you drag and drop the NameState 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. nav-custlist4.png
    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 NameState 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.



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.


Was this article helpful?
0 out of 0 found this helpful