Skip to main content

Grouping and subtotal functions in nested tables

Zuora

Grouping and subtotal functions in nested tables

  1. Last updated
  2. Save as PDF

You can configure your invoice templates to display usage charges and invoice items in nested tables. Use nested tables to group and subtotal complex usage charges and invoice items by any supported merge field or custom field. Zuora supports multi-level nested tables within the usage charges or invoice items table.

Nested table configuration

To configure nested tables in invoice templates, use the following merge fields:

  • NestedTable:GroupColumn: Groups information by a merge field, such as Charge Date, UOM (unit of measure) or a custom field.
  • NestedTable:ValueColumn: Calculates a subtotal based on a merge field, such as Amount, Quantity, or any other numbered merge field where summing makes sense.
    For example, you can calculate different subtotals based on all of the following merge fields:
    • InvoiceItem.ExtendedPrice
    • InvoiceItem.TaxAmount
    • InvoiceItem.AmountWithoutTax
    • InvoiceItem.Quantity
    {MERGEFIELD "NestedTable:ValueColumn InvoiceItem.Quantity,InvoiceItem.ExtendedPrice,InvoiceItem.TaxAmount,InvoiceItem.AmountWithoutTax" \* MERGEFORMAT}
    Note that you cannot add any spaces between the merge fields.

Outer table and inner table

The nested table in invoices consists of one or more outer tables and an inner table.

For example:

Outermost table: Outer table 1

Outer table 2

Outer table 3

Inner table

Outer table 

Defines the scope of the outer table. You can have an outer table inside another outer table.

The outer table includes:

  • TableStart and TableEnd merge fields.
    • If the outer table is an outermost table (Outer table 1):

      {MERGEFIELD TableStart:<objectName> \* MERGEFORMAT}

    • If the outer table has a parent table (Outer table 2 and Outer table 3):

      {MERGEFIELD TableStart:<objectName>_<xx> \* MERGEFORMAT}

      The object referenced in the TableStart and TableEnd merge fields is the object name referenced in the outer table appended with_xx, where xx represents any non-empty alphanumeric string. Once the outer table object is defined, reference it consistently when inserting other merge fields in the outer table. For example, follow a sequential numbering convention for the nested table and use 01:InvoiceItem_01.ListPrice.

  • NestedTable:GroupColumn merge field used to group the data.
  • NestedTable:ValueColumn merge field used to calculate the subtotal.
  • Merge field used to display the subtotal of the ValueColumn. 
  • TableSort merge field used to sort order for data.
  • In an outer table, you are not allowed to use the same field for both GroupColumn and ValueColumn merge fields.

When specifying merge fields for nested tables in your template document, use double quotes for merge fields that contain spaces. For example: 
{MERGEFIELD "NestedTable:GroupColumn Usage.BillingCode__c" \* MERGEFORMAT}

Inner table

Defines the line items that appear in the table nested inside the outer table. The inner table includes:

  • TableStart and TableEnd merge fields. The object referenced in the TableStart and TableEnd merge fields is the object name referenced in the outer table appended with_xx, where xx represents any non-empty alphanumeric string. For example: {MERGEFIELD TableStart:<objectName>_<xx> \* MERGEFORMAT} 

    Once the inner table object is defined, reference it consistently when inserting other merge fields in the inner table. For example, follow a sequential numbering convention for the nested table and use 01: InvoiceItem_01.ListPrice or Usage_01.Quantity.

  • You cannot include the GroupColumn merge field and subtotal amount of the ValueColumn merge field in the inner table scope.
  • TableSort merge field used to sort order for data.

Prerequisites

Review the following prerequisites to ensure you are ready to test your nested table configuration.

  1. Copy of the Invoice Template. Click your username at the top right and navigate to Billing > Manage Billing Document Configuration to download a sample invoice template for new service.
  2. Edit the invoice template you downloaded. The default template does not include NestedTable merge fields. You need to insert them into your invoice template. You can also customize the template based on the sample nested table templates.

    For more information on working with invoice templates and merge fields, see:

Prerequisites for usage data

You need processed usage data to display subtotals based on the charge rating amount. The following are required to populate the Amount field:

Sample nested table templates

Download the following sample templates, which you can use to customize your nested table.

See Examples: Using Nested Tables in Invoices for detailed explanations about the sample templates.

Group usage or invoice item summary and subtotals

You can configure your table to display only a summary of grouped usage charges or invoice items with totals. An inner table is not required to configure a summary table.

In the following example, the generated invoice displays a summary of charges grouped by billing code and totals of UOM quantities:

Example Invoice Displaying Only a Summary of Grouped Usage Data with Subtotals

Modify the original Usage Details table to look like the following table:

[ROW 1] Usage Details
[ROW 2] Billing Code Amount
[ROW 3:
Outer Table]

«TableStart:Usage» «NestedTable:GroupColumn Usage.BillingCode__c» «NestedTable:ValueColumn Usage.Quantity» BillingCode: «Usage.BillingCode__c»

Totals: «Usage.Quantity»«TableEnd:Usage»

Notes and limitations

  • When the TableFilter:GroupBy merge field is used for grouping invoice items, the invoice item custom fields are populated from the corresponding subscription rate plan charge custom fields. The first invoice item custom field you created corresponds to the first subscription rate plan charge custom field you created, and so on; the names of custom fields are not relevant.  Therefore, you must have created invoice item custom fields in exactly the same order as the corresponding subscription rate plan charge custom fields. Additionally, if a subscription rate plan charge custom field is non-indexed, the corresponding invoice item custom field must be also non-indexed.
  • Using the TableFilter:GroupBy merge field for the invoice items, credit memo items, and debit memo items in the nested tables is not supported. 
  • Using the TableFilter:GroupBy merge field for the credit memo items and debit memo items in regular tables are not supported. Therefore, it is recommended to use nested tables for credit memo items and debit memo items. 
  • Only the following fields are currently supported with nested tables:
    • Usage merge fields and custom merge fields
    • Invoice item merge fields and custom merge fields
    • Tax item merge fields
    • Credit memo item merge fields 
    • Debit memo item merge fields