参考
Dim filter1 As String
filter1="日期 >= #" & e.Form.Controls("startdate").value & "# " & " And 日期 <= #" & e.Form.Controls("enddate").value & "#"
Dim bd1 As New SQLGroupTableBuilder("统计表1","进货单明细")
Dim b As New SQLGroupTableBuilder("统计表1","订单")
bd1.ConnectionName = "wjhansdata"
Dim dt1 As fxDataSource
bd1.Groups.AddDef("产品名称") '根据型号分组
bd1.Groups.AddDef("仓库") '根据型号分组
bd1.Totals.AddDef("数量","进货_数量") '对数量进行统计
bd1.Totals.AddDef("金额","进货_金额") '对金额进行统计
bd1.Filter = filter1
dt1 = bd1.BuildDataSource()
Dim bd2 As New SqlGroupTableBuilder("统计表2","销售单明细")
bd2.ConnectionName = "wjhansdata"
Dim dt2 As fxDataSource
bd2.Groups.AddDef("产品名称") '根据型号分组
bd2.Groups.AddDef("仓库") '根据型号分组
bd2.Totals.AddDef("数量","销售_数量") '对数量进行统计
bd2.Totals.AddDef("金额","销售_金额") '对金额进行统计
bd2.Totals.AddDef("成本","销售_成本") '对金额进行统计
bd2.Filter = filter1
dt2 = bd2.BuildDataSource()
Dim bd5 As New GroupTableBuilder("统计表5",DataTables("期初库存"))
Dim dt5 As fxDataSource
bd5.Groups.AddDef("产品名称") '根据型号分组
bd5.Groups.AddDef("仓库") '根据型号分组
bd5.Totals.AddDef("期初数量","期初_数量") '对数量进行统计
bd5.Totals.AddDef("期初金额","期初_金额") '对金额进行统计
dt5 = bd5.BuildDataSource()
Dim filter2="日期 < #" & e.Form.Controls("startdate").value & "#"
Dim bd10 As New SQLGroupTableBuilder("统计表1","进货单明细")
bd10.ConnectionName = "wjhansdata"
Dim dt10 As fxDataSource
bd10.Groups.AddDef("产品名称") '根据型号分组
bd10.Groups.AddDef("仓库") '根据型号分组
bd10.Totals.AddDef("数量","进货_之前数量") '对数量进行统计
bd10.Totals.AddDef("金额","进货_之前金额") '对金额进行统计
bd10.Filter = filter2
dt10 = bd10.BuildDataSource()
Dim bd11 As New SqlGroupTableBuilder("统计表11","销售单明细")
bd11.ConnectionName = "wjhansdata"
Dim dt11 As fxDataSource
bd11.Groups.AddDef("产品名称") '根据型号分组
bd11.Groups.AddDef("仓库") '根据型号分组
bd11.Totals.AddDef("数量","销售_之前数量") '对数量进行统计
bd11.Totals.AddDef("金额","销售_之前金额") '对金额进行统计
bd11.Totals.AddDef("成本","销售_之前成本") '对金额进行统计
bd11.Filter = filter2
dt11 = bd11.BuildDataSource()
Dim nms As String() = {"产品名称","仓库"} '指定连接列
dt5.Combine(nms,dt1,nms) '将销售统计数据组合到进货统计数据
dt5.Combine(nms,dt2,nms) '将退货统计数据组合到进货统计数据
dt5.Combine(nms,dt10,nms) '将退货统计数据组合到进货统计数据
dt5.Combine(nms,dt11,nms) '将退货统计数据组合到进货统计数据
Tables("区间进销存_Table1").DataSource = dt5 '将统计结果绑定到Table
Dim dt As DataTable = DataTables("区间进销存_Table1")
For Each dr As DataRow In dt.DataRows
dr("期初_数量") += dr("进货_之前数量") - dr("销售_之前数量")
dr("期初_金额") += dr("进货_之前金额") - dr("销售_之前金额")
Next
With DataTables("区间进销存_Table1").DataCols '用表达式列计算库存数据
.Add("库存_数量",Gettype(Integer), "IsNull([期初_数量],0)+ IsNull([进货_数量],0) - ISNULL([销售_数量],0) ")
.Add("库存_金额",Gettype(Double), "IsNull([期初_金额],0)+ IsNull([进货_金额],0) - ISNULL([销售_成本],0) ")
End With
'
Tables("区间进销存_table1").Cols("期初_数量").GrandTotal = True '指定要合计的列
Tables("区间进销存_table1").Cols("期初_金额").GrandTotal = True
Tables("区间进销存_table1").Cols("进货_数量").GrandTotal = True
Tables("区间进销存_table1").Cols("进货_金额").GrandTotal = True
Tables("区间进销存_table1").Cols("销售_数量").GrandTotal = True
Tables("区间进销存_table1").Cols("销售_金额").GrandTotal = True
Tables("区间进销存_table1").Cols("销售_成本").GrandTotal = True
Tables("区间进销存_table1").Cols("库存_数量").GrandTotal = True
Tables("区间进销存_table1").Cols("库存_金额").GrandTotal = True
Tables("区间进销存_table1").GrandTotal = True '显示合计模式
[此贴子已经被作者于2016/6/8 16:36:24编辑过]
加好友 
发短信
Post By：2016/6/8 10:42:00 [显示全部帖子]
