Styjun

I have a DataTable (CategoryUnsummarised) which has three columns: Category, DataSort and Net.

This is used to drive a report showing totals by category. The reporting application is Devexpress XtraReports. This is within their layout scripting.

This table has several rows of different Net for each Category. The DataSort column is only there to ensure that the category "Other" appears last in the list when sorted. It will only ever be 1 or 2.

An example of the data:

I am trying to create a new table which is a sum of Net grouped by Category and Datasort.

Desired data:

The below is what I have tried, based on other examples on StockOverflow, however it is giving me an error.

 ' Sets columns in CategorySummary table
 CategorySummary = CategoryUnsummarised.clone()


 ' Now summarises the rows. Groups by Category and Datasort, and gives sum total.

 Dim CatGroups = CategoryUnsummarised.AsEnumerable().
 GroupBy(Function(row) New With 
 Key .Category = row.Field(Of String)("Category"),
 Key .DataSort = row.Field(Of integer)("DataSort")
 )

 For Each GroupRow In CatGroups
 CategorySummary.Rows.Add(GroupRow.Key.Category, GroupRow.Sum(Function(row) row.Field(Of decimal)("Net")), GroupRow.Key.DataSort)
 Next

This is the error that I'm getting:

If I declare GroupRow as a variable, it allows the code, however then I get the following:

As far as I can see, the Linq code is valid.

You could use GroupBy method:

Sub Main()
 Dim dt As New DataTable()
 dt.Columns.Add("Category")
 dt.Columns.Add("Net")
 dt.Rows.Add("Hat", 1)
 dt.Rows.Add("Hat", 2)
 dt.Rows.Add("Gloves", 3)
 dt.Rows.Add("Gloves", 4)

 Dim dtSum = dt.AsEnumerable().GroupBy(
 Function(row)
 Return row.Field(Of String)("Category").ToString()
 End Function).ToDictionary(Function(grp)
 Return New KeyValuePair(Of String, Integer)(grp.Key, grp.Sum(Function(row)
 Return Integer.Parse(row("Net").ToString())
 End Function))
 End Function)

End Sub

This should give an idea how to achieve what you want.