主题：在交叉统计设置中复制代码 不能直接使用吗？

1、贴出代码看看；

 

2、尽量做个实例发上来测试。

代码没问题。最后生成的表是【统计表2】，你还有其余的代码影响了？

 

做个出错的例子发上来测试。

Dim g As New sqlCrossTableBuilder("统计表2", "表A")
g.HGroups.AddExp("第一列","cstr(year(第一列)) + '年' + cstr(month(第一列)) + '月'")
g.VGroups.AddDef("第二列")
g.Totals.AddDef("第二列", AggregateEnum.Count, "第二列")
g.Build()
MainTable = Tables("统计表2")

 

加入代码

 

g.ConnectionName = "数据源名"

回复7楼，没看懂你第二个问题，请就例子具体说明那你要做什么。

手工统计，参考

 

http://www.foxtable.com/webhelp/scr/0681.htm

 

http://www.foxtable.com/webhelp/scr/2394.htm

 

 具体例子发生上来。

计算这样算。没看懂你使用数量怎么计算怎么显示，请举例说明。
 
Dim dtb As New DataTableBuilder("统计")
dtb.AddDef("日期", Gettype(String))
Dim str As String = "未领取,报废,返修A厂"
Dim prds As List(of String) = DataTables("订单").GetValues("领取人", "领取人 not in ('" & str.replace(",", "','") & "')")
prds.Insert(0, "返修A厂")
prds.Insert(0, "报废")
prds.Insert(0, "未领取")
For Each prd As String In prds
    dtb.AddDef(prd & "_领取数量", Gettype(Integer))
    dtb.AddDef(prd & "_使用数量", Gettype(Integer))
    dtb.AddDef(prd & "_剩余数量", Gettype(Integer))
Next
dtb.Build()
For Each cus As String In DataTables("订单").sqlGetValues("format(日期,'yyyy年MM月')")
    Dim dr As DataRow = DataTables("统计").AddNew()
    dr("日期") = cus
    Dim d As Date = new Date(cus.Substring(0,4), cus.SubString(5,2), 1)
    Dim filter As String = "日期 >= #" & d & "# and 日期 < #" & d.addMonths(1) & "#"
    For Each prd As String In prds
        Dim dt As DataTable =DataTables("登记表")
        dr(prd & "_领取数量") = DataTables("订单").Compute("Count(领取人)",filter & " And [领取人] = '" & prd & "'")
    Next
Next
MainTable= Tables("统计")

Dim dtb As New DataTableBuilder("统计")
dtb.AddDef("日期", Gettype(String))
Dim str As String = "未领取,报废,返修A厂"
Dim prds As List(of String) = DataTables("订单").GetValues("领取人", "领取人 not in ('" & str.replace(",", "','") & "')")
prds.Insert(0, "返修A厂")
prds.Insert(0, "报废")
prds.Insert(0, "未领取")
For Each prd As String In prds
    dtb.AddDef(prd & "_领取数量", Gettype(Integer))
    dtb.AddDef(prd & "_使用数量", Gettype(Integer))
    dtb.AddDef(prd & "_剩余数量", Gettype(Integer))
Next
dtb.Build()
Dim ids As String = DataTables("登记表").GetComboListString("产品id号").replace("|", "','")
For Each cus As String In DataTables("订单").sqlGetValues("format(日期,'yyyy年MM月')")
    Dim dr As DataRow = DataTables("统计").AddNew()
    dr("日期") = cus
    Dim d As Date = new Date(cus.Substring(0,4), cus.SubString(5,2), 1)
    Dim filter As String = "日期 >= #" & d & "# and 日期 < #" & d.addMonths(1) & "#"
    For Each prd As String In prds
        Dim dt As DataTable =DataTables("登记表")
        dr(prd & "_领取数量") = DataTables("订单").Compute("Count(领取人)",filter & " And [领取人] = '" & prd & "'")
        dr(prd & "_使用数量") = DataTables("订单").Compute("Count(领取人)",filter & " And [领取人] = '" & prd & "' and 产品id号 in ('" & ids & "')")
    Next
Next
MainTable= Tables("统计")

剩余数量，直接用【领用】-【使用】即可。

 

试试

 

Dim cmd As new SQLCommand
cmd.commandText = "Select  领取人, format(日期,'yyyy年MM月') as 日期, {登记表}.产品id号 As 产品id号 From {订单} Left JOIN {登记表} ON {登记表}.[产品ID号] = {订单}.[产品ID号]"
Dim dt As DataTable = cmd.ExecuteReader
Dim dtb As New DataTableBuilder("统计")
dtb.AddDef("日期", Gettype(String))
Dim str As String = "未领取,报废,返修A厂"
Dim prds As List(of String) = dt.GetValues("领取人", "领取人 not in ('" & str.replace(",", "','") & "')")
prds.Insert(0, "返修A厂")
prds.Insert(0, "报废")
prds.Insert(0, "未领取")
For Each prd As String In prds
    dtb.AddDef(prd & "_领取数量", Gettype(Integer))
    dtb.AddDef(prd & "_使用数量", Gettype(Integer))
    dtb.AddDef(prd & "_剩余数量", Gettype(Integer))
Next
dtb.Build()
For Each cus As String In dt.GetValues("日期")
    Dim dr As DataRow = DataTables("统计").AddNew()
    dr("日期") = cus
    Dim d As Date = new Date(cus.Substring(0,4), cus.SubString(5,2), 1)
    Dim filter As String = "日期 = '" & cus & "'"
    For Each prd As String In prds
        dr(prd & "_领取数量") = dt.Compute("Count(领取人)",filter & " And [领取人] = '" & prd & "'")
        dr(prd & "_使用数量") = dt.Compute("Count(领取人)",filter & " And [领取人] = '" & prd & "' and 产品id号 is not null")
    Next
Next
MainTable= Tables("统计")