我用这个方法能实现,比较复杂。
Dim f1 As fxDataSource
Dim f2 As fxDataSource
Dim f3 As fxDataSource
Dim datenow As Date = Functions.Execute("SysTime")
Dim d1 As String = format(e.Form.controls("datetimePicker1").value,"yyyy-MM-dd")
Dim d2 As String = format(e.Form.controls("datetimePicker2").value,"yyyy-MM-dd")
cmd.CommandText = "select 客户简称,总费用 from {托运单据} where 开单日期 >= '"& d1 &"' and 开单日期 <= '"& d2 &"' and 销单否 = '否'"
Dim dt1 As DataTable = cmd.ExecuteReader()
Dim cb1 As new GroupTableBuilder("统计表1",dt1)
cb1.Groups.AddDef("客户简称")
cb1.Totals.Adddef("总费用")
f1 = cb1.BuildDataSource()
cmd.CommandText = "select 客户简称,退货费 from {退货明细} where 入库日期 >= '"& d1 &"' and 入库日期 <= '"& d2 &"'"
Dim dt2 As DataTable = cmd.ExecuteReader()
Dim cb2 As new GroupTableBuilder("统计表2",dt2)
cb2.Groups.AddDef("客户简称")
cb2.Totals.Adddef("退货费")
f2 = cb2.BuildDataSource()
cmd.CommandText = "select 客户简称,提货费 from {提货计划明细} where 提货日期 >= '"& d1 &"' and 提货日期 <= '"& d2 &"'"
Dim dt3 As DataTable = cmd.ExecuteReader()
Dim cb3 As new GroupTableBuilder("统计表3",dt3)
cb3.Groups.AddDef("客户简称")
cb3.Totals.Adddef("提货费")
f3 = cb3.BuildDataSource()
f1.Combine("客户简称",f2,"客户简称")
f1.Combine("客户简称",f3,"客户简称")
Tables("S费用统计_Table2").DataSource = f1
Dim dtb As New DataTableBuilder("统计")
dtb.AddDef("客户简称", Gettype(String), 100)
dtb.AddDef("托运费", Gettype(Double))
dtb.AddDef("退货费", Gettype(Double))
dtb.AddDef("提货费", Gettype(Double))
dtb.AddDef("合计", Gettype(Double))
dtb.Build()
For Each nm As String In DataTables("S费用统计_Table2").GetValues("客户简称")
Dim dr As DataRow = DataTables("统计").AddNew()
dr("客户简称") = nm
Dim s1,s2,s3 As Double
s1 = DataTables("S费用统计_Table2").Compute("Sum(总费用)","[客户简称] = '" & dr("客户简称") & "'")
s2 = DataTables("S费用统计_Table2").Compute("Sum(退货费)","[客户简称] = '" & dr("客户简称") & "'")
s3 = DataTables("S费用统计_Table2").Compute("Sum(提货费)","[客户简称] = '" & dr("客户简称") & "'")
dr("托运费") = s1
dr("退货费") = s2
dr("提货费") = s3
dr("合计") = dr("托运费") + dr("退货费") + dr("提货费")
Next
Tables("S费用统计_Table1").DataSource = DataTables("统计")