What kind of column in table can be used as "group by" for reporting?

[Lisa71]

What kind of column in table can be used as "group by" for reporting? 

1. Some columns can be used as "group by" even it's string type, however some other string type column can NOT be selected in "group by" column in report, why? 

2. Sometimes we found in table list view, we can't group by using one column, however in report view, we can group by it. Or sometimes we can group by a column in report view, but can't in list view, why? 

[Amitoj Wadhera · Accepted solution]

Hi @Lisa71 ,

In ServiceNow, not all columns are eligible for "group by" operations in reports or list views. The ability to use a column for "group by" operations depends on several factors related to the column's configuration and data structure.

### 1. Some columns can be used as "group by" even if they are string type, but some other string type columns cannot be selected in "group by" column in reports, why?

There are a few key reasons why some string columns can be used for grouping while others cannot:

- **Dictionary Attributes:** Columns have attributes defined in the dictionary, such as `glide_list`, `reference`, `choice`, and others, which can affect their eligibility for grouping.
- **Column Type:** While some string columns can be grouped, certain complex column types (e.g., `Journal`, `HTML`, `Currency`, `Composite fields`) are not supported for grouping because they can store data in non-uniform ways or large blobs of data.
- **Column Configuration:** Columns marked as `glide_list`, `reference`, or `choice` can be grouped because they inherently represent a set of discrete values.
- **Internal Implementation:** Some fields might be implemented in a way that doesn't allow efficient grouping. This could be due to indexing or how the data is stored internally.
- **Choice Fields:** String fields configured as `choice` lists (dropdowns with predefined options) are often groupable because they have a fixed set of values.

### 2. Sometimes we can't group by using one column in table list view, but can in report view, or vice versa, why?

This discrepancy can occur due to the following reasons:

- **Different Grouping Implementations:** The grouping mechanism in list views and reports might be implemented differently. List views use server-side processing for grouping data, while reports might use a combination of server-side and client-side processing.
- **Table Configuration:** The configuration of the table and the columns (metadata, attributes) might affect their groupability differently in list views versus reports.
- **List Layout Configuration:** The list layout configuration might have restrictions based on performance considerations or column settings defined in the dictionary.
- **Field Type Limitations:** Some fields may be groupable in reports but not in list views due to performance concerns or implementation details.
- **Indexing and Performance:** Grouping on large text fields can be resource-intensive. ServiceNow might restrict grouping in list views to ensure performance, while reports might have more flexibility due to aggregation performed over a potentially limited dataset.

### Example Scenarios:

#### Scenario 1: Grouping by Choice Field
- **Field Type:** Choice (string)
- **Groupable in Report View:** Yes
- **Groupable in List View:** Yes
- **Reason:** Choice fields have a fixed set of discrete values, making them efficient for grouping.

#### Scenario 2: Grouping by Reference Field
- **Field Type:** Reference (string)
- **Groupable in Report View:** Yes
- **Groupable in List View:** Yes
- **Reason:** Reference fields link to another table, providing a discrete set of values for grouping.

#### Scenario 3: Grouping by Journal Field
- **Field Type:** Journal (string)
- **Groupable in Report View:** No
- **Groupable in List View:** No
- **Reason:** Journal fields store large, free-form text entries, which are not suitable for efficient grouping.

#### Scenario 4: Grouping by Regular String Field
- **Field Type:** String
- **Groupable in Report View:** Maybe
- **Groupable in List View:** Maybe
- **Reason:** Depending on internal implementation, size, and data uniformity, regular string fields might or might not be groupable.

### Tips for Ensuring Fields are Groupable:

1. **Check Field Type:** Ensure the field type is suitable for grouping (e.g., choice, reference).
2. **Configure Dictionary Attributes:** Adjust dictionary attributes if necessary to make fields more suitable for grouping.
3. **Use Appropriate Indexing:** Ensure fields likely to be used for grouping are indexed to improve performance.
4. **Test Grouping in Both Views:** Test the grouping functionality in both list and report views to identify and resolve any discrepancies.

Understanding the underlying reasons for these behaviors helps in configuring your tables and fields in a way that maximizes their utility for reporting and list views.

If you find my response helpful, please consider marking it as the 'Accepted Solution' and giving it a 'Helpful' rating. Your feedback not only supports the community but also encourages me to continue providing valuable assistance.

Thanks,

Amitoj Wadhera