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

请教老师，想得到下边的表，是否能实现

dvubb 此主题相关图片如下：接单.png

我写了如下代码，有错误

Dim sql As String = "Sele ct 接单日期,{接单登记}.接单单号,期望发货日期,客户来源,客户名称,收货地址,产品名称,规格型号,单位,单价,销售数量,发货数量 as 已发货数量,(销售数量 - 发货数量) AS 未发货数量 From ({接单登记} Inner JOIN {接单明细} ON {接单明细}.[接单单号] = {接单登记}.[接单单号]) ORDER BY 接单日期"
Dim sqlf As String = "Sele ct 发货日期,{发货表}.发货单号,{发货表}.接单单号,客户名称,收货地址,产品名称,规格型号,单位,发货数量 From ({发货表} Inner JOIN {发货明细} ON {发货明细}.[发货单号] = {发货表}.[发货单号]) ORDER BY 发货日期"
Dim bd1 As New CrossTableBuilder("统计表1", sql)
Dim dt1 As fxDataSource
bd1.HGroups.AddDef("接单日期", DateGroupEnum.none)
bd1.HGroups.AddDef("接单单号")
bd1.HGroups.AddDef("客户名称")
bd1.HGroups.AddDef("产品名称") '添加客户列用于水平分组
bd1.HGroups.AddDef("规格型号") '添加客户列用于水平分组
bd1.HGroups.AddDef("单位") '添加客户列用于水平分组
bd1.HGroups.AddDef("销售数量")
bd1.HGroups.AddDef("已发货数量")
bd1.HGroups.AddDef("未发货数量")
dt1 = bd1.BuildDataSource()

Dim bd2 As New CrossTableBuilder("统计表2", sqlf)
Dim dt2 As fxDataSource
bd2.VGroups.AddDef("发货日期", DateGroupEnum.none)
bd2.vGroups.AddDef("发货数量")
dt2 = bd1.BuildDataSource()

Dim nms As String() = {"接单单号", "客户名称", "产品名称" }
dt1.Combine(nms, dt2, nms)
Tables("窗口2_Table1").DataSource = dt1

[此贴子已经被作者于2024/12/5 16:55:34编辑过]

请上传实例测试

实例

下载信息 [文件大小：下载次数： ]
点击浏览该文件:测试.foxdb

下载信息 [文件大小：下载次数： ]
点击浏览该文件:测试.zip

感谢老师的指点！

再追问一下大师，导出报表时如何象一楼表中只显示有发货数量的列，没有发货的不显示

Dim filter As String

With e.Form.Controls("cmbCustname")

If .Value IsNot Nothing Then

If Filter > "" Then

Filter = Filter & " And "

End If

Filter = Filter & "客户名称 = '" & .Value & "'"

End If

End With

If Filter > "" Then

Tables("窗口2_Table1").Filter = Filter

End If

Dim dt As Table = Tables("窗口2_Table1")

Dim Book As New XLS.Book '定义一个Excel工作簿

Dim Sheet As XLS.Sheet = Book.Sheets(0) '引用工作簿的第一个工作表

For c As Integer = 0 To dt.Cols.Count - 1 '添加列标题

Sheet(0, c).Value = dt.Cols(c).Name

For r As Integer = 0 To dt.Rows.Count - 1 '填入数据

For c As Integer = 0 To dt.Cols.Count - 1

Sheet(r + 1, c).Value = dt.rows(r)(c)

'打开工作簿

Book.Save("c:\reports\test.xls")

Dim Proc As New Process

Proc.File = "c:\reports\test.xls"

Proc.Start()

'For Each c As Col In Tables("窗口2_Table1").cols

'If c.name Like "发货日期_*" Then

'If Tables("窗口2_Table1").comoute("count(" & c.name & ")", c.name & " is not null") > 0 Then

'If Tables("窗口2_Table1").compute("发货数量") = 0 Then

'Tables("窗口2_Table1").cols("发货日期").visible = True

'End If

[此贴子已经被作者于2024/12/7 14:29:20编辑过]

主题：报表