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:
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
Based on the unique RegionCodes the datatable can be filtered and the different groups are retrieved:
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:
Or
- Returning the grouped data as a List(Of List(Of DataTable):
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:
Result:
The LINQ Approach:
Ensure that an empty Datatable for the report is setup with the required DataColumns:
- dtReport2
Result:
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
Thanks… looking forward to updates on Alternates.