专家坐堂-如何转换成负数Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

Dim bd1 As New GroupTableBuilder("统计表5", DataTables("hyjd1"))

bd1.Groups.AddDef("收支时间", DateGroupEnum.Year, "年")

bd1.Groups.AddDef("收支时间", "月")

bd1.Groups.AddDef("收支时间", DateGroupEnum.Day, "日")

bd1.Groups.AddDef("星期")

bd1.Totals.AddDef("收支金额","优惠额度")

bd1.Totals.AddExp("优惠额度", "收支金额* -1")

bd1.Totals.AddDef("张数","折算张数")

Dim dt1 As fxDataSource

dt1 = bd1.BuildDataSource()

dt1.show("统计表c") \'将统计结果绑定到Table 暂时不直接显示

Dim t As Table = Tables("统计表c")

With t.DataTable.DataCols \'用表达式列计算库存数据

.Add("优惠额度",Gettype(Double), "IsNull([优惠额度],0)")

.Add("可减免张数",Gettype(Double), "IIF([星期]=\'星期六\'or [星期]=\'星期天\',65,60)")

.Add("超张数",Gettype(Double), "IIF([折算张数]>[可减免张数],(IsNull([折算张数],0)-IsNull([可减免张数],0)),0)")

.Add("超金额",Gettype(Double), "IsNull([超张数],0)*5")

.Add("实际减免金额",Gettype(Double), "isnull([优惠额度],0) - ISNULL([超金额],0)")

.Add("实际减免张数",Gettype(Double), "IIF([折算张数]>[可减免张数],[可减免张数],IsNull([折算张数],0))")

End With

With Tables("统计表c")

.MergeMode = MergeModeEnum.Standard

.MergeCols.Clear()

.MergeCols.Add("年")

.MergeCols.Add("月")

.MergeCols.Add("星期")

.MergeSort = "年,月,日,星期"

.AllowMerge = True

End With

Dim g As Subtotalgroup

t.SubtotalGroups.Clear()

t.GroupAboveData = False

t.TreeVisible = False

t.SpillNode = True

g = New Subtotalgroup

g.Aggregate = AggregateEnum.Sum

g.GroupOn = "月"

g.TotalOn = "优惠额度,折算张数,超张数,超金额,实际减免金额,实际减免张数"

g.Caption = "{0} 月小计"

t.SubtotalGroups.Add(g)

g = New Subtotalgroup

g.Aggregate = AggregateEnum.Sum

g.GroupOn = "*"

g.TotalOn = "优惠额度,折算张数,超张数,超金额,实际减免金额,实际减免张数"

g.Caption = "总计"

t.SubtotalGroups.Add(g)

t.Subtotal()

MainTable = t

\'以下显示格式

Tables("统计表c").cols("优惠额度").DataCol.setFormat("#,###")

Tables("统计表c").cols("折算张数").DataCol.setFormat("#,###")

Tables("统计表c").cols("可减免张数").DataCol.setFormat("#,###")

Tables("统计表c").cols("超张数").DataCol.setFormat("#,###")

Tables("统计表c").cols("超金额").DataCol.setFormat("#,###")

Tables("统计表c").cols("实际减免金额").DataCol.setFormat("#,###")

Tables("统计表c").cols("实际减免张数").DataCol.setFormat("#,###")

Tables("统计表c").SetColVisibleWidth("年|80|月|25|日|25|星期|80|优惠额度|100|折算张数|80|可减免张数|80|超张数|50|超金额|50|实际减免金额|120|实际减免张数|50")