Table of Contents

Document Report Data Grouping

Report Data grouping is particularly valuable for business documents like invoices, where sales lines may need to be grouped and aggregated for simplified presentation to external parties. For example, while your internal cost accounting might require detailed line breakdowns, your customers may prefer a consolidated view. The data grouping functionality enables users to configure these grouping rules directly in Business Central without requiring modifications to the underlying report layouts. This is especially beneficial when working with Word report layouts, which inherently lack built-in data grouping and aggregation capabilities. By implementing grouping at the application level, users can maintain detailed internal data structures while presenting simplified, organized information on external documents, all while preserving the simplicity of their report layouts.

Understanding Data Grouping in Business Central

The Data Item Grouping functionality in Business Central provides a powerful way to organize, group, and transform report data without modifying report layouts. This system operates at the application level, processing data before it reaches the report rendering engine.

Note

The data grouping functionality is supported for the following report data items:

  • Sales Invoice lines
  • Cost/Revenue lines (FSM)

Key benefits of the Data Item Grouping system include:

  • Flexible Grouping: Group data based on specific fields, combinations, or conditions
  • Data Aggregation: Apply functions like Sum, Count, Fixed or Blank to grouped data
  • No Layout Modifications Required: Apply grouping logic without changing report layouts
  • User-Configurable: Configure grouping rules through Business Central's user interface

Key Components of Data Item Grouping

The Data Item Grouping system consists of several key components:

1. Report Data Item Grouping

This is the primary configuration object that defines a grouping structure. Each grouping definition includes:

  • Code: A unique identifier for the grouping configuration
  • Description: A descriptive name for the grouping
  • Table No.: The primary table the grouping applies to
  • Data Item: The name of the data item in the report dataset

2. Report Data Item Grouping Detail

This component defines the specific grouping operations to perform. Each detail line can be one of two types:

  • Group: Aggregates data using defined combinations of fields and actions
  • Per Line: Processes individual records based on filters or conditions

Each detail line includes:

  • Execution Sequence: The order in which grouping operations are performed
  • Output Sequence: The order in which grouped data appears in the output
  • Table Filter: Optional filters to limit which records are processed

3. Report Data Combination

This defines how fields from the data source are combined and transformed during grouping. Each combination includes:

  • Code: A unique identifier for the combination
  • Description: A descriptive name for the combination
  • Table No.: The table this combination applies to
  • Table Filter: Optional filters to refine the data being combined
  • Field No. Description: The field used to generate descriptive text for the grouping

4. Report Data Combination Field

This defines the specific fields and actions to apply during grouping. Each field definition includes:

  • Field No.: The field from the source table to process
  • Field Action: The operation to perform on the field data
  • Value: An optional fixed value for certain actions

Field Actions for Data Grouping

The system supports the following field actions:

  • Sum: Adds up numeric values within a group
  • Count: Counts the number of records in a group
  • Fixed: Sets a fixed value for the field regardless of source data
  • Blank: Sets the field to a blank value
  • Split: Creates separate groupings based on unique values in the field
  • Line Value: Uses the original value from the line

Setting Up Data Item Grouping

To create a new Data Item Grouping:

  1. Choose the Lightbulb that opens the Tell Me feature icon, enter Report Data Item Grouping List, and then choose the related link.
  2. On the Report Data Item Grouping List page, choose the New action.
  3. Fill in the following fields:
    • Code: Enter a unique code for this grouping configuration.
    • Description: Enter a descriptive name.
    • Table No.: Select the primary table this grouping applies to.
    • Data Item: Enter the name of the data item in the report dataset.
  4. Choose the Details action to configure the grouping detail lines.

To configure Data Item Grouping Details:

  1. On the Report Data Item Grouping Details page, choose the New action to add a detail line.
  2. Set the Type field to either:
    • Group: To create an aggregation group
    • Per Line: To process records individually
  3. For Group type lines, fill in the Combination Code field, or create a new data combination.
  4. Set the Execution Sequence to determine the order of processing.
  5. Add additional detail lines as needed to build your grouping structure.

To create a new Data Combination:

  1. Choose the Lightbulb that opens the Tell Me feature icon, enter Report Data Combinations, and then choose the related link.
  2. On the Report Data Combinations page, choose the New action.
  3. Fill in the following fields:
    • Code: Enter a unique code for this combination.
    • Description: Enter a descriptive name.
    • Table No.: Select the table this combination applies to.
    • Field No. Description: Select the field to use for generating descriptions.
  4. Choose the Fields action to configure which fields to include and how to process them.

To configure Data Combination Fields:

  1. On the Report Data Combination Fields page, choose the New action to add a field.
  2. Select the Field No. you want to include in the combination.
  3. Set the Field Action to one of the available actions (Sum, Count, Fixed, Blank, Split, or Line Value).
  4. If using the Fixed action, enter the fixed value in the Value field.
  5. Repeat for all fields you want to include in the combination.

Examples of Data Item Grouping

Example 1: Sales Summary Report with Customer Grouping

In this example, we'll create a grouping that summarizes sales by customer:

  1. Create a Data Item Grouping with:

    • Code: SALES-BY-CUST
    • Table No.: Sales Line
    • Data Item: SalesLine

  2. Add a Group detail line with:

    • Type: Group
    • Combination Code: CUST-SALES-SUM
    • Execution Sequence: 10

  3. Create a Data Combination with:

    • Code: CUST-SALES-SUM
    • Table No.: Sales Line
    • Table Filter: Type=Item

  4. Configure the Combination Fields:

    • Field: Amount, Action: Sum
    • Field: Quantity, Action: Fixed, Value: "1"

This grouping will produce a report showing total sales amount for each customer.

Combining Group and Per Line Processing

For mixed reports that show both summary and detail information:

  1. Add Group detail lines for summary sections
  2. Add Per Line detail lines for detailed sections
  3. Set execution sequences to control the order of data

Using Table Filters

Apply table filters to limit which records are included in groupings:

  1. Set filters directly on the Data Item Grouping Detail
  2. Use standard Business Central filter syntax (e.g., Type=Item|Resource)
  3. Create multiple detail lines with different filters for complex scenarios

Troubleshooting Data Grouping Issues

If you encounter issues with your data grouping configurations, check the following:

  1. Execution Sequence: Ensure all detail lines have a proper execution sequence.
  2. Field Actions: Verify field actions are appropriate for the field types (e.g., Sum only works on numeric fields).
  3. Table Filters: Check that table filters are correctly formatted and not excluding necessary records.

Best Practices for Data Grouping

  • Start Simple: Begin with basic groupings before creating complex multi-level structures
  • Use Meaningful Codes: Choose descriptive codes for groupings and combinations
  • Document Your Groupings: Add clear descriptions to groupings and combinations
  • Test Thoroughly: Test groupings with various data scenarios
  • Consider Performance: Complex groupings may impact report generation time
  • Reuse Combinations: Create generic combinations that can be reused across multiple reports
  • Maintain Field Actions: Regularly review and update field actions as business needs change

For more information about working with data in report layouts, see Work with Word Layouts and Managing Report Layouts.

Edit this page