Skip to main content
insightsoftware Docs insightsoftware Docs
{%article.title%}
Published:
Was this article helpful?
0 out of 0 found this helpful

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.

Multi-Level Grouping (Dynamics NAV Example)

In the first grouping tutorial, we left room for future expansion of our outermost region.  In your reports, you may need to adjust the location of the subtotaling and grand total functions to compensate for any changes in the size of your regions.

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.

Published:

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.

Multi-Level Grouping (Dynamics NAV Example)

In the first grouping tutorial, we left room for future expansion of our outermost region.  In your reports, you may need to adjust the location of the subtotaling and grand total functions to compensate for any changes in the size of your regions.

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.

For an optimal Community experience, Please view on Desktop