以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  统计问题  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=192219)

--  作者:wlj88868
--  发布时间:2024/6/3 19:11:00
--  统计问题

Dim bd1 As New SqlGroupTableBuilder("统计表1", "出库明细")
Dim dt1 As fxDataSource
bd1.Groups.AddDef("品牌")\'根据型号分组
\'bd1.Groups.AddDef("出库商场")\'根据型号分组
\'bd1.Groups.AddDef("单价")\'根据型号分组
bd1.Groups.AddDef("备注")\'根据型号分组
bd1.Totals.AddDef("数量", "入库_数量")\'对数量进行统计
bd1.Totals.AddExp("金额", "数量*单价", "入库_金额")\'对金额进行统计
bd1.Filter = Filter
bd1.SubtotalLevel = 1
dt1 = bd1.BuildDataSource()

Dim bd2 As New SqlGroupTableBuilder("统计表2", "退货明细")
Dim dt2 As fxDataSource
bd2.Groups.AddDef("品牌")\'根据型号分组
\'bd2.Groups.AddDef("退货商场")\'根据型号分组
bd2.Totals.AddDef("数量", "退货_数量")\'对数量进行统计
bd2.Totals.AddExp("金额", "数量*单价", "退货_金额")\'对金额进行统计
bd2.Filter = Filter1
dt2 = bd2.BuildDataSource()

Dim bd3 As New SqlGroupTableBuilder("统计表3", "销售资料")
Dim dt3 As fxDataSource
bd3.Groups.AddDef("品牌")\'根据型号分组
\'bd3.Groups.AddDef("销售商场")\'根据型号分组
bd3.Totals.AddDef("数量", "销售_数量")\'对数量进行统计
bd3.Totals.AddDef("吊牌单价", "销售_应售金额")\'对数量进行统计
bd3.Totals.AddDef("销售单价", "销售_实售金额")\'对金额进行统计
bd3.Filter = Filter3
dt3 = bd3.BuildDataSource()

Dim bd4 As New SqlGroupTableBuilder("统计表4", "出库明细")
Dim dt4 As fxDataSource
bd4.Groups.AddDef("品牌")\'根据型号分组
bd4.Totals.AddDef("数量", "出库期初_数量")\'对数量进行统计
bd4.Totals.AddExp("金额", "数量*单价", "出库期初_金额")\'对金额进行统计
bd4.Filter = Filter4
dt4 = bd4.BuildDataSource()

Dim bd5 As New SqlGroupTableBuilder("统计表5", "退货明细")
Dim dt5 As fxDataSource
bd5.Groups.AddDef("品牌")\'根据型号分组
bd5.Totals.AddDef("数量", "退货期初_数量")\'对数量进行统计
bd5.Totals.AddExp("金额", "数量*单价", "退货期初_金额")\'对金额进行统计
bd5.Filter = Filter5
dt5 = bd5.BuildDataSource()

Dim bd6 As New SqlGroupTableBuilder("统计表6", "销售资料")
Dim dt6 As fxDataSource
bd6.Groups.AddDef("品牌")\'根据型号分组
bd6.Totals.AddDef("数量", "销售期初_数量")\'对数量进行统计
bd6.Totals.AddDef("吊牌单价", "销售期初_应售金额")\'对数量进行统计
bd6.Totals.AddDef("销售单价", "销售期初_实售金额")\'对金额进行统计
bd6.Filter = Filter6
dt6 = bd6.BuildDataSource()

dt1.Combine("品牌", dt2, "品牌")\'将销售统计数据组合到出货统计数据
dt1.Combine("品牌", dt3, "品牌")
dt1.Combine("品牌", dt4, "品牌")
dt1.Combine("品牌", dt5, "品牌")
dt1.Combine("品牌", dt6, "品牌")

Tables("统计查询_Table1").DataSource = dt1 \'将统计结果绑定到Table
With DataTables("统计查询_Table1").DataCols \'用表达式列计算库存数据
    .Add("期初_数量", GetType(Integer), "IsNull([出库期初_数量], 0) - IsNull([销售期初_数量], 0) - IsNull([退货期初_数量], 0)")
    .Add("期初_金额", GetType(Double), "IsNull([出库期初_金额], 0) - IsNull([退货期初_金额], 0) - IsNull([销售期初_应售金额], 0)")
    .Add("库存_数量", GetType(Integer), "IsNull([期初_数量], 0) + IsNull([入库_数量], 0) - IsNull([退货_数量], 0)-IsNull([销售_数量], 0)")
    .Add("库存_金额", GetType(Double), "IsNull([期初_金额], 0) + IsNull([入库_金额], 0) - IsNull([退货_金额], 0) - IsNull([销售_应售金额], 0)")
End With
Dim g As SubtotalGroup \'定义一个新的分组
    g = New SubtotalGroup
    g.GroupOn = "品牌" \'分组列为客户
    g.TotalOn = "期初_数量,期初_金额,入库_数量,入库_金额,退货_数量,退货_金额,销售_数量,销售_应售金额,销售_实售金额,库存_数量,库存_金额" \'对数量和金额进行统计
    g = New SubtotalGroup
    g.GroupOn = "*" \'分组列为客户
    g.TotalOn = "期初_数量,期初_金额,入库_数量,入库_金额,退货_数量,退货_金额,销售_数量,销售_应售金额,销售_实售金额,库存_数量,库存_金额" \'对数量和金额进行统计
With Tables("统计查询_Table1")
     .SubtotalGroups.Clear() \'清除原有的分组
     .SubtotalGroups.Add(g) \'加入刚刚定义的分组
     .Subtotal() \'生成汇总模式
End With

 

 

老师分组统计,表达式金额这样用不可以吗,,,下面统计的时候提示表没有 入库金额 是什么原因啊


--  作者:有点蓝
--  发布时间:2024/6/3 20:48:00
--  

Dim bd1 As New SqlGroupTableBuilder("统计表1", "出库明细")
Dim dt1 As fxDataSource
bd1.Groups.AddDef("品牌")\'根据型号分组
\'bd1.Groups.AddDef("出库商场")\'根据型号分组
\'bd1.Groups.AddDef("单价")\'根据型号分组
bd1.Groups.AddDef("备注")\'根据型号分组
bd1.Totals.AddDef("数量", "入库_数量")\'对数量进行统计
bd1.Totals.AddExp("入库_金额", "数量*单价")\'对金额进行统计
bd1.Filter = Filter
bd1.SubtotalLevel = 1
dt1 = bd1.BuildDataSource()

Dim bd2 As New SqlGroupTableBuilder("统计表2", "退货明细")
Dim dt2 As fxDataSource
bd2.Groups.AddDef("品牌")\'根据型号分组
\'bd2.Groups.AddDef("退货商场")\'根据型号分组
bd2.Totals.AddDef("数量", "退货_数量")\'对数量进行统计
bd2.Totals.AddExp("退货_金额", "数量*单价")\'对金额进行统计
bd2.Filter = Filter1
dt2 = bd2.BuildDataSource()

……后面同样改


--  作者:wlj88868
--  发布时间:2024/6/3 21:49:00
--  
谢谢,Groups.AddDef的时候也这样改过,就AddExp没这样改过,晕
--  作者:有点蓝
--  发布时间:2024/6/3 21:55:00
--  
学会看帮助,前面的是列名,后面是标题

图片点击可在新窗口打开查看此主题相关图片如下:1.png
图片点击可在新窗口打开查看