Dim Filter As String '设置筛选条件
With e.Form.Controls("DW")
If .Value IsNot Nothing Then
Filter = "{部门档案}.所属单位 = '" & .Value & "'"
End If
End With
Dim b As New SQLcrossTableBuilder("毛利率预算进度分析","部门损益总账")
Dim dt1 As fxDataSource
Dim d As Date = DataTables("部门损益总账").Compute("max(所属日期)")
Dim d1 As Date = new Date(d.year, 1, 1)
Dim d2 As Date = new Date(d.year, 12, 31)
Dim filter1 = "所属日期 >= #" & d1 & "# and 所属日期 <= #" & d2 & "#"
b.AddTable("部门损益总账","部门名称","部门档案","部门名称")
b.AddTable("部门损益总账","科目编号","科目档案","科目编码")
b.hGroups.AddDef("{部门档案}.所属单位")
b.vGroups.AddDef("一级科目")
b.Totals.AddDef("借方发生") '添加数量列用于统计
b.Decimals = 2 '小数点取0位
b.Filter = "(一级科目 = '主营业务收入' or 一级科目 = '主营业务成本') And 零售终端 = true and (" & filter & ") and (" & filter1 & ")"
dt1 = b.BuildDataSource()
b.Build '生成统计表
'If DataTables("年度总体预算").DataCols.Contains("毛利预算") = False Then
'DataTables("年度总体预算").DataCols.Add("毛利预算", Gettype(Double), "[毛利率预算] * [销售预算]")
'End If
Dim bd2 As New SQLGroupTableBuilder("统计表2","年度总体预算")
Dim dt2 As fxDataSource
bd2.AddTable("年度总体预算","部门名称","部门档案","部门名称")
bd2.Groups.AddDef("{部门档案}.所属单位")
bd2.Totals.AddExp("毛利预算","[毛利率预算] * [销售预算]")
'bd2.Totals.AddDef("毛利预算")
bd2.Totals.AddDef("销售预算")
bd2.Filter = "零售终端 = true and (" & filter & ")"
dt2 = bd2.BuildDataSource()
Dim nms As String() = {"所属单位"}
dt1.Combine(nms,dt2,nms)
dt1.Show("毛利率预算进度分析") '显示统计结果
If DataTables("毛利率预算进度分析").DataCols.Contains("综合毛利率") = False Then
DataTables("毛利率预算进度分析").DataCols.Add("综合毛利率", Gettype(Double), "[毛利预算] / [销售预算]")
End If
加好友 
发短信
Post By：2016/1/13 17:04:00 [显示全部帖子]
