Column Groupings

One of the fundamental operations in building reports is aggregating or grouping rows from your data. This is done to perform calculations of the data under a group, such as determining the mean or sum of values in that category.

ItemPath’s reporting engine does this in two ways:

1. Column Grouping: Specify up to three columns for a single grouping operation. The resulting report will consist of a row for each unique value (or combination of values) from the columns. ItemPath will default to showing the first value found for each column in the report, but you can control which aggregate functions will occur when forming the group such as means or sums, etc.
2. Snapshots: Think of a snapshot as a picture of your report at a moment in time. When building that picture, you can instruct the snapshot to calculate a value for groups or buckets using individual columns from your report.

See Snapshots Overview and Creating Snapshots for a discussion of how to use the Snapshots feature to generate summary or aggregate statics. This article focuses on the Column Grouping function within the Reports interface.

You can set column groupings in the Columns tab of the Reports interface. You can specify up to three layers of grouping, starting from the primary group column, before specifying a sub group column, and a final group column.

Single column groupings

When you introduce a single column grouping to a report, ItemPath will generate a report that consists of a row for each unique value under that column. For example, if you have a report built with the Orders theme, and introduce a column grouping over the Warehouse column, you will have a row for each warehouse associated with an order in Power Pick. ItemPath filters data before applying column groupings, except for filtering on Counts. Keep this in mind when building reports!

Note: Groupings will only contain a value if it occurs in your data. For example, if you have connected a Warehouse-003 to your system, but it has received no orders, it won’t appear in a report that groups based on the Warehouse column from the Orders theme.

Add a column group

To add a grouping, select the column using the dropdown and Save.

Note: A column must be in the report before it’s available for grouping.

Remove a column group

To remove a column group, hover over the dropdown menu for the column group. It will transform to an X icon, which you can use to clear the dropdown. Save to remove the column group from your report.

Note: If you remove a primary column grouping, this will remove other column groupings.

Count columns

One of the primary reasons to group columns is to determine the number of records within the group. The Count column type allows you to review the number of rows from the base table within a group.

To add a Count:

1. Specify your primary group column, and Save.
2. Select Count as the column type.
3. Enter a label for your Count.
4. Add the Count column to your report.

If you have multiple group columns in your report (like Warehouse as the primary group, then Direction Type as a subgroup), you can add multiple count columns. However, these will return the same value because ItemPath performs column groupings as a single step.

Grouping by date range

If you specify a column grouping with a date and time value, you can select which units should be used for the grouping. This will appear as an additional dropdown menu in the interface. Only one unit can be selected, which will be applied to all date and time fields.

Aggregate functions

Aggregate functions allow you to summarize data within your ItemPath reports, providing calculations over a group of rows. Unlike comparison columns, which operate on a row-by-row basis, grouping functions operate on sets of rows that share a common value. This enables you to calculate totals, averages, and other summary statistics across categories within your data.

By default, columns will return the value from the first row that occurs under the grouping (the “First” function).

To use grouping functions in your report:

1. Specify your primary group column and Save.
2. Identify the column you would like to perform summaries or aggregate functions over.
3. Modify the column:
   1. Use the pencil icon to edit the column, then use the dropdown menu to select from a list of available aggregate functions.
   2. Update the name of your column to reflect the calculation.
   3. Save your changes with the check mark.
4. View the calculations in the Data tab.

Available functions

ItemPath provides several standard aggregate functions:

Example: Mean deviated quantity by direction

Here’s a common scenario for using a single column group:

BuildCorp LLC’s process analysts are curious about the year-to-date mean deviated quantity per transaction (History theme), broken down by direction. This will give them greater insight into which processes to focus attention for optimization.

Here’s how they can create this report:

1. Create a new report using the History theme, titled “Mean Deviated Quantity Breakdown”.
2. Add the following columns:
   1. Material ID
   2. Direction Type (from the HistoryMasterOrder table)
   3. Deviated Quantity
   4. Creation Date
3. Add a filter on Creation Date for “during” this year.
4. Add a column grouping by Material ID.
5. Modify the Deviated Quantity column:
   1. Update the name to “Mean Deviated Quantity”.
   2. Change the aggregation function to “Mean”.
   3. Save.

When BuildCorp next loads the report, it will look like this:

Multiple groupings

So far, this article has discussed single column groups. Multiple column groupings are a way to generate a report that consists of every possible combination of values from rows under the columns specified in the column grouping interface. This is a way to show more detail from your report when you specify a grouping.

For example, if you want to see a breakdown of orders by warehouse and material name, then you could build a column grouping with both of those columns. The resulting report would have a row for every possible combination from your data. You could count the number of orders that meet some filter criteria (such as having a deviated quantity), and use those for aggregate statistics (such as the median deviated quantity for combinations of warehouse and material).

All grouping occurs before any aggregate functions or counts, in a single pass. If you were to add a count column to your report the count would be the number of rows grouped under each combination of values. If you changed the order of the groupings, the count would not change.

Note: This means that there is no practical difference between primary, sub, or final groupings. Feel free to enter complex groupings in whichever order you want. If you are familiar with grouping operations in SQL or similar environments, ItemPath performs a composite grouping, rather than a multiple level grouping.

You can perform one additional level of summary or aggregate functions through snapshots.

Example: Allocated quantity totals by type

When you specify a single column grouping, you instruct ItemPath to build a table with a row for each unique value from that column. When you build a complex grouping, ItemPath will build a table of all possible pairs or triples using your selection. A complex grouping will typically increase the number of rows from a single or double column grouping.

Consider this example of a report showing orders:

If you group by Warehouse, the resulting table would have 2 rows. If you group by Direction Type Description, the table would have 3 (Pick, Put, and Count). Suppose you want to see a breakdown of the number of orders by “Warehouse” and “Direction Type Description”. If you build the following report:

1. Select “Warehouse” for the primary column grouping.
2. Select “Direction Type Description” for the subgroup.
3. Save.
4. Add a Count column (but don’t change the function for Allocated Quantity).

Rows in the resulting table will be every unique combination of warehouses and direction type descriptions. From the above table, you will get the following report:

This grouping generates a row for each possible pair of values and the count corresponds to the number of rows that share that pair of values. Remember, ItemPath defaults to selecting the first value it finds under a grouping. If the analyst working with this report wanted, they could drop the Order ID column — after the grouping operations, it doesn’t add helpful information, and isn't useful for means, sums, or other aggregate functions.

Aggregate functions will always roll up data from the base table to the rows generated by performing any of your grouping examples. ItemPath doesn’t currently give you the ability to define the scope of a count or sum function.

Note: If you need to perform operations on the results of counting or summing values (such as average count of order lines per order per warehouse) Snapshots let you perform additional operations using values from your report.

If you were to modify the Allocated Quantity column from the previous example to the sum of allocated quantities in each group. The row for pick orders from Warehouse 2 would change to the sum of the two rows from the original table: