DataSet Column Totals

[Roey]

If I have a dataset with say three rows, is it possible to do some sort of group by or sum to manipulate the data into one row with a total for a particular column eg

 

Part 1 - Qty 4

Part 1 - Qty 5

Part 1 - Qty 3

 

into

 

Part 1 - Qty 12

 

 

Thanks

[mocella]

Yes - use a DataRelation to do this.

[Roey]

Yes - use a DataRelation to do this.

 

Could you give me an example of this or point me in the right direction as I was under the impression that DataRelations were just used for creating relationships between tables in a dataset, similar to reationships within a relational database.

 

Thanks

[mocella]

What you'd do is create and add another table to your dataset that has the same layout as your existing table (use Table.Clone() for this). Call it parentTable for my example.

 

One you do that, you need to provide your "Distinct" keyed rows in the new table - you can write your own Select Distinct function for ADO.Net or check out MSDN.com for the sample there.

 

Now that you have your "parent" rows, you simple add the datarelation to the dataset and finally set your column expression for the Quantity column. Call it myRelation in my example.

 

Dim myRelation As New DataRelation("NewRelation", New DataColumn() {parentTable.PartColumn}, New DataColumn() {existingTable.PartColumn})

ds.Relations.Add(myRelation)
parentTable.PartColumn.Expression = "Sum(Child(NewRelation).Quantity)"