Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet Reports Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Give Feedback

Grouping and Subtotaling Tutorial


Moderate

Related Articles...

How-To Videos

Adding Filters, Lists, and Totals

Adding
Groupings 
Adding Columns
and Dimensions
Adding Sheet
Grouping
Adding
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, Jet Professional 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. 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.


Subtotaling

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 as shown below.

  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
Have more questions? Submit a request

Comments