Rss & SiteMap

Foxtable(狐表) http://www.foxtable.com

新一代数据库软件,完美融合Access、Foxpro、Excel、vb.net之优势,人人都能掌握的快速软件开发工具!
共4 条记录, 每页显示 10 条, 页签: [1]
[浏览完整版]

标题:统计问题

1楼
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

 

 

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

2楼
有点蓝 发表于: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()

……后面同样改

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

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

共4 条记录, 每页显示 10 条, 页签: [1]

Copyright © 2000 - 2018 foxtable.com Tel: 4000-810-820 粤ICP备11091905号

Powered By Dvbbs Version 8.3.0
Processed in .02344 s, 2 queries.