麻烦老师给看下
Dim y As Integer = Date.Today.Year
Dim dt1 As New Date(y, 1, 1)
Dim dt2 As New Date(y, 12, 31)
Dim Filter As String
Filter = "接单日期 >= #" & dt1 & "# And 接单日期 <= #" & dt2 & "#"
Dim jd As String = "Selec t 接单日期,{接单登记}.接单单号,产品名称,规格型号,单位,销售数量,发货数量 as 已发货数量,(销售数量 - 发货数量) AS 未发货数量 Fro m {接单登记} Inner JOIN {接单明细} ON {接单明细}.[接单单号] = {接单登记}.[接单单号] "
Dim bd1 As New CrossTableBuilder("统计表1", jd, "sale")
bd1.HGroups.AddDef("产品名称") '添加客户列用于水平分组
bd1.HGroups.AddDef("规格型号") '添加客户列用于水平分组
bd1.HGroups.AddDef("单位") '添加客户列用于水平分组
bd1.VGroups.AddDef("接单日期", DateGroupEnum.Year, "{0}年")
bd1.VGroups.AddDef("接单日期", "{0}月") '添加日期列用于垂直分组,按月分组
bd1.Totals.AddDef("销售数量", "接单")
bd1.Totals.AddDef("已发货数量", "发货")
bd1.Filter = Filter
bd1.HorizontalTotal = True
Tables("窗口1_Table1").DataSource = bd1.BuildDataSource()
DataTables("窗口1_Table1").DataCols.Add( "上年结转", GetType(Integer))
Tables("窗口1_Table1").Cols("上年结转").Move(3)
DataTables("窗口1_Table1").DataCols.Add( "合计_结存", GetType(Integer), "IsNull([上年结转],0)+IsNull([合计_接单],0) - ISNULL([合计_发货],0)")
Dim d = Date.Today
d = d.addYears( - 1)
Dim 年 As Integer = d.Year
Dim dt3 As New Date(年, 1, 1)
Dim dt4 As New Date(年, 12, 31)
Dim Filterdate As String
Filterdate = "接单日期 >= #" & dt3 & "# And 接单日期 <= #" & dt4 & "#"
Dim bd2 As New CrossTableBuilder("统计表1", jd, "sale")
bd2.HGroups.AddDef("产品名称") '添加客户列用于水平分组
bd2.HGroups.AddDef("规格型号") '添加客户列用于水平分组
bd2.HGroups.AddDef("单位") '添加客户列用于水平分组
bd2.VGroups.AddDef("接单日期", DateGroupEnum.Year, "{0}年")
bd2.VGroups.AddDef("接单日期", "{0}月") '添加日期列用于垂直分组,按月分组
bd2.Totals.AddDef("销售数量", "接单")
bd2.Totals.AddDef("已发货数量", "发货")
bd2.Filter = Filterdate
bd2.HorizontalTotal = True
bd2.Build()
'Tables("窗口1_Table1").DataSource = bd2.BuildDataSource()
Dim lst As New List(Of String)
DataTables("统计表1").DataCols.Add( "上年结存", GetType(Integer))
Tables("统计表1").Cols("上年结存").Move(3)
DataTables("统计表1").DataCols.Add( "合计_结存", GetType(Integer), "IsNull([上年结存],0)+IsNull([合计_接单],0) - ISNULL([合计_发货],0)")
Tables("窗口1_Table1").Cols("上年结存") = Tables("统计表1").Cols("合计_结存")
[此贴子已经被作者于2025/1/9 12:16:05编辑过]