以下是引用狐狸爸爸在2011-9-27 14:58:00的发言：
第三个问题，合并欠款金额：
Dim Filter As String '设置过滤条件查询
Filter="1=1"
With e.Form.Controls("开始日期")
If .Value IsNot Nothing Then
Filter = Filter & "and 送货日期>= '" & .Value & "'"
End If
End With
With e.Form.Controls("结束日期")
If .Value IsNot Nothing Then
Filter = Filter & "and 送货日期<= '" & .Value & "'"
' Else
End If
End With
Dim b As New GroupTableBuilder("统计表1",DataTables("收款明细"))
b.Groups.AddDef("客户名称") '根据产品分组
'b.Totals.AddDef("期初欠款") '对数量进行统计
b.Totals.AddDef("销售总金额","本月销售额") '对数量进行统计
b.Totals.AddDef("已收款","本月回款额") '对数量进行统计
If Filter>"" Then
b.filter=Filter '进行数据过滤
End If
b.Build
DataTables("统计表1").DataCols.Add("欠款金额",Gettype(Double))
For Each dr As DataRow In DataTables("统计表1").DataRows
Dim flt As String = "客户名称 = '" & dr("客户名称") & "'"
Dim v1 As Double = DataTables("收款明细").Compute("Sum(期初欠款)",flt)
Dim v2 As Double = DataTables("收款明细").Compute("Sum(销售总金额)",flt)
Dim v3 As Double = DataTables("收款明细").Compute("Sum(已收款)",flt)
dr("欠款金额") = v1 + v2 - v3
Next
MainTable = Tables("统计表1")
Forms("返回2").OpenTo("统计表1")
MainTable.Focus()
[此贴子已经被作者于2011-9-27 15:07:46编辑过]
这个已做出来了，只是我的方法显得笨了些。
Dim Filter As String '设置过滤条件查询
Filter="1=1"
With e.Form.Controls("开始日期")
If .Value IsNot Nothing Then
Filter = Filter & "and 送货日期>= '" & .Value & "'"
End If
End With
With e.Form.Controls("结束日期")
If .Value IsNot Nothing Then
Filter = Filter & "and 送货日期<= '" & .Value & "'"
' Else
End If
End With
'Dim sql As String = "Select 客户名称,sum(销售总金额) as 本月销售额 ,sum(已收款) as 本月回款额,sum(期初欠款) as 期初欠款 ,sum(销售总金额)+sum(期初欠款)-sum(已收款)as 欠款额 FROM {收款明细} group by {收款明细}.客户名称 having 送货日期>='" & StartDate & "' and 送货日期<='" & EndDate & "'"
'Dim b As New GroupTableBuilder("统计表1",sql)
'b.Groups.AddDef("客户名称") '根据产品分组
'b.Totals.AddDef("本月销售额") '对数量进行统计
'b.Totals.AddDef("本月回款额") '对数量进行统计
'b.Totals.AddDef("欠款额") '对数量进行统计
'b.Build '生成统计表
'MainTable = Tables("统计表1") '打开生成的统计表
Dim b As New GroupTableBuilder("统计表1",DataTables("收款明细"))
Dim dt1 As fxDataSource
b.Groups.AddDef("客户名称") '根据产品分组
'b.Totals.AddDef("期初欠款") '对数量进行统计
b.Totals.AddDef("销售总金额","本月销售额") '对数量进行统计
b.Totals.AddDef("已收款","本月回款额") '对数量进行统计
If Filter>"" Then
b.filter=Filter '进行数据过滤
End If
dt1 = b.BuildDataSource()
Dim sql As String = "Select 客户名称,sum(IIF(ISNULL(销售总金额),0,销售总金额)) +sum(IIF(ISNULL(期初欠款),0,期初欠款)) -sum(IIF(ISNULL(已收款),0,已收款)) As 目前欠款额 FROM {收款明细} group by {收款明细}.客户名称 having 客户名称 Is not null order by 客户名称"
Dim g As New GroupTableBuilder("统计表1",sql)
Dim dt2 As fxDataSource
g.Groups.AddDef("客户名称") '根据产品分组
'b.Totals.AddDef("本月销售额") '对数量进行统计
'b.Totals.AddDef("本月回款额") '对数量进行统计
g.Totals.AddDef("目前欠款额") '对数量进行统计
dt2 = g.BuildDataSource()
dt1.Combine("客户名称",dt2,"客户名称") '将欠款额统计数据组合到收款统计数据
Tables("资金报表_Table1").DataSource = dt1 '将统计结果绑定到Table
With Tables("资金报表_Table1")
Dim gp As New Subtotalgroup
gp.Aggregate = AggregateEnum.Sum
gp.GroupOn = "*" '注意总计分组用符号*表示.
For Each c As col In .cols
If c.DataCol.IsNumeric Then
If gp.TotalOn > "" Then
gp.TotalOn = gp.TotalOn & ","
End If
gp.TotalOn = gp.TotalOn & c.name
End If
Next
gp.Caption = "总计"
.SubtotalGroups.Add(gp)
.Subtotal() '生成汇总模式
End With
For Each dc As DataCol In DataTables("资金报表_Table1").DataCols '将空值显示为0
If dc.IsNumeric Then
For Each dr As DataRow In DataTables("资金报表_Table1").DataRows
If dr.IsNull(dc.name) Then
dr(dc.name) = 0
End If
Next
End If
Next
DataTables("资金报表_Table1").DataCols("本月销售额").SetFormat("c")
DataTables("资金报表_Table1").DataCols("本月回款额").SetFormat("c")
DataTables("资金报表_Table1").DataCols("目前欠款额").SetFormat("c")
加好友 
发短信
Post By：2011/9/27 12:56:00 [只看该作者]
