[HowTo] Overview on different options for grouping data and processing the groups

This HowTo introduces on the different options for grouping data from a datatable in order to process the grouped data.

Introduction

Grouping data and processing the grouped data is a common scenario e.g. when the grouped data is to aggregate like summing up, find maximum, get the average or concatening items.

Lets have a look on following data:
grafik

A possible scenario could be:

  • Create a report containing following information:
    • the region code
    • the sum of CaseCount per RegionCode
    • the list of CaseNames per RegionCode

In order to get such a report we can define:

  • Grouping Criteria: The value of the Column RegionCode
  • Aggregation for the CaseCount: Summing up CaseCount values
  • Aggregation for the CaseNames: Concat the CaseName along with a seperating “;”

Grouping the Data

Filter the datatable based on the distinct Group Criteria Values

For getting the distinct RegionCode Values the datatable.DefaultView can help
grafik

Based on the unique RegionCodes the datatable can be filtered and the different groups are retrieved:

grafik
grafik

LINQ - Group By

For retrieving the Grouped data a LINQ can be used:

  • Returning the grouped data as a List(Of List(Of Datarow)) - outer List: Groups, inner List: group members:
    grafik
    grafik

Or

  • Returning the grouped data as a List(Of List(Of DataTable):
    grafik
    grafik

Processing (Aggregation) the grouped Data

Iteration of the groups - Input: DataTable.DefaultView Approach / LINQ List(Of List(Of DataTable)

Let’s select the DataTable.Default Approach and do the group processing:
grafik
grafik
grafik
grafik
Result:
grafik

The LINQ Approach:

Ensure that an empty Datatable for the report is setup with the required DataColumns:
grafik - dtReport2
grafik
grafik
Result:
grafik

Alternates

Descriptions will be included soon

  • the Dictionary Approach
  • Decomposed LINQ
  • Activities from the marketplace

Downloads:

Find sample Project (done with 2020.10.4)
ppr_HowTo_GroupByOverview.zip (36.8 KB)

Starter Help XAML:
ppr_HowTo_GroupBy.xaml (20.7 KB)

Questions

For questions on your retrieval case open a new topic and get individual support

6 Likes

Hi @ppr

Nice :smile: Thanks… looking forward to updates on Alternates.