1、参考：http://www.foxtable.com/webhelp/scr/2305.htm
Dim k As New CrossTableBuilder("租金收入2",DataTables("表A"))
k.HGroups.AddDef("设备名称")
k.VGroups.AddDef("产权单位")
k.Totals.AddDef("当月租金", "租金收入")
k.Totals.AddDef("租金累计", "收入累计")
k.HorizontalTotal = True
k.VerticalTotal = True
Dim dt1 As fxDataSource = k.BuildDataSource()
Dim k2 As New CrossTableBuilder("租金支出",DataTables("表A"))
k2.HGroups.AddDef("设备名称")
k2.VGroups.AddDef("使用单位")
k2.Totals.AddDef("当月租金", "租金支出")
k2.Totals.AddDef("租金累计", "支出累计")
k2.HorizontalTotal = True
k2.VerticalTotal = True
k2.Filter = "使用单位 <> '设备仓库'"
Dim dt2 As fxDataSource = k2.BuildDataSource()
Dim str() As String = {"设备名称","产权单位"}
Dim str2() As String = {"设备名称","使用单位"}
dt1.Combine("设备名称",dt2,"设备名称") '将销售统计数据组合到进货统计数据
dt1.Show("租金收入2")
With DataTables("租金收入2").DataCols '用表达式列计算库存数据
.Add("合计净值",Gettype(Double), "IsNull([合计_租金收入],0) - ISNULL([合计_租金支出],0)")
.Add("累计净值",Gettype(Double), "IsNull([合计_收入累计],0) - ISNULL([合计_支出累计],0)")
End With
2、使用sql生成查询表，在交叉统计
Dim sql = "select 设备名称,单位,sum(当月租金) as 当月租金,sum(租金累计) as 租金累计 from( Select 设备名称,产权单位 as 单位,sum(iif(当月租金 is null,0,当月租金)) As 当月租金,sum(iif(租金累计 is null,0,租金累计)) As 租金累计 from {表A} group by 设备名称,产权单位 union all Select 设备名称,使用单位 As 单位,sum(iif(当月租金 Is null,0,当月租金)) * -1 As 当月租金,sum(iif(租金累计 Is null,0,租金累计)) * -1 As 租金累计 from {表A} where 使用单位 <> '设备仓库' group by 设备名称,使用单位) as a group by 设备名称,单位"
Dim q As new QueryBuilder
q.TableName = "净值"
q.SelectString = sql
q.Build()
MainTable = Tables("净值")
Dim k As New CrossTableBuilder("净值统计",DataTables("净值"))
k.HGroups.AddDef("设备名称")
k.VGroups.AddDef("单位")
k.Totals.AddDef("当月租金", "当月租金")
k.Totals.AddDef("租金累计", "租金累计")
k.HorizontalTotal = True
k.VerticalTotal = True
k.Build()
MainTable = Tables("净值统计")
加好友 
发短信
Post By：2017/11/28 23:05:00 [显示全部帖子]
